bulk insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

bulk insert

Author

Topic

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-13 : 03:45:18

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 this

056191*QLOBAL NETVORKS MMC 2 000 0 00400609440911876601 -200 000 000 000 000 -200 0
092010 AZTELEKOM ISTEHSALAT BIRLIYI 2 000 1233060000 49500 000 000 000 000 49500 1
0

http://sql-az.tr.gg/

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-01-13 : 04:44:57

hi, can you post what inside myTable.fmt?

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-13 : 04:50:33

yes

8.0
13
1 SQLCHAR 0 7 "" 1 Test Cyrillic_General_CI_AS
2 SQLCHAR 0 60 "" 2 Test1 Cyrillic_General_CI_AS
3 SQLCHAR 0 2 "" 3 Test2 Cyrillic_General_CI_AS
4 SQLCHAR 0 4 "" 4 Test3 Cyrillic_General_CI_AS
5 SQLCHAR 0 4 "" 5 Test4 Cyrillic_General_CI_AS
6 SQLCHAR 0 25 "" 6 Test5 Cyrillic_General_CI_AS
7 SQLCHAR 0 11 "" 7 Test6 Cyrillic_General_CI_AS
8 SQLCHAR 0 7 "" 8 Test7 Cyrillic_General_CI_AS
9 SQLCHAR 0 10 "" 9 Test8 Cyrillic_General_CI_AS
10 SQLCHAR 0 8 "" 10 Test9 Cyrillic_General_CI_AS
11 SQLCHAR 0 10 "" 11 Test10 Cyrillic_General_CI_AS
12 SQLCHAR 0 10 "" 12 Test11 Cyrillic_General_CI_AS
13 SQLCHAR 0 7 "\r\n" 13 Test12 Cyrillic_General_CI_AS

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-13 : 04:51:27

i changed
'\ t' to ''

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-13 : 04:56:18

how can I attach a file here

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-13 : 04:56:45

??????

http://sql-az.tr.gg/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-13 : 14:16:07

upload it in skydrive/google drive and post link here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-14 : 01:46:21

https://drive.google.com/?urp=https://www.google.az/&authuser=0#my-drive

login ---- pjimi91@gmail.com

password --- jimi1984

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-14 : 02:28:21

my friends are the same PROFESSIONALS

nobody can import this file?

http://sql-az.tr.gg/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-14 : 02:30:05

we can't view the file

KH
[spoiler]Time is always against us[/spoiler]

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-14 : 02:35:57

in

https://drive.google.com/?

my account login ---- pjimi91@gmail.com
my account password ----jimi1984

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-14 : 02:37:54

there

OBOROTKA-092.list.txt

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-14 : 03:13:15

khtan

watched you file?

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-14 : 03:28:16

can I send your emaill this file?
please?

http://sql-az.tr.gg/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-14 : 03:56:48

Sorry, i don't feel comfortable login in using other's ID and password and you should not publish your ID & password here also.

You can shared the file by right click on it and choose share. It will create a shared link. Just paste the link here

KH
[spoiler]Time is always against us[/spoiler]

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-14 : 05:32:03

https://docs.google.com/file/d/0B3LMe3WzKs3TZ21sRGhSTGFZeDQ/edit

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-14 : 05:38:49

i have sql2000

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-14 : 06:05:25

khtan

watched you file?

at all possible to import

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-01-14 : 07:42:47

my friends are the same PROFESSIONALS

nobody can import this file?

http://sql-az.tr.gg/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-14 : 08:00:05

The text file is 182 chars per line. Looks like your myTable99 column size and fmt file does not match the actual text file.

Double check the length and make sure it matches

also you will need the "FIRSTROW" option in the BULK INSERT to skip the first few rows

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources