I am trying to import csv files in existing table in SQL server. I managed to used import data wizard which provides the great features to import csv files and map to the columns in the existing table. However, the issue here is that table has 5 data fields contents but the csv file has 4 data field content. I wish to add this extra field and hardcode a specific value to it as in table column is defined as not null.
1 Answer
You need to follow the below steps to import CSV file to SQL Server table with additional column.
- Say you are having a table with two columns (FirstName, LastName). You can insert them using BULK INSERT.
CREATE TABLE NameList
(
FirstName varchar(40),
LastName varchar(40)
)
- You have got Format File which defines two columns to insert for target table.
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="40" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
- If you want to add new column with NOT NULL, You can add a new column with default value and do bulk insert as given below:
ALTER TABLE dbo.NameList ADD Test varchar(30) not null default 'test'
- BULK INSERT to the table will add default values for additional column test
TRUNCATE TABLE dbo.NameList; -- (for testing)
BULK INSERT dbo.NameList
FROM 'c:\dev\NameList.csv'
WITH (FORMATFILE = 'C:\dev\NameListFormat.xml');
GO
Now, you will be having default values for the additional column.
