tsql - T-SQL 使用条件而不使用游标插入多个链接表

Question

T-SQL 使用条件而不使用游标插入多个链接表。

你好，

我有以下表格

CREATE TABLE [dbo].[TestMergeQuote](
    [uid] [uniqueidentifier] NOT NULL,
    [otherData] [nvarchar](50) NULL,
 CONSTRAINT [PK_TestMergeQuote] PRIMARY KEY CLUSTERED 
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



ALTER TABLE [dbo].[TestMergeQuote] ADD  CONSTRAINT [DF_TestMergeQuote_uid]  DEFAULT (newid()) FOR [uid]


--=============



CREATE TABLE [dbo].[TestMergeClient](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [otherData] [nvarchar](50) NULL,
 CONSTRAINT [PK_TestMergeClient] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--==============




CREATE TABLE [dbo].[TestMergeDocument](
    [id] [int] NOT NULL,
    [uid_quote] [uniqueidentifier] NOT NULL,
    [id_owner] [int] NOT NULL,
    [id_keeper] [int] NULL,
    [otherData] [nvarchar](50) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TestMergeDocument]  WITH CHECK ADD  CONSTRAINT [FK_TestMergeDocument_TestMergeClient_Keeper] FOREIGN KEY([id_keeper])
REFERENCES [dbo].[TestMergeClient] ([id])
GO

ALTER TABLE [dbo].[TestMergeDocument] CHECK CONSTRAINT [FK_TestMergeDocument_TestMergeClient_Keeper]
GO

ALTER TABLE [dbo].[TestMergeDocument]  WITH CHECK ADD  CONSTRAINT [FK_TestMergeDocument_TestMergeClient_Owner] FOREIGN KEY([id_owner])
REFERENCES [dbo].[TestMergeClient] ([id])
GO

ALTER TABLE [dbo].[TestMergeDocument] CHECK CONSTRAINT [FK_TestMergeDocument_TestMergeClient_Owner]
GO

ALTER TABLE [dbo].[TestMergeDocument]  WITH CHECK ADD  CONSTRAINT [FK_TestMergeDocument_TestMergeQuote] FOREIGN KEY([uid_quote])
REFERENCES [dbo].[TestMergeQuote] ([uid])
GO

ALTER TABLE [dbo].[TestMergeDocument] CHECK CONSTRAINT [FK_TestMergeDocument_TestMergeQuote]
GO

并且还有带有其他各种数据的表 X。

我想将这三个表中已经存在的数据插入到这三个表中，但给它不同的 id，并替换 X 表中的一些数据。
这是一种“复制去年的数据”，但添加新信息。

条件是id_keeper有时为null，不应该对其进行插入。

我知道我必须使用 OUTPUT 和 MERGE，但我不知道如何实现如此复杂的东西。

使用游标的 CRUDE 代码将是:

DECLARE @OldIdDocument INT, @NewIdDocument INT
DECLARE @OldIdOwner INT, @NewIdOwner INT
DECLARE @OldIdKeeper INT, @NewIdKeeper INT
DECLARE @OldIdQuote UNIQUEINDETIFIER, @NewIdQuote UNIQUEINDETIFIER, 


INSERT INTO TestMergeQuote(otherData) 
SELECT TOP(1) otherData FROM TestMergeQuote WHERE uid = @OldIdQuote 
SET @NewIdQuote = @@IDENTITY

INSERT INTO TestMergeClient(otherData) 
SELECT TOP(1) otherData FROM TestMergeClient WHERE uid = @OldIdOwner 
SET @NewIdOwner = @@IDENTITY

IF(@OldIdKeeper  IS NOT NULL)
    BEGIN
        INSERT INTO TestMergeClient(otherData) 
        SELECT TOP(1) otherData FROM TestMergeClient WHERE uid = @OldIdKeeper 
        SET @NewIdKeeper = @@IDENTITY
    END

INSERT INTO TestMergeDocument([uid_quote], [id_owner] , [id_keeper], otherData) 
SELECT TOP(1) @NewIdQuote , @NewIdOwner , @NewIdKeeper  ,otherData FROM TestMergeDocument WHERE uid = @OldIdDocument 
SET @NewIdDocument = @@IDENTITY

Answer 1

你不应该使用游标。我会尝试首先将数据抽取到单独的表中，以便您可以根据自己的喜好操作数据。

首先是这样的:

select * into TestMergeQuote_Temp from TestMergeQuote

这将创建一个包含您要复制的数据的新表。当然，您可以添加 where 子句来过滤数据，这样您就不会复制非常大的表。

然后您可以在 _Temp 版本上添加值、更改值、删除值。

准备好后，您可以插入数据。当然，如果您有一个自动递增的主键，您可能必须关闭自动 id。或者，如果您只想要新 id 并且不想手动创建 id，您应该能够很好地插入新记录并为您创建新 id。

但首先，尝试将数据泵入新表，然后再考虑插入。