Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Populating Multiple Tables with Same ID# Expand / Collapse
Author
Message
Posted Wednesday, January 08, 2014 3:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
We have a web application in which a member can sign up, get a member ID, then that ID populates 6 various tables. That is fine 1 at a time.

But I have a batch of records (say 500 or so) that I need to insert into all 6 tables at once. The main member table has ID as identity column to generate the next #, then that # needs to be inserted into the other 5 tables for each record ... so assuming I already have 1000 records, then Id 1001-1500 will be inserted in all tables. There are no foreign keys involved.

Is there a good way to insert all 500 rows into each of the 6 tables at once so they each get the corresponding ID# for each record ?

Otherwise I could do a loop and read 1 record at a time from my import table, so 1 new ID# gets created and inserted into the 6 tables, then loop and repeat.



Post #1529118
Posted Wednesday, January 08, 2014 3:10 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:01 PM
Points: 3,081, Visits: 11,229
homebrew01 (1/8/2014)
We have a web application in which a member can sign up, get a member ID, then that ID populates 6 various tables. That is fine 1 at a time.

But I have a batch of records (say 500 or so) that I need to insert into all 6 tables at once. The main member table has ID as identity column to generate the next #, then that # needs to be inserted into the other 5 tables for each record ... so assuming I already have 1000 records, then Id 1001-1500 will be inserted in all tables. There are no foreign keys involved.

Is there a good way to insert all 500 rows into each of the 6 tables at once so they each get the corresponding ID# for each record ?

Otherwise I could do a loop and read 1 record at a time from my import table, so 1 new ID# gets created and inserted into the 6 tables, then loop and repeat.


Use the OUTPUT clause to capture the ID column value (and other needed information) from the batch insert into the first table into an output temp table, and then use that information to insert into the other 5 tables. You should only need 5 inserts to handle any size batch.

Of course it should be done in a single transaction around all the inserts to make sure you don't create a big mess.

Without details of the data and tables, I can't really give you something more specific.




Post #1529121
Posted Wednesday, January 08, 2014 4:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:37 AM
Points: 80, Visits: 135
The other option (not as optimal using OUTPUT) is to generate these IDs yourselves. You could prepare the data sets and insert them into the main tables with INSERT for each table. This method assumes that the INSERT process is single threaded and there is only one place in the application to populate these tables. Even if there is a slightest possibility of data being inserted from other method, dont use this method.


Post #1529139
Posted Wednesday, January 08, 2014 4:10 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
I tried something like this, and the temp table does contain the new MemID values, but get the error " Must declare the scalar variable "@MemberID"." during the 2nd insert


DECLARE @MemberID table( MemID int)

-- Insert into main Member table with identiky ID
--Member.Mem_ID is identity column automatically incremented
insert into Member
(CreateDate,
FirstName,
LastName,
MemberType)
OUTPUT INSERTED.Mem_ID INTO @MemberID
Select Getdate(), FName, LName, MType from MemberImport order by Email

-- Insert into 2nd table
Insert into MemberEmail
(Mem_ID, -- comes from Member Insert
EmailAddress)
select @MemberID.MemID, Email from MemberImport order by Email

-- Insert into 3rd table
Insert into MemberMeeting
(Mem_ID, -- comes from Member Insert
MeetingDate,
MeetingName)
select @MemberID.MemID, MDate, MName from MemberImport order by Email




Post #1529140
Posted Wednesday, January 08, 2014 4:27 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:01 PM
Points: 3,081, Visits: 11,229
homebrew01 (1/8/2014)
I tried something like this, and the temp table does contain the new MemID values, but get the error " Must declare the scalar variable "@MemberID"." during the 2nd insert


DECLARE @MemberID table( MemID int)

-- Insert into main Member table with identiky ID
--Member.Mem_ID is identity column automatically incremented
insert into Member
(CreateDate,
FirstName,
LastName,
MemberType)
OUTPUT INSERTED.Mem_ID INTO @MemberID
Select Getdate(), FName, LName, MType from MemberImport order by Email

-- Insert into 2nd table
Insert into MemberEmail
(Mem_ID, -- comes from Member Insert
EmailAddress)
select @MemberID.MemID, Email from MemberImport order by Email

-- Insert into 3rd table
Insert into MemberMeeting
(Mem_ID, -- comes from Member Insert
MeetingDate,
MeetingName)
select @MemberID.MemID, MDate, MName from MemberImport order by Email



You need to capture columns in your OUTPUT clause that allow you to join back to the MemberImport table, and then use those to join MemberImport to @MemberID in your remaining inserts.

Post #1529145
Posted Wednesday, January 08, 2014 8:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
That makes sense. Looks like it's going to work, Thanks !


Post #1529189
Posted Friday, January 24, 2014 6:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 35,949, Visits: 30,231
homebrew01 (1/8/2014)
That makes sense. Looks like it's going to work, Thanks !


I might be missing something but, even if you did get it working, I'm not seeing anything in the 2nd or 3rd insert that absolutely guarantees that the correct @MemberID.MemID will be used for any given row of the MemberImport table. While it might look like it works for now, I can see it silently failing sometime in the future.

What did you end up doing?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534690
Posted Saturday, January 25, 2014 9:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
Jeff, the code above is really just a snippet example using the OUTPUT to a temp table.

In the real code, the import table also has a RecNum identity column. During the first insert with the OUTPUT, the RecNum value is put into the @MemberID Temp Table RecNum column.

The following inserts join to @MemberID by RecNum to get the correct record.

Sorry if I'm not clearly explaining it.



Post #1534728
Posted Saturday, January 25, 2014 3:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 35,949, Visits: 30,231
No problem. I just wanted to make sure that something wasn't going to break on you. Thanks for the feedback.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534754
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse