How to bulk copy similar data to two tables in SQL Server 2005?

Question

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)?

Example:

Excel-import:

Name
Email
plus more

Table 1

name
personID (GUID)
groupID (same for all imported members)
+ other attributes

Table2

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!

You're right - you cannot do this, bulk import only works into a single table. So basically do this: (1) bulk import your data into a "staging" table - as today. Then (2) split up that table, from the staging table, into the two actual tables — marc_s, Apr 11 '12 at 20:43
How and where do you get that `GroupID` from??? And what exactly are you doing? Inserting from "Excel import" into `Table1` and then you need to get back some data from that table to insert into `Table2` ?? Could you try to explain for a single row from your Excel import exactly what you're doing?? All the steps, all the data involved... — marc_s, Apr 11 '12 at 20:50

score 0 · Answer 1 · answered Apr 11 '12 at 21:01

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 GroupID, PersonID and EMail.

With this information, you should be able to also insert your values into Table2 :

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) ......

How to bulk copy similar data to two tables in SQL Server 2005?

1 Answers1