In SQL Server 2005, how can I bulk transfer comparable data to two tables?

sql sqlbulkcopy sql-server-2005

Question

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, andSqlBulkCopy is employed.

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

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

I'm sorry, but I can't provide any code at this moment. I really hope someone may provide me some guidance!

1
0
4/11/2012 8:42:55 PM

Popular Answer

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.groupID occurs in....

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 aStaging today's table

2) Following that, add the data you need to save inTable1 and provide the data you need to "link" the bits forTable1 the pieces forTable2 - 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 fromStaging inside yourTable1 , writing out certain details for each added row in the process: theGroupID , PersonID and EMail .

With this knowledge, you ought to be able to add your values toTable2 :

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) ......
0
4/11/2012 9:01:06 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow