FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to import a flat file into the database?

Execute the following T-SQL scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate the import of the content of flat files into temporary and permanent tables:

Security Warning: Writing Secure Transact-SQL

-- SQL import file line by line using INSERT EXEC - SQL server import text file

-- Microsoft SQL Server T-SQL xp_cmdshell - extended system procedure

USE AdventureWorks2008;

CREATE TABLE #FlatFile (

  ID   INT    IDENTITY ( 1 , 1 ),

  Line VARCHAR(128))

 

INSERT #FlatFile (Line)

EXEC MASTER..xp_cmdshell  'type f:\data\SUPPReport2009.txt'

-- (507 row(s) affected)

 

SELECT *

FROM   #FlatFile

GO

 

-- Cleanup

DROP TABLE #FlatFile

 

-- MSSQL import file line by line using BULK INSERT

USE AdventureWorks2008;

 

CREATE TABLE #FlatFile (

  Line VARCHAR(128))

 

BULK INSERT #FlatFile

FROM 'F:\data\export\Department.txt'

 

SELECT *

FROM   #FlatFile

 

GO

 

/* Partial results

 

Line

DepartmentID,Name,GroupName,ModifiedDate

1,Engineering,Research and Development,2008-02-19 15:38:58.470000000

2,Tool Design,Research and Development,2008-02-19 15:38:58.470000000

*/

-- Cleanup

DROP TABLE #FlatFile

GO

------------

 

-- SQL Server import csv file into database using BULK INSERT

-- CSV - Comma Separated Values

-- SQL Server .fmt (format) file is bcp format file - T-SQL dynamic SQL, dynamic query

DECLARE  @DynamicSQL NVARCHAR(512)

 

DECLARE  @FlatFilePath VARCHAR(64),

         @bcpFormatFilePath VARCHAR(64)

 

SET @FlatFilePath = 'e:\data\inventory20120201.txt'

 

SET @bcpFormatFilePath = 'e:\data\inventory.fmt'

 

SET @DynamicSQL = 'BULK INSERT [BookInventory] FROM ''' + @FlatFilePath + ''' WITH (formatfile = ''' + @bcpFormatFilePath + ''')'

 

PRINT @DynamicSQL -- test & debug

 

/* 

BULK INSERT [BookInventory] FROM 'e:\data\inventory20120201.txt' 

WITH (formatfile = 'e:\data\inventory.fmt')

*/

EXEC SP_EXECUTESQL   @DynamicSQL

GO

------------
------------
-- SQL Server T-SQL BCP command for data export/import to/from flat file
------------
/* Command prompt:
bcp AdventureWorks.Production.Product out c:\export\Prod1.csv -c -S SRVRNAME -T
*/
-- Windows authentication (-T) - exporting table
EXEC xp_cmdshell 'bcp AdventureWorks.Production.Product out c:\export\Prod1.csv -c -S SRVRNAME -T';
/*
NULL
Starting copy...
NULL
504 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 47     Average : (10723.40 rows per sec.)
NULL
*/
-- Exporting Northwind db table data with bcp
EXEC xp_cmdshell 'bcp Northwind.dbo.Products out c:\export\Prod2.csv -c -S SRVRNAME -T';
-- 77 rows copied.
 
-- Windows authentication (-T) - importing table with bcp
-- Create empty table with SELECT INTO
SELECT TOP(0) * INTO tempdb.dbo.Prod2 FROM Northwind.dbo.Products
EXEC xp_cmdshell 'bcp tempdb.dbo.Prod2 in c:\export\Prod2.csv -c -S SRVRNAME -T';
-- 77 rows copied.
DROP TABLE tempdb.dbo.Prod2
 
-- SQL Server authentication - exporting table
EXEC xp_cmdshell 'bcp AdventureWorks.Production.Product out c:\export\Prod1.csv -c -S SRVRNAME -Usa -PLondon007';
-- 504 rows copied.
 
-- BCP exporting query result
EXEC xp_cmdshell 'bcp "SELECT * FROM AdventureWorks.Production.Product WHERE ListPrice > 0" queryout c:\export\Prod1.csv -c -S SRVRNAME -T';
-- 304 rows copied.

------------

-- SQL Server .fmt (bcp format) file content example

-- 8.0 refers to SQL Server version number (SQL Server 2000)

/*

8.0

25

1       SQLINT        0       4       ""                        1     ProductID                 ""

2       SQLNCHAR      2       100     ""                        2     Name                      SQL_Latin1_General_CP1_CI_AS

3       SQLNCHAR      2       50      ""                        3     ProductNumber             SQL_Latin1_General_CP1_CI_AS

4       SQLBIT        0       1       ""                        4     MakeFlag                  ""

5       SQLBIT        0       1       ""                        5     FinishedGoodsFlag         ""

6       SQLNCHAR      2       30      ""                        6     Color                     SQL_Latin1_General_CP1_CI_AS

7       SQLSMALLINT   0       2       ""                        7     SafetyStockLevel          ""

8       SQLSMALLINT   0       2       ""                        8     ReorderPoint              ""

9       SQLMONEY      0       8       ""                        9     StandardCost              ""

10      SQLMONEY      0       8       ""                        10    ListPrice                 ""

11      SQLNCHAR      2       10      ""                        11    Size                      SQL_Latin1_General_CP1_CI_AS

12      SQLNCHAR      2       6       ""                        12    SizeUnitMeasureCode       SQL_Latin1_General_CP1_CI_AS

13      SQLNCHAR      2       6       ""                        13    WeightUnitMeasureCode     SQL_Latin1_General_CP1_CI_AS

14      SQLDECIMAL    1       19      ""                        14    Weight                    ""

15      SQLINT        0       4       ""                        15    DaysToManufacture         ""

16      SQLNCHAR      2       4       ""                        16    ProductLine               SQL_Latin1_General_CP1_CI_AS

17      SQLNCHAR      2       4       ""                        17    Class                     SQL_Latin1_General_CP1_CI_AS

18      SQLNCHAR      2       4       ""                        18    Style                     SQL_Latin1_General_CP1_CI_AS

19      SQLSMALLINT   1       2       ""                        19    ProductSubcategoryID      ""

20      SQLINT        1       4       ""                        20    ProductModelID            ""

21      SQLDATETIME   0       8       ""                        21    SellStartDate             ""

22      SQLDATETIME   1       8       ""                        22    SellEndDate               ""

23      SQLDATETIME   1       8       ""                        23    DiscontinuedDate          ""

24      SQLUNIQUEID   1       16      ""                        24    rowguid                   ""

25      SQLDATETIME   0       8       ""                        25    ModifiedDate              ""

 

*/

------------

 

-- SQL Server BULK INSERT import special character limited flat file

-- MSSQL dynamic query - dynamic sql

USE tempdb;

CREATE TABLE Product (

      ProductName       varchar(64),

      Color             varchar(16),

      ListPrice         smallmoney)

GO

 /*  Content of ProductExport.txt

 

ProductName|Color|ListPrice

....

AWC Logo Cap|Multi|8.99

Long-Sleeve Logo Jersey, S|Multi|49.99

Long-Sleeve Logo Jersey, M|Multi|49.99

Long-Sleeve Logo Jersey, L|Multi|49.99

Long-Sleeve Logo Jersey, XL|Multi|49.99

HL Road Frame - Red, 62|Red|1431.5

HL Road Frame - Red, 44|Red|1431.5

HL Road Frame - Red, 48|Red|1431.5

HL Road Frame - Red, 52|Red|1431.5

HL Road Frame - Red, 56|Red|1431.5

LL Road Frame - Black, 58|Black|337.22

....

*/

DECLARE @DynamicSQL nvarchar(max), @PathFileName varchar(256)

SET @PathFileName = 'f:\data\import\csv\ProductImport.txt'

-- SQL string concatenation - string concat - string append

SET @DynamicSQL = 'BULK INSERT Product FROM '''+@PathFileName+

            ''' WITH (FIELDTERMINATOR = ''|'', FIRSTROW=2) '

PRINT @DynamicSQL -- test & debug

-- Dynamic SQL execution

EXEC sp_executesql @DynamicSQL

GO

 

-- SQL TOP function - newid random number generator function

SELECT TOP (5) *

FROM Product

ORDER BY NEWID()

GO

/* Results

 

ProductName                   Color             ListPrice

HL Mountain Pedal             Silver/Black      80.99

ML Mountain Frame - Black, 38 Black             348.76

HL Touring Frame - Blue, 54   Blue              1003.91

Touring-1000 Blue, 46         Blue              2384.07

Road-250 Black, 44            Black             2443.35

*/

-- Cleanup - sql delete table - mssql drop table

DROP TABLE tempdb.dbo.Product

GO

------------

Related articles:

How to read notepad text file into a table?

The Data Loading Performance Guide

Exam Prep 70-461
Exam 70-461