DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE