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