Insert into Multiple Tables Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert into Multiple Tables Problem

Author

Topic

huntera
Starting Member

4 Posts

Posted - 2009-09-22 : 11:20:31

Hello!

This is my first post, so please be kind :)

I've been working on this problem for a few days now, but yet to find an answer, so hoping you guys/gals can help.

I'm attempting to insert into multiple tables at once, each with a 1-many relationship between them. Each table has a primary key which is an identity column. Apart from the master table, all the children have foreign keys linking to their parents.

Other than the identity, there is no (or we can presume for this example) uniqueness in any of the other columns of the table.

I have a temporary table with all the tables combined into 1, a kind of mash-up table, with temporary ids for each table in the mash-up:

Id1, Data1, Id2, Data2, Id3, Data3 etc
1, 'd1', 1, 'd2', 1, 'd3'
1, 'd1', 1, 'd2', 2, 'd3'
1, 'd1', 2, 'd2', 3, 'd3'
1, 'd1', 2, 'd2', 4, 'd3'
2, 'd1', 3, 'd2', 5, 'd3'
2, 'd1', 3, 'd2', 6, 'd3'
2, 'd1', 4, 'd2', 7, 'd3'

Id3 in this example will be unique and is the lowest level table

For each final table I want to insert into, I "GROUP BY" the mash-up to get the data I want

Here's the question:

Once I insert into the desired table (let's say the first master table), a new identity is created. How do I use this to link back to the mash-up table to carry on with the other inserts to the child tables? As soon as I insert into the table, the original mash-up ID is lost!

I know I can use a cursor. But, would rather not, or at least like to find another alternative!

Also I would rather not create a temporary column in any of the 'actual' tables. (i.e. I could insert the 'Mash-up' Id into the table too, which would provide the link)

Thanks,

Alex.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-22 : 13:31:09

What do the destination(s) look like? Can you give some more deatail around what you are selecting and grouping by from the mash-up table. I'm sure we can help you, but I don't understand the problem well enough.

huntera
Starting Member

4 Posts

Posted - 2009-09-22 : 19:16:56

Thanks for the response, I shall elaborate!

From the example mash-up table I can insert into 3 tables in total (again examples):

TableA
TableAId int identity,
TableAData varchar /other.

TableB
TableBId int identity,
TableAId (FK)
TableBData varchar / other.

TableC
TableCId int identity
TableBId (FK)
TableCData varchar / other.

The (Attempted) insert would look something like this:

Insert into TableA
Select Data1
From @Mashup
Group By Id1

OK we inserted into the master table here (TableA), however the identity automatically generated (TableAId) doesn't relate to Id1 of the Mash-up. Lets try and carry on:

Insert into TableB (TableAId, TableBData)
Select [??How do we get this??], Data2
From @Mashup
Group By Id2

I can't join to TableA at this point as have nothing to join on :(
(I don't think!)

Hope this helps!

Thanks.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-23 : 01:18:38

By Keeping the primary column of TableA,TableB etc. as identity columns you are losing the original values of the Ids in your mashup tables and I do not think you can retrieve it later. Why do you want it to be identity columns? why cant you explicitly insert your mashup id to it? That will be something like

create table #mashup(Id1 int, Data1 varchar(50), Id2 int, Data2 varchar(50), Id3 int, Data3 varchar(50))

insert into #mashup values (1, 'd1', 1, 'd2', 1, 'd3')
insert into #mashup values (1, 'd1', 1, 'd2', 2, 'd3')
insert into #mashup values (1, 'd1', 2, 'd2', 3, 'd3')
insert into #mashup values (1, 'd1', 2, 'd2', 4, 'd3')
insert into #mashup values (2, 'd1', 3, 'd2', 5, 'd3')
insert into #mashup values (2, 'd1', 3, 'd2', 6, 'd3')
insert into #mashup values (2, 'd1', 4, 'd2', 7, 'd3')

create table #TableA
(
TableAId int,
TableAData varchar(50)
)

create table #TableB
(
TableBId int,
TableAId int,
TableBData varchar(50)
)

Insert into #TableA Select Id1,MIN(Data1) From #mashup Group By Id1

Insert into #TableB (TableBId,TableAId, TableBData)
Select
id1,
Id2,
MIN(Data2)
From
#mashup
Group By
id1,
Id2

huntera
Starting Member

4 Posts

Posted - 2009-09-23 : 03:19:25

Thanks Sanoj,

This is not really a solution for me I'm afraid.

The tables I'm inserting into already have values in them, so the identity is already in place. Ok I suppose I could utilize the IDENTITY_INSERT ON but I still need to know what ID's I'd need to insert. The temporary ID's in the Mash-up table are generated on the fly, starting at 1. It would be too complicated to look at the last inserted value for each of the tables (TableA, B etc) to know what values to store in the Mash-up. Even if this were possible, there may be a case where someone inserts into one of the tables (A, B etc) whilst I am building the Mash-up, resulting in an out of order identity.

Hope that waffle makes sense.

Surely this must be a common problem for SQL developers? Looks like I may have to resort to the cursor after all :(

Is there a way to utilize the OUTPUT keyword in some way to get both IDs? I can certainly get the INSERTED.TableAId, but is there no way to get the Id1 from the Mash-up table too?

Cheers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources