T-SQL使用条件而不使用游标将其插入到多个链接表中 [英] T-SQL Insert into multiple linked tables using a condition and without using a cursor

问题描述

T-SQL使用条件而不使用游标将其插入到多个链接表中.

T-SQL Insert into multiple linked tables using a condition and without using a cursor.

你好

我有下表

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与其他各种数据一起存储.

---

AND also table X with other various data.

我想将这3个表中已经存在的数据插入到这3个表中，但是为其赋予不同的ID，并且还要替换X表中的某些数据. 这是一种复制去年的数据"，但是添加新信息.

I want to insert into these three tables the data that already exists in these 3 tables, but giving it different id's, and also replacing some of the data within the X table. It's a sort of a "copy the data from last year", but add new info.

条件是id_keeper有时为null，因此不应对其进行任何插入.

The condition is that id_keeper is sometimes null, and no insert should be done for it.

我知道我必须使用OUTPUT和MERGE，但是我不知道如何实现这种复杂的功能.

I am aware that I have to use OUTPUT and MERGE, but I have no ideea how to achieve something this complex.

使用游标的此CRUDE代码为:

The CRUDE code for this using a cursor would be:

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

推荐答案

您不必使用游标.我要尝试的是先将数据泵出到单独的表中，以便您可以根据自己的意愿操作数据.

You shouldn't have to use a cursor. What I would try is to first pump the data out into separate tables so you can manipulate the data to your heart's content.

首先是这样的:

select * into TestMergeQuote_Temp from TestMergeQuote

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

That will make a new table with the data you want to copy. Of course you can add a where clause to filter the data so you aren't copying a very large table.

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

Then you can add values, change values, delete values on the _Temp versions.

准备就绪后，可以将数据插入回去.当然，如果您具有自动递增的主键，则可能必须关闭自动ID.或者，如果您只是想要新的ID，而又不想手动制作ID，则应该可以插入新记录，并为您创建新的ID.

When you are ready you can insert the data back. Of course you might have to turn auto id off if you have a primary key that is auto-incrementing. Or if you just want new id's and don't want to make id's manually, you should be able to insert the new records just fine and have new id's created for you.

但是，首先，请尝试将数据泵送到新表中，然后再担心会在之后插入.

But as a start, try pumping the data into new tables and then worry about inserting after that.

这篇关于T-SQL使用条件而不使用游标将其插入到多个链接表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！