Import data from CSV file using BULK INSERT and OPENROWSET

17. April 2024 Mehedi SQL Server , SQL Tips Comments (0)

Today I am going to show you how to import data from a CSV file using Transact-SQL BULK INSERT and the  OPENROWSET(BULK...) statement.

BULK INSERT

BULK INSERT allows importing data from a file into a SQL table. This is similar to SSMS IMPORT Flat file wizard or BCP command with "in" option.

Example

Let's put below test data in a csv file and name it as test.csv:

Name
Mehedi
Hasan
John
Richard
David

Now create a table with below schema:

CREATE TABLE [dbo].[Test](
	[name] [varchar](50) NULL
) ON [PRIMARY]
GO

Open the command prompt and create a XML format file using BCP utility.

bcp MehediTest.dbo.Test format nul -c -x -f C:\BCP\Test.xml -t, -T

This command will create a xml file in your C drive's BCP folder. This file maps the column and data types.

<?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="\r\n" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="name" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

Finally, execute below query in SSMS.

BULK INSERT dbo.Test  
   FROM 'C:\BCP\test.csv'  -- Source CSV file
   WITH (firstrow = 2, -- Discard the header from CSV file
   FORMATFILE = 'C:\BCP\Test.xml');  -- Format file

SELECT * FROM dbo.Test

Output



OPENROWSET

OPENROWSET with BULK option also enables to import data from file into a table. In addition, INSERT, UPDATE, or DELETE statement can be used to the target table.

Drop the previous table and create it with a additional column.

DROP TABLE [dbo].[Test];
GO
CREATE TABLE [dbo].[Test](
	[pk_id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
	[pk_id] ASC
)
) ON [PRIMARY]
GO

Open the command prompt and create the format file again.

bcp MehediTest.dbo.Test format nul -c -x -f C:\BCP\Test.xml -t, -T

Now open the C:\BCP\Test.xml and edit it.

Before

<?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="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="pk_id" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="name" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

After

<?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="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="2" NAME="name" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

Execute below query in SSMS and load only the selected column "name".

INSERT INTO dbo.Test
(name)
    SELECT *
    FROM OPENROWSET (
        BULK 'C:\BCP\test.csv',
        FORMATFILE = 'C:\BCP\Test.xml',  
        firstrow = 2
        ) AS t1;

SELECT * FROM dbo.Test

Output

Difference Between BULK INSERT and OPENROWSET(BULK...)

BULK INSERT is more suitable during transferring data directly from a CSV file into a table that precisely matches its structure. On the other hand, OPENROWSET offers the flexibility to query the CSV file directly. This means you can select specific columns, apply WHERE clauses, and even utilize a FORMATFILE to specify data types, lengths, and column names. Such control helps to write appropriate import operations, particularly when you do not want to import all the data in a specific manner.

Conclusion

Expecting BULK INSERT and OPENROWSET statements are clear to you now. Happy T-SQLing!

Tags:

Add comment