Generate scripts with new ids (also for dependenci

2019-08-01 14:30发布

问题:

In Microsoft SQL Server 2008 R2 I have some data stored in a table for which I want a script which creates an exact copy of the data, with different guids's (uniqueidentifiers) only. The challenge is, there is also data in other tables with dependencies on the main table. These data should also be copied and referenced to the new entries.

The structure looks like that:

Table Form:

Guid    Name
335AC2DD-C874-45E4-90AA-194882DB7C12    Testform

Table Field:

Guid    FormGuid    Name
9640CA20-2CE6-4BFB-929C-8A92D313DEB2    335AC2DD-C874-45E4-90AA-194882DB7C12    Testfield

Now I'd like to copy the data of both tables and all entries should get a new primary key. Also the value of FormGuid in Table Field should reference the new id.

The result should look like that:

Table Form:

Guid    Name
335AC2DD-C874-45E4-90AA-194882DB7C12    Testform
B649C385-278B-4163-882C-E5C3B6A96F2F    Testform

Table Field:

Guid    FormGuid    Name
9640CA20-2CE6-4BFB-929C-8A92D313DEB2    335AC2DD-C874-45E4-90AA-194882DB7C12    Testfield
C7C65EEE-E02B-49F5-99CD-F0042CC15C4F    B649C385-278B-4163-882C-E5C3B6A96F2F    Testfield

Is there a way to generate such a kind of script automatically in SQL Server Management Studio? In the "Generate Scripts" wizard I can generate scripts with the existing primary keys only.

回答1:

Something like this? I use a table valued variable to "add" a column to the original forms table.

DECLARE @Form1 UNIQUEIDENTIFIER=NEWID();
DECLARE @Form2 UNIQUEIDENTIFIER=NEWID();

DECLARE @tblForms TABLE(id UNIQUEIDENTIFIER,FormName VARCHAR(100));
INSERT INTO @tblForms VALUES(@Form1,'test1'),(@Form2,'test2');

DECLARE @tblFields TABLE(id UNIQUEIDENTIFIER,FormId UNIQUEIDENTIFIER,FieldName VARCHAR(100));
INSERT INTO @tblFields VALUES(NEWID(),@Form1,'test1.1'),(NEWID(),@Form1,'test1.2'),(NEWID(),@Form1,'test1.3')
                            ,(NEWID(),@Form2,'test2.1'),(NEWID(),@Form2,'test2.2'),(NEWID(),@Form2,'test2.3');

--These are "your original IDs"
SELECT frms.id,frms.FormName
      ,flds.id,flds.FieldName
FROM @tblForms AS frms
INNER JOIN @tblFields AS flds ON frms.id=flds.FormId ;                           

--Copy forms into a new table with an extra column
DECLARE @tblFormsNeu TABLE(id UNIQUEIDENTIFIER,FormName VARCHAR(100),myNewID UNIQUEIDENTIFIER);
INSERT INTO @tblFormsNeu
SELECT id,FormName,NEWID() FROM @tblForms;

SELECT frms.myNewID, frms.FormName
      ,NEWID() AS myNewFieldID,flds.FieldName
FROM @tblFormsNeu AS frms
INNER JOIN @tblFields AS flds ON frms.id=flds.FormId