October 10, 2008 at 12:08 pm
I'm trying, with some success, to implement a big data import operation using:
BULK INSERT Location
FROM 'C:\cssqlsdk\v6\locations.import'
WITH (
DATAFILETYPE='char'
)
Thinking this would be faster than having a program read the source data and INSERT all these rows.
But what do you do with a UniqueIdentifier column? The database defaults it to NewSequentialID. Do I leave a blank space between the tabs?
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
October 10, 2008 at 12:29 pm
Apparently, you just leave a blank in your text file between the tabs;)
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
October 10, 2008 at 3:32 pm
What I do in this situation is to create a view without the ID column and BCP/BulkInsert into that. Make sure to set permissions correctly on both the base table and the view though.
Gary Johnson
Sr Database Engineer
October 10, 2008 at 3:50 pm
Or create a format file to skip the column!
* Noel
October 10, 2008 at 4:24 pm
Noel,
That certainly works, but in our ETL system, the format file would be just one more thing for the Operations team to lose. Also, this way we can simply do our BCP in with the columns specified in the correct order in the view for the file coming in(not always the case when columns are added to a table later in the dev cycle) and also skip fields we don't care about in the incoming file. By using a view I am able to order the column names regardless of how they are in the table. This gives me a much more robust ETL system.
Gary Johnson
Sr Database Engineer
October 11, 2008 at 6:52 am
I like that view idea. Simpler than the format file. I also find that on some installations, the order of the columns in the database might be different. View will solve that too.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply