Some code that I 'inherited' is difficult for me to deal with. The program offers an Excel import feature that allows users to add members to a members table, and
A junction table was built a few months ago, and for the program to function correctly, two of the members table properties must also be placed there. No hard connection exists (no PK or FK - not my decision!).
Since you can't bulkcopy to two tables, you will need to do it independently. I'm not sure how to fix this. But how can I efficiently (with little performance effect) access the GUID property of the recently imported members in addition to the other attribute data (groupId)?
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
I'm sorry, but I can't provide any code at this moment. I really hope someone may provide me some guidance!
It's not quite apparent what you're attempting to do with the data from your Excel import in these two tables or how and where you're trying to save it.
In essence, you are correct; bulk inserting into many tables is not possible. So, here's what I'd say:
1) Insert data in bulk from Excel into a
2) Following that, add the data you need to save in
and provide the data you need to "link" the bits for
the pieces for
- maybe something like:
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???)
By doing so, the rows from
, writing out certain details for each added row in the process: the
With this knowledge, you ought to be able to add your values to
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) ......