Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Bulk Insert into table with more columns

I do a daily import of millions of rows so would prefer to use BULK INSERT.  

I would like to import the csv files into a table with two extra columns not in the source files, but it seems as though BULK INSERT does not allow this.  Are there any functions or properties of BULK INSERT that  allow this.  

It is imperative to use BULK INSERT over normal import as this reduces the import time from hours to minutes.


I've thought about importing into a temporary table on the SQL server db, and then moving this across to the actual table, but this also slows the daily import process quite considerably

Any ideas?
0
charb0y
Asked:
charb0y
1 Solution
 
OtanaCommented:
1)  I suppose the csv files are generated somehow? Maybe you could add two columns just to get it imported?
2) Maybe you could drop the columns, do the insert, and then recreate the columns? This would only work if there is no data in the columns of course.
0
 
patriktCommented:
Doin't need drop columns or other crazy think.
Read help and look fro BULK INSERT ...WITH FORMATFILE=...
FORMATFILE  is file where you can simply specify which columns miss or should be omited.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now