SQL Server script to Bulk Insert from a CSV file into a table

The following SQL Server script uses Bulk Insert to insert a CSV file into a target table via a staging table.

Here are steps:

1. Create Raw Staging Table With All Varchar Columns
2. Importing From CSV To Raw Staging Table
3. Create Target Table With Typed Columns
4. Copy Raw Staging Table To Target Table

-- ================================================
PRINT '1 CREATE RAW STAGING TABLE WITH ALL VARCHAR COLUMNS'

CREATE TABLE [dbo].[MyTableName_BULK_INSERT](
[SomeStringColumn] [nvarchar](255) NULL,
[SomeIntColumn] [nvarchar](255) NULL
) ON [PRIMARY]


-- ================================================
PRINT '2 IMPORTING FROM CSV TO RAW STAGING TABLE'

BULK INSERT MyTableName_BULK_INSERT
FROM 'C:\Temp\MySourceFile.csv'
WITH
(
FIRSTROW = 2,  -- If has a header row
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
MAXERRORS = 0,
ERRORFILE = 'C:\Temp\Bulk_Insert_Errors.log',
CODEPAGE = 'ACP',
DATAFILETYPE = 'widechar'
)

-- select count(*) from MyTableName_BULK_INSERT
-- select top 10 * from MyTableName_BULK_INSERT


-- ================================================
PRINT '3 CREATE TARGET TABLE WITH TYPED COLUMNS'

CREATE TABLE [dbo].[MyTableName](
[SomeStringColumn] [varchar](255) NOT NULL,
[SomeIntColumn] [int] NOT NULL
 CONSTRAINT [PK_MyTableName] PRIMARY KEY CLUSTERED 
 ([SomeStringColumn] ASC)
  WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)


-- ================================================
PRINT '4 COPY RAW STAGING TABLE TO TARGET TABLE'

INSERT INTO [MyTableName]
([SomeStringColumn]
,[SomeIntColumn])
(SELECT 
[SomeStringColumn]
,[SomeIntColumn]
FROM [MyTableName_BULK_INSERT])

-- select count(*) from [MyTableName]
-- select top 10 * from [MyTableName]


-- ================================================
PRINT 'FINISHED'

Using bcp command to export and import a SQL Server table between databases

Here's how to use the bcp command line tool to export a SQL Server table to disk and then import into another table that may be on a different database and server.


1) Script the table definition and create in the target database (Right click table > Script table as)
CREATE TABLE [dbo].[MyExampleTable](
[Id] [uniqueidentifier] NOT NULL,
[DateCreated] [datetime] NULL,
[DateUpdated] [datetime] NULL,
[OtherExampleColumn] [varchar](100) NULL
)


2) Export source table to disk using bcp:
bcp MySourceDatabaseName.dbo.MyExampleTable out C:\SomeFolder\MyExampleTable.dat -c -t, -S localhost -T


3) Import file into target SQL table using bcp:
bcp MyTargetDatabaseName.dbo.MyExampleTable in C:\SomeFolder\MyExampleTable.dat -c -t, -S localhost -T


For further options and details, see this link:
https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/

Export SQL Server table to csv file with headers using bcp

Ref: http://pastebin.com/x8Kk4Dn9
Ref: http://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an/9754485#9754485


I use a method that outputs one file for the column headers read from INFORMATION_SCHEMA.COLUMNS and then appends a second file with the table data, both of which are generated using BCP.

Here is the batch file that creates TableData.csv, just replace the environment variables at the top.

Note that if you need to supply credentials, replace the -T option with -U my_username -P my_password

set BCP_EXPORT_SERVER=put_my_server_name_here
set BCP_EXPORT_DB=put_my_db_name_here
set BCP_EXPORT_TABLE=put_my_table_name_here

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='%BCP_EXPORT_TABLE%'; select @colnames;" queryout HeadersOnly.csv -c -T -S%BCP_EXPORT_SERVER%

BCP %BCP_EXPORT_DB%.dbo.%BCP_EXPORT_TABLE% out TableDataWithoutHeaders.csv -c -t, -T -S%BCP_EXPORT_SERVER%

set BCP_EXPORT_SERVER=
set BCP_EXPORT_DB=
set BCP_EXPORT_TABLE=

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv

del HeadersOnly.csv
del TableDataWithoutHeaders.csv

This method has the advantage of always having the column names in sync with the table by using INFORMATION_SCHEMA.COLUMNS. The downside is it's a bit messy and creates temporary files. Microsoft should really fix the bcp utility to support this.

It uses the row concatenation trick from Concatenate many rows into a single text string? combined with ideas from http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/812b8eec-5b77-42a2-bd23-965558ece5b9/