How can I clone a table with guid's as well as another table that references those guid's as foreign keys.
The two tables have a one to many relationship. I would need to do this in an automated way.
My problem is being able to reference the relationship between the two tables when newly created guid's are created during the clone.
EDIT: I need to clone the entries to the tables themselves, not new tables.
Basically, we want to duplicate the Tables with new Ids but keep the relationship the same as the originals. If required, you could keep the same Ids, but in practice this shouldn't be a requirement; for testing, the Ids should not matter - only the relationship.
I'll demonstrate this with two Tables:
The first is AnimalType with Id (uniqueidentifier - RowGuid, Primary Key) and AnimalType (nvarchar) Columns
The second is Animal with AnimalName (nvarchar) and AnimalType (uniqueidentifier, Foreign Key) Columns
For the parent/lookup Table:
Create a new Table (newTable) to populate with the data of the existing Table (oldTable).
Create newTable with its own Primary Key Id column (ROWGUID, IDENTITY etc.) with its Default
Create an extra Column in newTable to hold a copy of oldTable's Id Column values
The Id Column in newTable will generate unique Ids on creation of records
The second (child) Table:
Create a new Table (newChildTable) to populate with the data of the existing Table (oldChildTable).
Create newChildTable with its own Foreign Key Column to point to newTable's Primary Key Column
Create an extra Column in newChildTable to hold a copy of oldChildTable's Foreign Key Column values
Once created, we populate the new parent/lookup Table with the data from the original Table, placing the Id values in the extra Column added for this data. The Table's own Ids will generate uniquely as usual.
Next, we populate the child Table with the data from its original Table, placing the original Foreign Key Column values into the added Column for this data.
Next, we join the two new tables on the Columns that hold the original Id values and update the Foreign Key Column values to the new Ids in the parent/lookup Table.
Finally, we can remove the Columns holding the original Id values, and we are left with two Tables linked to the same data but by the new Ids we generated when created when the records were copied.
You will not have any reference to the original Ids - just in case of selecting the wrong Table at any time in your testing (although this should be done in a different server...). If you needed the original Ids too, you can perform the above, not move the Ids around, rename the Columns etc. - as you wish really.
/*
Create copy of parent/lookup Table with its own Id column
Add a column to hold the original Ids
*/
CREATE TABLE [dbo].[AnimalTypeBak](
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_AnimalTypeBak_Id] DEFAULT (newid()),
[OriginalId] [uniqueidentifier] NOT NULL,
[AnimalType] [nvarchar](32) NOT NULL,
CONSTRAINT [PK_AnimalTypeBak] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]
GO
/*
Create copy of child Table
Add a column to hold the original Foreign Key values
*/
CREATE TABLE [dbo].[AnimalBak](
[AnimalName] [nvarchar](20) NOT NULL,
[OriginalAnimalType] [uniqueidentifier] NOT NULL,
[AnimalType] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
/*
Import data from the parent/lookup Table placing the origional Ids into the added Column
*/
INSERT INTO [dbo].[AnimalTypeBak]
([OriginalId]
,[AnimalType])
SELECT [Id], [AnimalType]
FROM [dbo].[AnimalType]
GO
/*
Import data from the child Table placing the origional Foreign Key values into the added Column
*/
INSERT INTO [dbo].[AnimalBak]
([OriginalAnimalType]
,[AnimalName])
SELECT [AnimalType], [AnimalName]
FROM [dbo].[Animal]
GO
/*
Update the child Table placing the new parent/lookup Ids into the Foreign Key Column
*/
UPDATE [dbo].[AnimalBak]
SET [dbo].[AnimalBak].[AnimalType] = [dbo].[AnimalTypeBak].[Id]
FROM [dbo].[AnimalBak]
INNER JOIN [dbo].[AnimalTypeBak]
ON [dbo].[AnimalBak].[OriginalAnimalType] = [dbo].[AnimalTypeBak].[OriginalId]
GO
/*
Drop the redundant Columns
*/
ALTER TABLE [dbo].[AnimalBak]
DROP COLUMN [OriginalAnimalType]
GO
ALTER TABLE [dbo].[AnimalTypeBak]
DROP COLUMN [OriginalId]
/*
Add the Foreign Key Contraint between the two Tables
*/
ALTER TABLE [dbo].[AnimalBak] WITH CHECK ADD CONSTRAINT [FK_AnimalBak_AnimalTypeBak] FOREIGN KEY([AnimalType])
REFERENCES [dbo].[AnimalTypeBak] ([Id])
GO
/*
And select the data to ensure the data is related as it was in the original Tables
*/
SELECT a.AnimalName, a.AnimalType, b.AnimalType FROM [dbo].[AnimalBak] as a INNER JOIN [dbo].[AnimalTypeBak] as b ON b.Id = a.AnimalType
declare @Parents table (Id uniqueidentifier, Name varchar(50));
declare @Children table (Id uniqueidentifier, ParentId uniqueidentifier, Name varchar(50));
declare @NewId uniqueidentifier = newid();
insert into @Parents values (@NewId, 'Original parent');
insert into @Children values (newid(), @NewId, 'First original child');
insert into @Children values (newid(), @NewId, 'Second original child');
declare @Ids table (CloneId uniqueidentifier, OriginalId uniqueidentifier);
merge @Parents as target
using (
select
CloneId = newid(),
OriginalId = Id,
Name = Name + ' (Cloned)'
from
@Parents
where
Id = @NewId
)
as source on source.CloneId = target.Id
when not matched by target then
insert (Id, Name)
values (source.CloneId, source.Name)
output
source.CloneId, source.OriginalId
into @Ids (CloneId, OriginalId);
merge @Children as target
using (
select
Id = newid(),
ParentId = ids.CloneId,
Name = Name + ' (Cloned)'
from
@Children c
inner join @Ids ids on ids.OriginalId = c.ParentId
)
as source on source.Id = target.Id
when not matched by target then
insert (Id, ParentId, Name)
values (source.Id, source.ParentId, source.Name);
select * from @Parents
select * from @Children
