January 8, 2014 at 3:02 pm
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.
January 8, 2014 at 3:10 pm
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.
January 8, 2014 at 4:10 pm
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.
January 8, 2014 at 4:10 pm
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
January 8, 2014 at 4:27 pm
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.
January 8, 2014 at 8:16 pm
That makes sense. Looks like it's going to work, Thanks !
January 24, 2014 at 6:51 pm
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
Change is inevitable... Change for the better is not.
January 25, 2014 at 9:06 am
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.
January 25, 2014 at 3:57 pm
No problem. I just wanted to make sure that something wasn't going to break on you. Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply