Unanswered: Bulk Import into multiple tables with foreign keys
I'm writing a tool to bulk import log files (application activity logs). I've normalized my tables, but I can't figure out how BULK INSERT, OPENROWSET can work with normalized tables. For instance, the logs contain a UserId field. So I have a separeate table for UserIds with a FK relationship to my main table.
There doesn't seem to be an elgant way to tell BULK IMPORT/OPENROWSET to put some of the data into the main table and some of the data into the Users table. I was thinking I could bulk import twice. once into the main table and once in the the users table with the right format file to skip columsn, but how do I populate the FK (mainTable.UserId) with the coresponding ID from ther usersTable).
I'm thinking that this is going to be a hack fest (C# or StoredProc), but before I go down that road I want to make sure I'm not missing anything.
+1, they are the "cursors" of data loading. I believe anything you do via DTS/SSIS can be done by a stage then load model using BCP/Bulkcopy and T-SQL, albeit a bit more time consuming to develop, but certainly more portable with fewer interfaces. MS went backwards from DTS to SSIS, from the words of Geddy Lee, I refuse to conform, so I shall be cast out.
I believe a majority of us who came from the UNIX Sybase/Oracle world would agree with Brett.