July 10, 2006 at 8:44 am
Hi all,
I'm having a wee bit of difficulty with a bulk insert task.
What it is, is... We have 2 tables. Table 1 has 2 identity columns ID (PK) and unitCode. Table 2 has a column that is a foreign key to the ID in table 1.
I want to do a bulk insert to table 2 but can only be sure of the value of unitCode. We have multiple databases so PK values are not known.
I would like to stick the ID in a variable (using: set intID = select ID from table1 where unitCode = 'x') and then use this value to populate the foreign key column via the bulk insert statement. The data for bulk insert would come from a CSV file with an FMT file specifying the column positions.
The trouble is, I don't know how. Any ideas?
July 11, 2006 at 8:53 am
I worked it out...
1. Create temporary table
2. Bulk insert to temp table from file
3. Change FK values
4. Insert contents of temp table into real table
5. Drop temp table
Seems to work okay.
July 11, 2006 at 8:55 am
Robert
Or create a view of the two joined tables and update that?
John
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply