Bulk Insert Query useful for Test Data

Bulk insert Query

To bulk upload data from csv to sql tables, import csv file to sql table or load comma deliminated file into sql, use BULK INSERT query. This is very useful when you want to insert test data from csv files to sql.

Follow below steps.

• Create database with TestDatabase
• Create table with CSVTest using below query
• Create csvtest.txt file in C:
• If your database server is local then file keep in local C:
• If your database server is remote then file keep in remote database server’s C:
• Value of txt file should be in order of your table structure (refer example)
• Run bulk insert query
• Verify content of your table

--use current database

use TestDatabase

--create csvtest table with fields

GO

CREATE TABLE CSVTest

(ID INT,

FirstName VARCHAR(40),

LastName VARCHAR(40),

BirthDate SMALLDATETIME)

GO

-- create csvtest.txt file in c:\csvtest.txt with

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202

--bulk insert query from text file

BULK

INSERT dbo.CSVTest

FROM 'c:\test.txt'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

GO

--Check the content of the table.

SELECT *

FROM CSVTest

GO