bulk insert problem

Question

bulk insert problem

gurbanov.1984

Mr or Mrs. 500

Points: 508
More actions
January 14, 2014 at 3:55 am

#284446

hi friends want to do an bulk insert but get an error
Message 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
here's the sql code
CREATE TABLE myTable99
(Test char(7), Test1 char(60), Test2 char(2), Test3 char(4),
Test4 char(4), Test5 char(25), Test6 char(11),
Test7 char(7), Test8 char(10), Test9 char(8),
Test10 char(10), Test11 char(10), Test12 char(7)
)
exec xp_cmdshell
'bcp DB_BOOKS.dbo.myTable99 format nul -S XIRDALAN-SERVER -f "c:\myTable.fmt" -c -T'
select @@servername
BULK INSERT myTable99
FROM 'C:\abs040123.txt'
WITH
(
FORMATFILE = 'c:\temp\myTable.fmt'
)
text file in this adress
https://docs.google.com/file/d/0B3LMe3WzKs3TZ21sRGhSTGFZeDQ/edit

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQL_Buddy99 Right there with Babe Points: 776 More actions · Answer 1

The error message is saying that string or Binary value would have been Truncate means you are trying to insert a value which has more length you defined so you need to change the length of the columns.

Try using bigger column lengths while creating the table. It will work

gurbanov.1984 Mr or Mrs. 500 Points: 508 More actions · Answer 2

can you change the length of the columns?

please

I changed a few times but have not received

David Burrows SSC Guru Points: 65142 More actions · Answer 3

please post the format file as your columns do not match the data

Far away is close at hand in the images of elsewhere.
Anon.

SQL_Buddy99 Right there with Babe Points: 776 More actions · Answer 4

Have you tried with changing the datatype try using nchar instead of char. This may work use nchar(255) a bigger value.

gurbanov.1984 Mr or Mrs. 500 Points: 508 More actions · Answer 5

gurbanov.1984

Mr or Mrs. 500

Points: 508

January 15, 2014 at 10:23 pm

#1680506

made but received nothing

gurbanov.1984 Mr or Mrs. 500 Points: 508 More actions · Answer 6

my friends are the same PROFESSIONALS

nobody can import this file?

David Burrows SSC Guru Points: 65142 More actions · Answer 7

Using the following table definition

CREATE TABLE myTable99 (

F1 char(7),

F2 char(60),

F3 char(2),

F4 char(4),

F5 char(2),

F6 char(24),

F7 char(1),

F8 char(12),

F9 char(12),

F10 char(12),

F11 char(12),

F12 char(12),

F13 char(14),

F14 char(7)

)

using this format file

10.0

14

1 SQLCHAR 0 7 "" 1 F1 ""

2 SQLCHAR 0 60 "" 2 F2 ""

3 SQLCHAR 0 2 "" 3 F3 ""

4 SQLCHAR 0 4 "" 4 F4 ""

5 SQLCHAR 0 2 "" 5 F5 ""

6 SQLCHAR 0 24 "" 6 F6 ""

7 SQLCHAR 0 1 "" 7 F7 ""

8 SQLCHAR 0 12 "" 8 F8 ""

9 SQLCHAR 0 12 "" 9 F9 ""

10 SQLCHAR 0 12 "" 10 F10 ""

11 SQLCHAR 0 12 "" 11 F11 ""

12 SQLCHAR 0 12 "" 12 F12 ""

13 SQLCHAR 0 14 "" 13 F13 ""

14 SQLCHAR 0 7 "\r" 14 F14 ""

with this sql

BULK INSERT myTable99

FROM 'C:\TEMP\abs040123.txt'

WITH

(

FORMATFILE = 'C:\TEMP\myTable.fmt',

FIRSTROW = 6

)

I was able to load your file.

Note you will need to remove the blank line at the end of the file to avoid eof error

Far away is close at hand in the images of elsewhere.
Anon.