生成具有新 ID 的脚本(也用于依赖项) [英] Generate scripts with new ids (also for dependencies)

问题描述

在 Microsoft SQL Server 2008 R2 中，我有一些数据存储在一个表中，我想要一个脚本来创建数据的精确副本，只有不同的 guid(唯一标识符).挑战在于，其他表中也有数据依赖于主表.这些数据也应该被复制并引用到新条目中.

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.

结构如下:

表格形式:

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

表格字段:

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

现在我想复制两个表的数据，所有条目都应该获得一个新的主键.表字段中 FormGuid 的值也应该引用新的 id.

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.

结果应该是这样的:

表格形式:

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

表格字段:

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

有没有办法在 SQL Server Management Studio 中自动生成这种脚本?在生成脚本"向导中，我只能使用现有的主键生成脚本.

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.

推荐答案

类似这样的事情?我使用表值变量将一列添加"到原始表单表中.

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    

这篇关于生成具有新 ID 的脚本(也用于依赖项)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！