|
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 |
