July 4, 2011 at 3:21 pm
Attempting to do a bulk insert into table below
TABLE CITIES
[ccode] [char](2) NULL,
[city] [varchar](100) NULL,
[rcode] [char](2) NULL,
[iguid] [uniqueidentifier] NULL,
[uguid] [uniqueidentifier] NULL,
[rguid] [uniqueidentifier] NULL
With the following command
bulk insert cities
from 'c:\worldcities1a.txt'
with
(
fieldterminator = ',',
rowterminator = ''
)
worldcities1a.txt data example
'ad','Aixàs','06'
'ad','Aixirivali','06'
'ad','Aixirivall','06'
'ad','Aixirvall','06'
'ad','Aixovall','06'
'ad','Andorra','07'
'ad','Andorra la Vella','07'
'ad','Andorra-Vieille','07'
'ad','Andorre','07'
'ad','Andorre-la-Vieille','07'
'ad','Andorre-Vieille','07'
'ad','Ansalonga','04'
'ad','Anyós','05'
Getting the following errors if either "rowterminator = ''" or "rowterminator = '\r'"
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ccode).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 1 (ccode).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 3, column 1 (ccode).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 4, column 1 (ccode).
Msg 4863, Level 16, State 1, Line 1
Any help?
July 4, 2011 at 6:29 pm
There is a syntax error with your CREATE TABLE Statement.
Also you have a syntax error on your Terminators of your Bulk Insert.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 4, 2011 at 6:32 pm
Thanks
July 4, 2011 at 6:38 pm
I would recommend using a pipe "|" for your row terminator. If you have a comma in your text it messes up the load.
You row terminator is not being recognized.
HTH.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 4, 2011 at 7:12 pm
CREATE TABLE CITIES (
ccode varchar(200) NULL,
[city] [varchar](100) NULL,
[rcode] varchar(200) NULL,
[iguid] [uniqueidentifier] NULL,
[uguid] [uniqueidentifier] NULL,
[rguid] [uniqueidentifier] NULL
)
Conternts of TxtFile3.txt
ad,Aixàs,06,,,
ad,Aixirivali,06,,,
ad,Aixirivall,06,,,
ad,Aixirvall,06,,,
ad,Aixovall,06,,,
ad,Andorra,07,,,
ad,Andorra la Vella,07,,,
ad,Andorra-Vieille,07,,,
ad,Andorre,07,,,
ad,Andorre-la-Vieille,07,,,
ad,Andorre-Vieille,07,,,
ad,Ansalonga,04,,,
ad,Anyós,05,,,
bulk insert cities
from 'C:\SSIS\TxtFile3.txt'
with
(
fieldterminator = ',',
ROWTERMINATOR = ''
)
(13 row(s) affected)
SELECT * FROM CITIES
adAixas06NULLNULLNULL
adAixirivali06NULLNULLNULL
adAixirivall06NULLNULLNULL
adAixirvall06NULLNULLNULL
adAixovall06NULLNULLNULL
adAndorra07NULLNULLNULL
adAndorra la Vella07NULLNULLNULL
adAndorra-Vieille07NULLNULLNULL
adAndorre07NULLNULLNULL
adAndorre-la-Vieille07NULLNULLNULL
adAndorre-Vieille07NULLNULLNULL
adAnsalonga04NULLNULLNULL
adAny=s05NULLNULLNULL
TRUNCATE TABLE CITIES
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 4, 2011 at 8:07 pm
Hell I better add more.
I just used part of the create statement to show the table structure - it was not the actual create statement.
The bulk insert adds in the quotes as well as the data to the fields.
I changed the char fields to a longer varchar field to see what was happening.
I needed to use quotes as some of the city values included quotes.
The actual row terminator got eaten when I posted.
The bulk insert does not honor sdf conventions.
As I was inserting 2.5 million records about the only method was a bulk insert.
I just added the quotes and all and modified the field values using sql then modified the fields after.
I managed to get all the records loaded
thanks
rykk
July 4, 2011 at 8:26 pm
Good deal.
Try using a delimiter another than ",".
You can use "|" but I had a situation where someone placed a Pipe in the text which caused the job to fail.
So I used a non displayable character sequence. It was so long ago that I can't recall what I used. 🙂
Good luck.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 4, 2011 at 8:58 pm
I actually used a pipe on your suggestion even thought I knew there were no commas in the city names
July 5, 2011 at 7:13 am
Are you going to post your solution?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 5, 2011 at 2:50 pm
sales 77914 (7/4/2011)
I actually used a pipe on your suggestion even thought I knew there were no commas in the city names
Yes but you will often encounter commas in text fields.
Feel free to post you final solution.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply