Bulk Insert and Default Values

I am experiencing an issue with bulk insert and default values.
Say that i have a table with thirteen. My format file species all columns
except 14. Column 14 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?
string|string|s|59|aa|aaaaa|N|N|N|N|N|Y|Y
8.0
13
1 SQLCHAR 0 4 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 25 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 2 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 4 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 6 "|" 6 col6 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 1 "|" 7 col7 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "|" 8 col8 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 1 "|" 9 col9 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "|" 10 col10 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 1 "|" 11 col11 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 1 "|" 12 col12 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 1 "\r\n" 13 col13 SQL_Latin1_General_CP1_CI_AS
BULK INSERT db..table FROM 'file' WITH
(
FORMATFILE='fmt.fmt',
CODEPAGE='RAW',
ROWS_PER_BATCH=141,
MAXERRORS=10,
TABLOCK
)
GO
Thoughts?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
> 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?
I tried your format file, data file and BULK INSERT with the table below and
the default value was assigned properly under both SQL 2000 and SQL 2005. I
find it strange that a value of zero is assigned in your environment...
CREATE TABLE dbo.table1
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
col6 varchar(10),
col7 varchar(10),
col8 varchar(10),
col9 varchar(10),
col10 varchar(10),
col11 varchar(10),
col12 varchar(10),
col13 varchar(10),
col14 varchar(10) NULL CONSTRAINT DF_table_col14 DEFAULT '9'
)
Hope this helps.
Dan Guzman
SQL Server MVP
"lotek via droptable.com" <u16539@.uwe> wrote in message
news:7f4a9d35a33d0@.uwe...
>I am experiencing an issue with bulk insert and default values.
> Say that i have a table with thirteen. My format file species all columns
> except 14. Column 14 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?
> string|string|s|59|aa|aaaaa|N|N|N|N|N|Y|Y
> 8.0
> 13
> 1 SQLCHAR 0 4 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 25 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 1 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 0 2 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 0 4 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 6 "|" 6 col6 SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 1 "|" 7 col7 SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 1 "|" 8 col8 SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 1 "|" 9 col9 SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 1 "|" 10 col10 SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 1 "|" 11 col11 SQL_Latin1_General_CP1_CI_AS
> 12 SQLCHAR 0 1 "|" 12 col12 SQL_Latin1_General_CP1_CI_AS
> 13 SQLCHAR 0 1 "\r\n" 13 col13 SQL_Latin1_General_CP1_CI_AS
> BULK INSERT db..table FROM 'file' WITH
> (
> FORMATFILE='fmt.fmt',
> CODEPAGE='RAW',
> ROWS_PER_BATCH=141,
> MAXERRORS=10,
> TABLOCK
> )
> GO
> Thoughts?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
>
|||Col14 is an int where you have a varchar. That might make the difference...
Thanks for your time.
-Matt
Dan Guzman wrote:[vbcol=seagreen]
>I tried your format file, data file and BULK INSERT with the table below and
>the default value was assigned properly under both SQL 2000 and SQL 2005. I
>find it strange that a value of zero is assigned in your environment...
>CREATE TABLE dbo.table1
>(
>col1 varchar(10),
>col2 varchar(10),
>col3 varchar(10),
>col4 varchar(10),
>col5 varchar(10),
>col6 varchar(10),
>col7 varchar(10),
>col8 varchar(10),
>col9 varchar(10),
>col10 varchar(10),
>col11 varchar(10),
>col12 varchar(10),
>col13 varchar(10),
>col14 varchar(10) NULL CONSTRAINT DF_table_col14 DEFAULT '9'
>)
>[quoted text clipped - 34 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
|||> Col14 is an int where you have a varchar. That might make the
> difference...
I ran another test with the int and it works in my environment. Below is
the full repro. The only change I made from the info your original post
(other than file paths) was to shorten the test data to avoid truncation.
You might try the CHECK_CONSTRAINTS option of BULK INSERT but it wasn't
necessary in my environment.
CREATE TABLE dbo.table1
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
col6 varchar(10),
col7 varchar(10),
col8 varchar(10),
col9 varchar(10),
col10 varchar(10),
col11 varchar(10),
col12 varchar(10),
col13 varchar(10),
col14 int NULL CONSTRAINT DF_table_col14 DEFAULT 9
)
GO
--c:\temp\fmt.fmt
8.0
13
1 SQLCHAR 0 4 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 25 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 2 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 4 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 6 "|" 6 col6 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 1 "|" 7 col7 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "|" 8 col8 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 1 "|" 9 col9 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "|" 10 col10 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 1 "|" 11 col11 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 1 "|" 12 col12 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 1 "\r\n" 13 col13 SQL_Latin1_General_CP1_CI_AS
--c:\temp\file.txt
a|b|s|59|aa|aaaaa|N|N|N|N|N|Y|Y
BULK INSERT dbo.table1 FROM 'c:\temp\file.txt' WITH
(
FORMATFILE='c:\temp\fmt.fmt',
CODEPAGE='RAW',
ROWS_PER_BATCH=141,
MAXERRORS=10,
TABLOCK
)
GO
SELECT * FROM dbo.table1
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"lotek via droptable.com" <u16539@.uwe> wrote in message
news:7f4ee474dd997@.uwe...
> Col14 is an int where you have a varchar. That might make the
> difference...
> Thanks for your time.
> -Matt
> Dan Guzman wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
>