Create a table and insert data from a text file:
IF OBJECT_ID('TestTable') IS NOT NULL DROP TABLE TestTable GO CREATE TABLE TestTable ( ID INT NOT NULL DEFAULT 0 PRIMARY KEY, Test VARCHAR(100) NOT NULL DEFAULT ) ON [PRIMARY] GO BULK INSERT TestTable FROM 'C:\Temp\TestFile.txt' WITH ( FIRSTROW = 1, MAXERRORS = 0, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' ) GO
Import a text file using BULK INSERT WITH FORMATFILE:
File to import (test.txt):
1,0.00," 6-SEP-01" 2,2.00," 4-SEP-01"
Format file (test.fmt):
8.0 3 1 SQLCHAR 0 800 "," 1 C1 "" 2 SQLCHAR 0 800 ",\"" 2 C2 "" 3 SQLCHAR 0 800 "\"\r\n" 3 C3 ""
The script:
CREATE TABLE Test ( i INT, m MONEY, d DATETIME ) GO BULK INSERT Test FROM 'C:\test.txt' WITH (FORMATFILE='C:\test.fmt')