SQL Buckcopy

Question

I have a DB table with 10 fields. I have a .csv file containing 6 fields of the table. How can use bulk copy to insert those records into the DB? The file only has 6 of 10 fields that the DB table contains. Thanks.

Answer 1

You can set up a format file for the field mapping. Say that your CSV files has data for columns 1, 3, 4, 5, 7 and 10:

   9.0  
   6  
   1 SQLVARCHAR  0 0 "," 1 col1 ""  
   2 SQLVARCHAR  0 0 "," 3 col3 ""  
   3 SQLVARCHAR  0 0 "," 4 col4 ""  
   4 SQLVARCHAR  0 0 "," 5 col5 ""  
   5 SQLVARCHAR  0 0 "," 7 col6 ""  
   6 SQLVARCHAR  0 0 "\r\n" 10 col10 ""  

The command would be:

   BULK INSERT MyTable FROM 'C:\temp\MyCSVFile.txt'  
   WITH (FORMATFILE = 'C:\temp\MyCSVFile.fmt')  

Answer 2

Hi @2956 ,

Another approach would be to create a view with six columns on top of the destination table.
The view structure will match the *.csv file with six data elements per line.

Bulk insert will use that view to load data from the *.csv file.
Behind the scenes, the actual table will be populated.

BULK INSERT dbo.vMyView  
FROM  'e:\Temp\input_file.csv'  
...  

Answer 3

Use a stagging table matching the CSV content, bulk copy the CSV to it and then copy over the content to the final table.

Answer 4

Hi,@2956

csv is imported into a database table, usually using bulk insert, but you must know that using it requires the same matching fields, the typical approach is to load the data into a temporary table, then move it to the base table, modify it as appropriate, Refer this link and link2.

Bert Zhou