Menu
I want to bulk insert columns of a csv file to specific columns of a destination table. Description - destination table has more columns than my csv file. So, I want the csv file columns to go to the right target columns using BULK INSERT.
Is this possible ? If yes, then how do I do it ?
I saw the tutorial and code at - http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
and http://www.codeproject.com/Articles/439843/Handling-BULK-Data-insert-from-CSV-to-SQL-Server
BULK INSERT dbo.TableForBulkData
FROM 'C:\BulkDataFile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
They don't show you how you can control where data is inserted.
647
INSERT INTO Syntax Specify both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...)
The INSERT statement also allows you to insert multiple rows into a table using a single statement as the following: INSERT INTO table_name(column1,column2…) VALUES (value1,value2,…), (value1,value2,…), … In this form, you need to provide multiple lists of values, each list is separated by a comma.
Bulk insert is generally much faster.
Yes, you can do this. The easiest way is to just create a View that Selects from the target table, listing the columns that you want the data to go to, in the order that they appear in the source file. Then BULK INSERT to your View instead of directly to the Table.
99
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
