How do I get bulk insert to recognize end of row?
Posted on 2010-08-15
I have started having trouble with a more complex bulk insert stored procedure. So I decided to build the sp from the basic and now I'm having trouble with a very simple one. I should note I'm used to sql srvr 2005 and am now using 2008 if that makes a difference. The txt file is tab delimited. It looks like this:
aaa bbb ccc
ddd eee fff
ggg hhh iii
When transfered from the temp table to the db table I get this in the db table
last_name first_name middle_name
aaa bbb cccdddeeefffggghhhiii
I have tried the insert with the terminators below -- also with the row terminator \r -- and with no terminators. I always get the same results.
I need to get this complex bulk insert I started done tonight so in keeping with only asking one question at a time there will be at least one follow up question. Everything will be worth 500 pts. I'll make a closing comment at the end of this after solution for title and location of next issue.
Below is the code for the sp:
ALTER PROCEDURE [dbo].[uspImportStudentInfoNoDuplicates]
-- Add the parameters for the stored procedure here
-- @p1 int = 0,
-- @p2 int = 0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE ImportStudentTempTable
BULK INSERT ImportStudentTempTable
FROM *****This was the path and txt file location
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
-- Insert the new student records
INSERT INTO ImportDataTest
SELECT last_name, first_name, middle_name
DROP TABLE ImportStudentTempTable