BULK INSERT T-SQL Command to Import Data from Text or CSV File in SQL Server 2005

BULK INSERT T-SQL Command in SQL Server allows users to import data directly from CSV or Text file in to SQL Server 2005 right from Query Window. First of all, let’s have a quick look at syntax of BULK INSERT.

Syntax:


BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
        ( 
   [ [ , ] BATCHSIZE = batch_size ] 
      [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
      [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
        )] 


CustomerFile.txt

A Rahman,Khan,Kurigram
Kalim,khan,Kurigram
Shahnaj,Parveen,Dhaka

Now, I have a CustomerFile.txt file and I want to import this file contents in SQL Server. Let's jump in to coding directly.


-- Create Table Customer
CREATE TABLE Customer
(
	FirstName nvarchar(50) NULL,
	LastName nvarchar(50) NULL,
	City nvarchar(50) NULL
)  


-- BULK INSERT to import data from Text or CSV File
BULK INSERT Customer 
FROM 'C:\CustomerFile.txt' 
WITH 
(
 FIRSTROW = 1,
 FIELDTERMINATOR = ',', 
 ROWTERMINATOR = '\n'    
)


If you want have a look at those data before inserting, OPENROWSET T-SQL Command is here to do that. However, you will have to generate a FORMATFILE describing Table Definition. Please refer to this MSDN Link to generate a FORMATFILE for any specific Table.


-- OPENROWSET to view Data from Text or CSV File
SELECT *
FROM  OPENROWSET
(
BULK  'C:\CustomerFile.txt',
FORMATFILE='C:\CustomerDefinition.xml'
) CustomerTable;
 


-- Enable 'xp_cmdshell', if it requires
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

-- Export data directly to CSV or Text file from Query Window or SQLCMD
EXEC master..xp_cmdshell 'sqlcmd -S RAHIM\SQLEXPRESS -E -d TrialDB -Q "SELECT * FROM Customer" -o "C:\CustomerFile.txt" -W -w 999 -s "," '

CustomerFile.txt

FirstName,LastName,City
---------,--------,----
A Rahim,khan,Kurigram
A Karim,khan,Dhaka
A Rahman,Khan,Kurigram
Kalim,khan,Kurigram
Shahnaj,Parveen,Dhaka

Thanks
A Rahim Khan

Advertisements
  1. January 3rd, 2011

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: