BULK INSERT into specific columns?

Question

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.

Answer 1

Really late to the party here, but I just had a situation that this was the right answer for and judging from the OP comments I thought the answer could use a little more clarification, so here goes.

The way this works is you have your permanent table you want the data from the .csv file to end up in. What you do is create a view of that table that only has the columns that match up with the columns in your .csv file. Then you upload the data from your .csv file to the view and behind the scenes Sql Server puts that data into your permanent table.

My situation is that I have a table where I have more columns than are in the source .csv file. Specifically, I had a GUID column I wanted to have the default value of NEWSEQUENTIAL() and also an INT column I wanted to have the default value of IDENTITY(1,1) for. This is a problem when trying to insert values from a .csv file because I couldn't find a way to insert the values from the .csv file while allowing the two columns in my permanent table (the GUID and the INT column) to be able to use the default values that would increment automatically.

Here's an example of the CREATE TABLE statement that will create my table:

CREATE TABLE [dbo].[MyBaseTable](
    [MyBaseTableGUID] UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
    [MyBaseTableRowID] INT IDENTITY(1,1),
    [SomeNumber] INT NOT NULL,
    [SomeText1] NVARCHAR(10) NULL,
    [SomeText2] NVARCHAR(50) NULL,
    [SomeText3] NVARCHAR(50) NULL
GO

Here is an example of the data in my .csv file:

SomeNumber,SomeText1,SomeText2,SomeText3 
4,Hey,Hi,Hello 
2,Bye,Later,Good Bye
7,Yo,Sup,What's Up

To create the view of my table in Sql Server Management Studio (SSMS), I followed the instructions at this documentation page from Microsoft. On step 5 of these instructions, I selected all of the columns from my table except for the 2 columns that represented my GUID and my INT columns (so for my example when I created the view I selected the columns SomeNumber, SomeText1, SomeText2 and SomeText3 to be created in the view). For this example we'll say I named the view of MyBaseTable as View_MyBaseTable.

Then here is the BULK INSERT statement I used to insert the data from my .csv file into the view I just created:

BULK INSERT [dbo].[View_MyBaseTable]
FROM 'C:\\FullPathToMyCsvFile\MyCsvFile.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
)
GO

Then when this completes, I can query MyBaseTable and all of the data from MyCsvFile.csv is in there and the GUID and INT columns are populated with the incrementing values like I wanted.

One thing to note: if you are doing a BULK INSERT like this, the .csv file needs to be in a location that the machine running the instance of Sql Server is on has access to. So, if your Sql Server database is on a network server, the .csv file needs to be either on a machine that has a network share that is visible to the Sql Server or else it needs to be directly on the server running the Sql Server instance.

I hope this clears up the solution and gives a more clear explanation of what is happening and how to do it.

Answer 2

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.