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

Question

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

Hello,

I have the following tables

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

AND also table X with other various data.

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.

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

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

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

Too long didn't read. Try a shorter question :)

Andomar
– Andomar

2011-12-22 12:22:00 +00:00
Commented Dec 22, 2011 at 12:22 — Andomar
– Andomar, Commented Dec 22, 2011 at 12:22

ryan1234 · Accepted Answer · 2011-12-22 17:38:22Z

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.

Something like this first:

select * into TestMergeQuote_Temp from TestMergeQuote

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.

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

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related