I've 'inherited' some code that I am having a hard time working with. The application has an excel import function to import members to a members table, and
SqlBulkCopy is used.
A few months ago a junction table was added and two of the attributes in the members table has to be added there as well for the application to work properly. There are no hard coupling (no PK, FK - not my choice!).
I am not sure how to solve this, because as far as I know you can't bulkcopy to two tables, you will have to do it separately. But how can I retrieve the GUID attribute of the newly imported members as well as the other attribute values (groupId) in the best way (low impact on performance)?
Name Email plus more
name personID (GUID) groupID (same for all imported members) + other attributes
personID (GUID) groupID (same for all imported members) + other 'new' attributes
Sorry I can't provide any code this time :/ Really hope somebody can give me any advice!
It's not quite clear what exactly and how exactly you're trying to store the data from your Excel import into these two tables, and where that
groupID comes from....
Basically - you're right - you cannot bulk insert into multiple tables. So my take would be:
1) Do the bulk insert from Excel into a
Staging table as today
2) Then insert those pieces of information you need to store in
Table1 and output the necessary info that you need to "connect" the bits for
Table1 to the bits for
Table2 - something along the lines of:
DECLARE @Connection TABLE (GroupID INT, PersonID UNIQUEIDENTIFIER, EMail VARCHAR(500)) INSERT INTO dbo.Table1 (list of columns here) OUTPUT Inserted.GroupID, Inserted.PersonID, Inserted.EMail INTO @Connection(GroupID, PersonID, EMail) SELECT (list of columns here) FROM dbo.Staging WHERE (possibly a condition here???)
This would insert the rows from
Staging into your
Table1, and while doing so, it will write out some information for each row inserted - the
With this information, you should be able to also insert your values into
INSERT INTO dbo.Table2 (GroupID, PersonID, EMail, list of other columns here) SELECT c.GroupID, c.PersonID, c.EMail, (list of other columns from Staging table here) FROM @Connection c INNER JOIN dbo.Staging s ON c.EMail = s.EMail -- or use whatever you can use to -- connect the two sets of data WHERE (condition) ......