How can I write bulk insert T-SQL query for this flat file
7273|80110200|1
8152|58130103|1
10715|87480302|1
20462|76991103|1
21964|72159903|1
25537|59219900|1
30600|58120310|1
I want to know how I can define the field terminator and row terminator
Try something like this:
BULK INSERT test.dbo.bulkinserttest -- your target table here (db).(schema).(tablename)
FROM 'd:\test.txt' -- that's the path to your file with the data
WITH
(
CODEPAGE = 'ACP',
DATAFILETYPE = 'char',
FIELDTERMINATOR ='|',
ROWTERMINATOR = '\n'
)
This is straight from the wonderful MSDN Books Online documentation which you can check for further details.