March 28, 2008 at 7:54 am
I have a temporary table that (based on its current values) would fit nicely one-to-one across two existing tables.
The second existing table has a foreign key from an auto generated primary key off of the first existing table.
Rather than insert half the columns from the temp table into the first existing table, search for the new IDs then use those to insert the rest of the columns for the temporary table rows into the second existing table, is there a way to insert these records into both tables at the same time? Would I need to use a cursor?
Thanks! (And I'm sorry if this appears elsewhere-- I found articles about inserting into multiple tables, but not where there's a dependency between the targets)
March 28, 2008 at 8:49 am
Here's a quick example. I'm only using a single row, but the same methodology will work with multiple rows:
CREATE TABLE Master1
(
Master1Id INT IDENTITY(1, 1)
NOT NULL
,Master1Desc NVARCHAR(50)
)
GO
CREATE TABLE Child1
(
Child1Id INT IDENTITY(1, 1)
NOT NULL
,Master1Id INT
,SomeValue NVARCHAR(50)
)
GO
ALTER TABLE Master1
ADD CONSTRAINT PK_Master1 PRIMARY KEY CLUSTERED ( Master1Id ASC )
GO
ALTER TABLE Child1
ADD CONSTRAINT PK_Child1 PRIMARY KEY CLUSTERED ( Child1Id ASC )
GO
USE [Performance2]
GO
ALTER TABLE Child1 WITH CHECK
ADD CONSTRAINT [FK_Master_Child] FOREIGN KEY ( Master1Id ) REFERENCES Master1 ( Master1Id )
GO
CREATE TABLE #MyTemp ( Id INT )
INSERT INTO Master1 ( Master1Desc )
OUTPUT Inserted.Master1ID
INTO #MyTemp
VALUES ( 'Something' )
INSERT INTO Child1 ( Master1Id, SomeValue )
SELECT Id
,'AnotherValue'
FROM #MyTemp
DROP TABLE #MyTemp
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 28, 2008 at 8:51 am
Hi,
You don't have to use cursor, there are better options.
Look at the OUTPUT clause in BOL.
Here's a small example I wrote. I assume that since you mentioned autogenerated field that you use as a key, you should have a set of columns that can serve as actual key - that is, they can uniquely identify the record. in the example, such unique columns are a and b, which are used to retrieve child data from original table in second insert in transaction.
create table amaster(pk int identity(1,1) primary key, fa varchar(5), fb varchar(5))
create table achild(fk int references amaster(pk), fc datetime)
go
create table #temptable
(a varchar(5), b varchar(5), c datetime)
insert #temptable(a, b, c)
values('aaa', 'bbb', getdate() + 1)
insert #temptable(a, b, c)
values('aab', 'bbc', getdate() + 2)
insert #temptable(a, b, c)
values('aac', 'bbd', getdate() + 3)
insert #temptable(a, b, c)
values('aad', 'bbe', getdate() + 4)
insert #temptable(a, b, c)
values('aae', 'bbf', getdate() + 5)
select * from #temptable
create table #masterwithids
(generatedid int, a varchar(5), b varchar(5))
begin try
begin tran
--this is adding new records to amaster and fills #masterwithids
--with generated key
--and columns used later to join to original #temptable
insert amaster (fa, fb)
output inserted.pk, inserted.fa, inserted.fb
into #masterwithids(generatedid, a, b)
select a, b from #temptable
--this is adding rows to child table. rows to add are identified
-- by mathing a and b fields
-- on source table and the one populated in previous query.
insert achild(fk, fc)
select generatedid, c from #temptable inner join #masterwithids
on #temptable.a = #masterwithids.a and #temptable.b = #masterwithids.b
commit
end try
begin catch
rollback
end catch
select * from amaster
select * from achild
drop table #temptable
drop table #masterwithids
go
drop table achild
drop table amaster
HTH
Piotr
...and your only reply is slàinte mhath
March 28, 2008 at 9:15 am
Notice that what Grant and Piotr are both trying to improve on is the "lookup" part of the process.
That's because you can't use a single operation to insert into both tables at the same time, since each insert can affect only one table. Even if you were to "simulate" doing an insert into 2 tables using views and triggers on the view, the bottom line is - the trigger(s) will have a distinct operation inserting into table 1 and another inserting into table 2.
So - to answer your original question - no there really isn't a way to insert into 2 tables at once. There are ways to improve on the "traditional" way to split data (among which the OUTPUT clause), and ways to ensure that both sides get updated or nothing does (transactions/triggers) but still - each table needs its own operation.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 28, 2008 at 9:45 am
Yeah, that's true. Sorry, I should have clarified. My issue is to prevent the INSERT...SELECT...INSERT chain and the OUTPUT clause bypasses that horror show quite well. I do tend to jump past explanations into answers. Nice job Matt.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2008 at 7:12 am
I don't know if this helped the original poster but it certainly helped me. Thank you all very much (I used Piotr's example pretty much as it was given as this eerily mirrored by situation).
Cheers,
Ash
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply