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.
| Author |
Topic |
|
lotek
Starting Member
42 Posts |
 Posted - 2008-02-04 : 13:46:36
|
| I am experiencing an issue with bulk insert and default values.Say that i have a table with three columns: col1, col2, col3. My format file species col1 and col2 but not col3. Col3 is an int and has a default value of '9'. When i insert a record manually it picks up the default value fine, but when i bulk insert all of the records have a default value of 0. Any thoughts?BULK INSERT Database..table FROM 'file' WITH(FORMATFILE='c:\format.fmt',CODEPAGE='RAW',ROWS_PER_BATCH=19065,MAXERRORS=10,TABLOCK)Thanks,Matt |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-04 : 23:00:24
|
| How's your data file look like? |
 |
|
|
lotek
Starting Member
42 Posts |
Posted - 2008-02-05 : 10:47:35
|
quote:
Originally posted by rmiao
How's your data file look like?
string|string|s|59|aa|aaaaa|N|N|N|N|N|Y|Y8.0131 SQLCHAR 0 4 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 25 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 1 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 0 2 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS5 SQLCHAR 0 4 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS6 SQLCHAR 0 6 "|" 6 col6 SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 0 1 "|" 7 col7 SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 0 1 "|" 8 col8 SQL_Latin1_General_CP1_CI_AS9 SQLCHAR 0 1 "|" 9 col9 SQL_Latin1_General_CP1_CI_AS10 SQLCHAR 0 1 "|" 10 col10 SQL_Latin1_General_CP1_CI_AS11 SQLCHAR 0 1 "|" 11 col11 SQL_Latin1_General_CP1_CI_AS12 SQLCHAR 0 1 "|" 12 col12 SQL_Latin1_General_CP1_CI_AS13 SQLCHAR 0 1 "\r\n" 13 col13 SQL_Latin1_General_CP1_CI_ASBULK INSERT db..table FROM 'file' WITH(FORMATFILE='fmt.fmt',CODEPAGE='RAW',ROWS_PER_BATCH=141,MAXERRORS=10,TABLOCK)GOCol14 is an int with a default value, but it always gets set to zero. |
|
|
|
|
|
|
|
|
Bulk Insert and Default Values