BULK INSERT Question

Hi, i have table with 15 columns

CREATE TABLE [dbo].[myTable] (
[m] [bigint] PRIMARY KEY ,
[c1] [bigint] NULL ,
[c2] [bigint] NULL ,
[c3] [bit] NULL ,
[c4] [tinyint] NULL ,
[c5] [nvarchar] (50) NULL ,
[c6] [bit] NULL
............
...............

now i want to run BULK INSERT from file that consist only values for first
column e.g.

22222222222
33333333333
44444444444
.........
.........

and i need that other columns will sets to it's default values i.e. NULL

Any ideas ???

Thanks

--
Message posted via http://www.sqlmonster.com"akej via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:97f86ad1e7ec4a7497b655baa10a64fb@.SQLMonster.c om...
> Hi, i have table with 15 columns
> CREATE TABLE [dbo].[myTable] (
> [m] [bigint] PRIMARY KEY ,
> [c1] [bigint] NULL ,
> [c2] [bigint] NULL ,
> [c3] [bit] NULL ,
> [c4] [tinyint] NULL ,
> [c5] [nvarchar] (50) NULL ,
> [c6] [bit] NULL
> ............
> ...............
> now i want to run BULK INSERT from file that consist only values for first
> column e.g.
> 22222222222
> 33333333333
> 44444444444
> .........
> .........
> and i need that other columns will sets to it's default values i.e. NULL
>
> Any ideas ???
> Thanks
> --
> Message posted via http://www.sqlmonster.com

You need to use a format file - see "Using Format Files" and "Using a Data
File with Fewer Fields" in Books Online. DTS is another option, and probably
quicker if this is a one-off task.

Simon|||akej via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> Hi, i have table with 15 columns
> CREATE TABLE [dbo].[myTable] (
> [m] [bigint] PRIMARY KEY ,
> [c1] [bigint] NULL ,
> [c2] [bigint] NULL ,
> [c3] [bit] NULL ,
> [c4] [tinyint] NULL ,
> [c5] [nvarchar] (50) NULL ,
> [c6] [bit] NULL
> ............
> ...............
> now i want to run BULK INSERT from file that consist only values for first
> column e.g.
> 22222222222
> 33333333333
> 44444444444
> .........
> .........
> and i need that other columns will sets to it's default values i.e. NULL

This is the format file (save without identation):

8.0
1
1 SQLCHAR 0 0 "\r\n" 1 X ""

And this is the SQL command:

BULK INSERT myTable FROM 'E:\temp\slask.bcp'
WITH (FORMATFILE = 'E:\temp\slask.fmt')
go

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ok thanks , very helpful

--
Message posted via http://www.sqlmonster.com|||i use this format file:

8.0
1
1 SQLCHAR 0 8 "\n" 1 mob Hungarian_CS_AI

however i got an error message:

Server: Msg 4822, Level 16, State 1, Line 1
Could not bulk insert. Invalid number of columns in format file 'c:\1.fmt'.

--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> i use this format file:
>
> 8.0
> 1
> 1 SQLCHAR 0 8 "\n" 1 mob Hungarian_CS_AI
>
> however i got an error message:
> Server: Msg 4822, Level 16, State 1, Line 1
> Could not bulk insert. Invalid number of columns in format file
>'c:\1.fmt'.

As I said, leave out the indentation. They were only in my post to
separate the data from my text. You cannot have leading spaces on
the lines in your format file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||sorry, but i don't really understand what do u mean.
Please explain, Thanks

--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> sorry, but i don't really understand what do u mean.
> Please explain, Thanks

In the sample file you posted, you appear to have leading spaces on line
two and three. You need to remove these.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ok, i understand. there are no spaces at all, each number ended with
<ENTER>.

However how can solve this problem, i got an error

--
Message posted via http://www.sqlmonster.com|||Thanks i remove them and it works. Thanks KING

--
Message posted via http://www.sqlmonster.com|||i have another question for u.

Why when i disable replication from Enterprise Manager, tables and jobs not
removed (or not disabled).

What i need do to perform remove replication (remove publishing and
distributor) from some DB, THANKS.

sql server 2000

--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> i have another question for u.
> Why when i disable replication from Enterprise Manager, tables and jobs
> not removed (or not disabled).
> What i need do to perform remove replication (remove publishing and
> distributor) from some DB, THANKS.

Since this a completely different question not related to BULK INSERT,
it would be a good idea to start a new thread. That makes it more likely
that people with experience of replication (I am not one of them) might see
the thread and can help you.

You could also try the newsgroup microsoft.public.sqlserver.replication.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>BULK INSERT myTable FROM 'E:\temp\slask.bcp'

Is it possible to do mapping
e.g

in my slask.bcp file 20 columns, i need to insert to myTable that have
suppose 15 columns also the names of colums in the file and table are
DIFFERENT, so how to acomplish mapping ?

Thanks

--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
>>BULK INSERT myTable FROM 'E:\temp\slask.bcp'
> Is it possible to do mapping
> e.g
> in my slask.bcp file 20 columns, i need to insert to myTable that have
> suppose 15 columns also the names of colums in the file and table are
> DIFFERENT, so how to acomplish mapping ?

Yes, this is possible. The format file describes in input file, and
is indeed a mapping to the table. Each line describes a field in the
file, and possibly also maps it to a table column. Here is a format file
with for a file with three fields:

8.0
3
1 SQLCHAR 1 0 "" 7 col1 Hungarian_CS_AI
2 SQLCHAR 0 10 "" 2 col2 Hungarian CS_AI
3 SQLCHAR 0 0 "\r\n" 5 col5 ""

The first number is the field number, and they should come in consecutive
order. Note that the 3 on the second line, describes the number of fields
in the field.

The next is the data type *in the file*. As long as you work with text
files this is always SQLCHAR (or SQLNCHAR for Unicode files), no matter
the data type of the target column in the database. But it is possible
to bulk load binary files, for which you would use SQLINT and such.

The next three fields describes the field is in the file. And while
they can be combined, you normally use them one by one. (Honestly, I
don't exactly what happens if you combine them.)

The first of these columns is a "prefix length" and is always 1, 2 or 4.
This prefix gives the actual length the of the field, and the prefix
is itself a binary value of 1, 2 or 4 bytes. From this follows that
prefix lengths are rarely used with data files in text format.

The sceond of these columns is a fixed length in bytes.

The last column is a character sequence that terminates the field. Note
that in this example the delimiter is newline, \r\n, and many files do
indeed have one data record per line in the file. However, BCP makes
no such assumptions, and will just iterate over the field definitions,
and if a newline appears in what BCP thinks is in the middle of a text
field, then that newline is handled as data. This permits you to import
data with newlines, but it also means that when BCP goes out of sync,
it gets out of sync badly.

Next number is the mapping you are looking for. This is the column
number in the database. In the example the file loads columns 2, 5
and 7 in the database. You can also specify 0 to indicate that that
field in the table should not be loaded at all. This can be used to
handle formats like:

"quoted data","more quoted data",989

Next column in the format file is the column name, but the value of
this field is ignored, so you can put "" if you like.

The last column is the collation of the field in the text file, in
case you would need some conversion. You can set "" for non-character
columns, or set "" all the way to get some default.

All of what I have said here is Books Online, under
Administering SQL Server
Importing and Exporting Data
Using bcp and BULK INSERT
Using Format files

But my presentation above maybe somewhat clearer than the sections in
Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank u very much. I can't understand why some documentation are written in
language that only the author of it can understand.

Last question on the subject however i think it's impossible, in case the
datatype are different in some columns (in the table and in the file)
is it possible to convert in some way? (using the BULK INSERT)

THANKS.

P.S. Do u have some new tutorials or books?? Please let me know in case
the books i want buy in case tutorials (like ERROR HAndling) please give me
the links, thanks.

--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> Last question on the subject however i think it's impossible, in case the
> datatype are different in some columns (in the table and in the file)
> is it possible to convert in some way? (using the BULK INSERT)

Not really sure what you mean here. Assuming that the file you import is
a text file, the data type will be different as soon as the target column
is not a character data type.

I have not dug deeply into this, but I would assume that the same
conversions to be available, that are available in SQL statements.

Maybe if you have a specific example, I can give some suggestions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Suppose if i acomplish UPDATING to some table and i need to convert data
(in my case data that i got from parameters) i can use the CASE statement
e.g.

UPDATE myTable
SETcol1 = CASE WHEN @.param = 'TRUE' THEN 1
ELSE 0 END,
...........................
...........................
...........................

the col1 of myTable has datatype bit (because the datatype of the column
different form this that i got in param i need to convert)

Also while i perfom UPDATING with using the CASE statement i can convert
from any datatype that i want.

Ny question about above but in BULK INSERT ??

--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> Suppose if i acomplish UPDATING to some table and i need to convert data
> (in my case data that i got from parameters) i can use the CASE statement
> e.g.
> UPDATE myTable
> SET col1 = CASE WHEN @.param = 'TRUE' THEN 1
> ELSE 0 END,
> ...........................
> ...........................
> ...........................
> the col1 of myTable has datatype bit (because the datatype of the column
> different form this that i got in param i need to convert)
> Also while i perfom UPDATING with using the CASE statement i can convert
> from any datatype that i want.
> Ny question about above but in BULK INSERT ??

I'm sorry, but you have me lost completely. First you talk about
UPDATE, and then you go on with BULK INSERT. You cannot update tables
with BULK INSERT, so I fail to see the connection.

If you have problem with BULK INSERT, please post:

o CREATE TABLE statement for your table.
o Sample data file.
o Any format file you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In my aoverhead post i wanted to show u as an example that i can covert
data e.g. from char to int, in my case when i accomplish BULK INSERT i want
to convert from suppese char to int. If in my data file one column is char
i need in some way to cenvert it to int like i did in my overhead post with
CASE statement.

Thanks, and sorry that confused u

--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> In my aoverhead post i wanted to show u as an example that i can covert
> data e.g. from char to int, in my case when i accomplish BULK INSERT i
> want to convert from suppese char to int. If in my data file one column
> is char i need in some way to cenvert it to int like i did in my
> overhead post with CASE statement.

Normally, when I hear a conversion I think in terms of implicit conversion
as when the string literal '20000520 12:21:31' can be interpreted as a
datetime value, or when you use explicit conversion with cast() or
convert().

The only form of conversion you can do with bulk load is implicit
conversion, since you cannot apply functions to the data you load. From
this follows that you neither can do user-implemented "conversion"
as in your example with bulk-load directly.

If you need to do transformation like storing TRUE/FALSE in an input
file as bit values, there are two choices: 1) use an intermediate
table into which you load the data, and the use INSERT-SELECT to move
the data to the target table. 2) Use DTS to write a transformation task.

As for 1), this is often needed anyway, because the file data may be
imperfect and need scrubbing of duplicates etc. As for 2) I assume that
this is what you can use DTS for, but never having used DTS myself, I
can give any details. It's possible that running the Import/Export
wizard can give you headstart in this area.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks the 1) is perfect.

Thanks.

--
Message posted via http://www.sqlmonster.comsql

BULK INSERT HELP!

Hello,
This is my table
CREATE TABLE [dbo].[TDE018](
[no_empl] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
[no_dexp] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
[no_doss_ir] [int] NOT NULL,
[no_even_ir] [smallint] NOT NULL,
[dat_even_orig_ir] [datetime] NULL,
[dat_even_ir] [datetime] NULL,
[cod_loi_repa] [varchar](2) COLLATE SQL_Latin1_General_CP850_CI_AI NULL,
[dat_inscr_decis_r] [datetime] NULL,
[cod_decis_admis_r] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[cod_motif_deci_adm] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[dat_presc_drt] [datetime] NULL,
[dat_cess_1er_emplo] [datetime] NULL,
[dat_consol] [datetime] NULL,
[dat_rtr_1er_emploi] [datetime] NULL,
[text_desc_diagn] [varchar](80) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[taux_pe_enga_ap] [float] NULL,
[dat_enrg_oper] [datetime] NULL
) ON [PRIMARY]
This is the command that I use
BULK INSERT TDE018 FROM 'C:\TDE018.csv' WITH (FORMATFILE = 'C:\TDE018.fmt',
KEEPNULLS, CODEPAGE ='1252')
This is my format file
8.0
17
1 SQLCHAR 2 12 ";" 1
no_empl SQL_Latin1_General_CP850_CI_AI
2 SQLCHAR 2 12 ";" 2
no_dexp SQL_Latin1_General_CP850_CI_AI
3 SQLINT 0 9 ";"
3 no_doss_ir ""
4 SQLSMALLINT 0 4 ";" 4
no_even_ir ""
5 SQLDATETIME 1 10 ";" 5
dat_even_orig_ir ""
6 SQLDATETIME 1 10 ";" 6
dat_even_ir ""
7 SQLCHAR 2 2 ";" 7
cod_loi_repa SQL_Latin1_General_CP850_CI_AI
8 SQLDATETIME 1 10 ";" 8
dat_inscr_decis_r ""
9 SQLCHAR 2 3 ";" 9
cod_decis_admis_r SQL_Latin1_General_CP850_CI_AI
10 SQLCHAR 2 3 ";" 10
cod_motif_deci_adm SQL_Latin1_General_CP850_CI_AI
11 SQLDATETIME 1 10 ";" 11
dat_presc_drt ""
12 SQLDATETIME 1 10 ";" 12
dat_cess_1er_emplo ""
13 SQLDATETIME 1 10 ";" 13
dat_consol ""
14 SQLDATETIME 1 10 ";\"" 14
dat_rtr_1er_emploi ""
15 SQLCHAR 2 70 "\";" 15
text_desc_diagn SQL_Latin1_General_CP850_CI_AI
16 SQLFLT8 1 6 ";" 16
taux_pe_enga_ap ""
17 SQLDATETIME 1 10 "\n" 17
dat_enrg_oper ""
This is an exemple of row in my data file
ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
laceration to the left hand";1.2;2001-01-01
I have this error and I don't know where to look because it is the first
time I use BULK INSERT.
This is the error
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
The statement has been terminated.
Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
I need help please
Thank you
Marc R.
> This is an exemple of row in my data file
> ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
> laceration to the left hand";1.2;2001-01-01
This record is in character format and has no length prefixes. The format
file below describes the source file according this sample data. I also
increased the maximum field lengths to accommodate the target SQL data types
in case other records have longer values.
8.0
17
1 SQLCHAR 0 12 ";" 1 no_empl SQL_Latin1_General_CP850_CI_AI
2 SQLCHAR 0 12 ";" 2 no_dexp SQL_Latin1_General_CP850_CI_AI
3 SQLCHAR 0 9 ";" 3 no_doss_ir ""
4 SQLCHAR 0 4 ";" 4 no_even_ir ""
5 SQLCHAR 0 10 ";" 5 dat_even_orig_ir ""
6 SQLCHAR 0 10 ";" 6 dat_even_ir ""
7 SQLCHAR 0 2 ";" 7 cod_loi_repa SQL_Latin1_General_CP850_CI_AI
8 SQLCHAR 0 10 ";" 8 dat_inscr_decis_r ""
9 SQLCHAR 0 3 ";" 9 cod_decis_admis_r
SQL_Latin1_General_CP850_CI_AI
10 SQLCHAR 0 3 ";" 10 cod_motif_deci_adm
SQL_Latin1_General_CP850_CI_AI
11 SQLCHAR 0 10 ";" 11 dat_presc_drt ""
12 SQLCHAR 0 10 ";" 12 dat_cess_1er_emplo ""
13 SQLCHAR 0 10 ";" 13 dat_consol ""
14 SQLCHAR 0 10 ";\"" 14 dat_rtr_1er_emploi ""
15 SQLCHAR 0 70 "\";" 15 text_desc_diagn
SQL_Latin1_General_CP850_CI_AI
16 SQLCHAR 0 38 ";" 16 taux_pe_enga_ap ""
17 SQLCHAR 0 10 "\n" 17 dat_enrg_oper ""
Hope this helps.
Dan Guzman
SQL Server MVP
"Marc Robitaille" <marc.marie AT globetrotter.net.del> wrote in message
news:e18Mq8lUHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Hello,
> This is my table
> CREATE TABLE [dbo].[TDE018](
> [no_empl] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
> [no_dexp] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
> [no_doss_ir] [int] NOT NULL,
> [no_even_ir] [smallint] NOT NULL,
> [dat_even_orig_ir] [datetime] NULL,
> [dat_even_ir] [datetime] NULL,
> [cod_loi_repa] [varchar](2) COLLATE SQL_Latin1_General_CP850_CI_AI NULL,
> [dat_inscr_decis_r] [datetime] NULL,
> [cod_decis_admis_r] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [cod_motif_deci_adm] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [dat_presc_drt] [datetime] NULL,
> [dat_cess_1er_emplo] [datetime] NULL,
> [dat_consol] [datetime] NULL,
> [dat_rtr_1er_emploi] [datetime] NULL,
> [text_desc_diagn] [varchar](80) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [taux_pe_enga_ap] [float] NULL,
> [dat_enrg_oper] [datetime] NULL
> ) ON [PRIMARY]
> This is the command that I use
> BULK INSERT TDE018 FROM 'C:\TDE018.csv' WITH (FORMATFILE =
> 'C:\TDE018.fmt', KEEPNULLS, CODEPAGE ='1252')
> This is my format file
> 8.0
> 17
> 1 SQLCHAR 2 12 ";" 1
> no_empl SQL_Latin1_General_CP850_CI_AI
> 2 SQLCHAR 2 12 ";"
> 2 no_dexp SQL_Latin1_General_CP850_CI_AI
> 3 SQLINT 0 9 ";" 3 no_doss_ir
> ""
> 4 SQLSMALLINT 0 4 ";" 4
> no_even_ir ""
> 5 SQLDATETIME 1 10 ";" 5
> dat_even_orig_ir ""
> 6 SQLDATETIME 1 10 ";" 6
> dat_even_ir ""
> 7 SQLCHAR 2 2 ";" 7
> cod_loi_repa SQL_Latin1_General_CP850_CI_AI
> 8 SQLDATETIME 1 10 ";" 8
> dat_inscr_decis_r ""
> 9 SQLCHAR 2 3 ";" 9
> cod_decis_admis_r SQL_Latin1_General_CP850_CI_AI
> 10 SQLCHAR 2 3 ";" 10
> cod_motif_deci_adm SQL_Latin1_General_CP850_CI_AI
> 11 SQLDATETIME 1 10 ";" 11
> dat_presc_drt ""
> 12 SQLDATETIME 1 10 ";" 12
> dat_cess_1er_emplo ""
> 13 SQLDATETIME 1 10 ";" 13
> dat_consol ""
> 14 SQLDATETIME 1 10 ";\"" 14
> dat_rtr_1er_emploi ""
> 15 SQLCHAR 2 70 "\";" 15
> text_desc_diagn SQL_Latin1_General_CP850_CI_AI
> 16 SQLFLT8 1 6 ";" 16
> taux_pe_enga_ap ""
> 17 SQLDATETIME 1 10 "\n" 17
> dat_enrg_oper ""
> This is an exemple of row in my data file
> ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
> laceration to the left hand";1.2;2001-01-01
> I have this error and I don't know where to look because it is the first
> time I use BULK INSERT.
> This is the error
> Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> The statement has been terminated.
> Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, column
> 1. Make sure the field terminator and row terminator are specified
> correctly.
> I need help please
> Thank you
> Marc R.
>

BULK INSERT HELP!

Hello,
This is my table
CREATE TABLE [dbo].[TDE018](
[no_empl] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
[no_dexp] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
[no_doss_ir] [int] NOT NULL,
[no_even_ir] [smallint] NOT NULL,
[dat_even_orig_ir] [datetime] NULL,
[dat_even_ir] [datetime] NULL,
[cod_loi_repa] [varchar](2) COLLATE SQL_Latin1_General_CP850_CI_AI NULL,
[dat_inscr_decis_r] [datetime] NULL,
[cod_decis_admis_r] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[cod_motif_deci_adm] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[dat_presc_drt] [datetime] NULL,
[dat_cess_1er_emplo] [datetime] NULL,
[dat_consol] [datetime] NULL,
[dat_rtr_1er_emploi] [datetime] NULL,
[text_desc_diagn] [varchar](80) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[taux_pe_enga_ap] [float] NULL,
[dat_enrg_oper] [datetime] NULL
) ON [PRIMARY]
This is the command that I use
BULK INSERT TDE018 FROM 'C:\TDE018.csv' WITH (FORMATFILE = 'C:\TDE018.fmt',
KEEPNULLS, CODEPAGE ='1252')
This is my format file
8.0
17
1 SQLCHAR 2 12 ";" 1
no_empl SQL_Latin1_General_CP850_CI_AI
2 SQLCHAR 2 12 ";" 2
no_dexp SQL_Latin1_General_CP850_CI_AI
3 SQLINT 0 9 ";"
3 no_doss_ir ""
4 SQLSMALLINT 0 4 ";" 4
no_even_ir ""
5 SQLDATETIME 1 10 ";" 5
dat_even_orig_ir ""
6 SQLDATETIME 1 10 ";" 6
dat_even_ir ""
7 SQLCHAR 2 2 ";" 7
cod_loi_repa SQL_Latin1_General_CP850_CI_AI
8 SQLDATETIME 1 10 ";" 8
dat_inscr_decis_r ""
9 SQLCHAR 2 3 ";" 9
cod_decis_admis_r SQL_Latin1_General_CP850_CI_AI
10 SQLCHAR 2 3 ";" 10
cod_motif_deci_adm SQL_Latin1_General_CP850_CI_AI
11 SQLDATETIME 1 10 ";" 11
dat_presc_drt ""
12 SQLDATETIME 1 10 ";" 12
dat_cess_1er_emplo ""
13 SQLDATETIME 1 10 ";" 13
dat_consol ""
14 SQLDATETIME 1 10 ";\"" 14
dat_rtr_1er_emploi ""
15 SQLCHAR 2 70 "\";" 15
text_desc_diagn SQL_Latin1_General_CP850_CI_AI
16 SQLFLT8 1 6 ";" 16
taux_pe_enga_ap ""
17 SQLDATETIME 1 10 "\n" 17
dat_enrg_oper ""
This is an exemple of row in my data file
ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
laceration to the left hand";1.2;2001-01-01
I have this error and I don't know where to look because it is the first
time I use BULK INSERT.
This is the error
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
The statement has been terminated.
Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
I need help please
Thank you
Marc R.> This is an exemple of row in my data file
> ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
> laceration to the left hand";1.2;2001-01-01
This record is in character format and has no length prefixes. The format
file below describes the source file according this sample data. I also
increased the maximum field lengths to accommodate the target SQL data types
in case other records have longer values.
8.0
17
1 SQLCHAR 0 12 ";" 1 no_empl SQL_Latin1_General_CP850_CI_AI
2 SQLCHAR 0 12 ";" 2 no_dexp SQL_Latin1_General_CP850_CI_AI
3 SQLCHAR 0 9 ";" 3 no_doss_ir ""
4 SQLCHAR 0 4 ";" 4 no_even_ir ""
5 SQLCHAR 0 10 ";" 5 dat_even_orig_ir ""
6 SQLCHAR 0 10 ";" 6 dat_even_ir ""
7 SQLCHAR 0 2 ";" 7 cod_loi_repa SQL_Latin1_General_CP850_CI_AI
8 SQLCHAR 0 10 ";" 8 dat_inscr_decis_r ""
9 SQLCHAR 0 3 ";" 9 cod_decis_admis_r
SQL_Latin1_General_CP850_CI_AI
10 SQLCHAR 0 3 ";" 10 cod_motif_deci_adm
SQL_Latin1_General_CP850_CI_AI
11 SQLCHAR 0 10 ";" 11 dat_presc_drt ""
12 SQLCHAR 0 10 ";" 12 dat_cess_1er_emplo ""
13 SQLCHAR 0 10 ";" 13 dat_consol ""
14 SQLCHAR 0 10 ";\"" 14 dat_rtr_1er_emploi ""
15 SQLCHAR 0 70 "\";" 15 text_desc_diagn
SQL_Latin1_General_CP850_CI_AI
16 SQLCHAR 0 38 ";" 16 taux_pe_enga_ap ""
17 SQLCHAR 0 10 "\n" 17 dat_enrg_oper ""
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Marc Robitaille" <marc.marie AT globetrotter.net.del> wrote in message
news:e18Mq8lUHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Hello,
> This is my table
> CREATE TABLE [dbo].[TDE018](
> [no_empl] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
> [no_dexp] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
> [no_doss_ir] [int] NOT NULL,
> [no_even_ir] [smallint] NOT NULL,
> [dat_even_orig_ir] [datetime] NULL,
> [dat_even_ir] [datetime] NULL,
> [cod_loi_repa] [varchar](2) COLLATE SQL_Latin1_General_CP850_CI_AI NULL,
> [dat_inscr_decis_r] [datetime] NULL,
> [cod_decis_admis_r] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [cod_motif_deci_adm] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [dat_presc_drt] [datetime] NULL,
> [dat_cess_1er_emplo] [datetime] NULL,
> [dat_consol] [datetime] NULL,
> [dat_rtr_1er_emploi] [datetime] NULL,
> [text_desc_diagn] [varchar](80) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [taux_pe_enga_ap] [float] NULL,
> [dat_enrg_oper] [datetime] NULL
> ) ON [PRIMARY]
> This is the command that I use
> BULK INSERT TDE018 FROM 'C:\TDE018.csv' WITH (FORMATFILE => 'C:\TDE018.fmt', KEEPNULLS, CODEPAGE ='1252')
> This is my format file
> 8.0
> 17
> 1 SQLCHAR 2 12 ";" 1
> no_empl SQL_Latin1_General_CP850_CI_AI
> 2 SQLCHAR 2 12 ";"
> 2 no_dexp SQL_Latin1_General_CP850_CI_AI
> 3 SQLINT 0 9 ";" 3 no_doss_ir
> ""
> 4 SQLSMALLINT 0 4 ";" 4
> no_even_ir ""
> 5 SQLDATETIME 1 10 ";" 5
> dat_even_orig_ir ""
> 6 SQLDATETIME 1 10 ";" 6
> dat_even_ir ""
> 7 SQLCHAR 2 2 ";" 7
> cod_loi_repa SQL_Latin1_General_CP850_CI_AI
> 8 SQLDATETIME 1 10 ";" 8
> dat_inscr_decis_r ""
> 9 SQLCHAR 2 3 ";" 9
> cod_decis_admis_r SQL_Latin1_General_CP850_CI_AI
> 10 SQLCHAR 2 3 ";" 10
> cod_motif_deci_adm SQL_Latin1_General_CP850_CI_AI
> 11 SQLDATETIME 1 10 ";" 11
> dat_presc_drt ""
> 12 SQLDATETIME 1 10 ";" 12
> dat_cess_1er_emplo ""
> 13 SQLDATETIME 1 10 ";" 13
> dat_consol ""
> 14 SQLDATETIME 1 10 ";\"" 14
> dat_rtr_1er_emploi ""
> 15 SQLCHAR 2 70 "\";" 15
> text_desc_diagn SQL_Latin1_General_CP850_CI_AI
> 16 SQLFLT8 1 6 ";" 16
> taux_pe_enga_ap ""
> 17 SQLDATETIME 1 10 "\n" 17
> dat_enrg_oper ""
> This is an exemple of row in my data file
> ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
> laceration to the left hand";1.2;2001-01-01
> I have this error and I don't know where to look because it is the first
> time I use BULK INSERT.
> This is the error
> Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> The statement has been terminated.
> Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, column
> 1. Make sure the field terminator and row terminator are specified
> correctly.
> I need help please
> Thank you
> Marc R.
>

BULK INSERT file with quoted strings, but no quotes when NULL

Hello,
I've put together a format file for a text file that I need to import
using ",\"" and "\",\"", etc. as column delimiters. This would normally
cover the whole problem with quoted strings. However, in this
particular file if a string value is NULL then that column doesn't have
the double quotes around it for that record.
Is there any way to handle this using BULK INSERT? I know that I can do
it in DTS, but can it be done with BULK INSERT?
Thanks!
-Tom.I should also point out that some of these files can be a couple
hundred million rows, so I would really prefer not to load them in with
the quotes and then use REPLACE() to remove those quotes.
Thanks,
-Tom.
Aardvark wrote:
> Hello,
> I've put together a format file for a text file that I need to import
> using ",\"" and "\",\"", etc. as column delimiters. This would normally
> cover the whole problem with quoted strings. However, in this
> particular file if a string value is NULL then that column doesn't have
> the double quotes around it for that record.
> Is there any way to handle this using BULK INSERT? I know that I can do
> it in DTS, but can it be done with BULK INSERT?
> Thanks!
> -Tom.|||My suggestion since you do not want to use the REPLACE function is to
strip the " character using the Operating System or a text editor..
Maybe you could use cygwin and SED/AWK.
JD.|||Aardvark (tom_hummel@.hotmail.com) writes:
> I've put together a format file for a text file that I need to import
> using ",\"" and "\",\"", etc. as column delimiters. This would normally
> cover the whole problem with quoted strings. However, in this
> particular file if a string value is NULL then that column doesn't have
> the double quotes around it for that record.
> Is there any way to handle this using BULK INSERT? I know that I can do
> it in DTS, but can it be done with BULK INSERT?
Unless there are some very fortunate circumstances with the file format,
the answer is no. The best suggestion I can give is to write a program
that reads the file, strips the quotes and the uses the bulk-copy API
to insert from variables. But you probably prefer to use DTS instead.
(And what those fortunate cicrumstances may be, I don't really know.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Bulk insert errors

Hi.

I am trying following procedure:

I have table:

CREATE TABLE [dbo].[organiz] (
[cislo_subjektu] [int] NULL ,
[reference_subjektu] [varchar] (30) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_subjektu] [varchar] (100) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_zkraceny] [varchar] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ulice] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[psc] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[misto] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ico] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[dic] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[uverovy_limit] [money] NULL ,
[stav_limitu] [money] NULL
) ON [PRIMARY]
GO

Format File:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="cislo_subjektu" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="reference_subjektu" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="nazev_subjektu" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="nazev_zkraceny" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="ulice" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="6" NAME="psc" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="7" NAME="misto" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="8" NAME="ico" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="9" NAME="dic" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="10" NAME="uverovy_limit" xsi:type="SQLMONEY"/>
<COLUMN SOURCE="11" NAME="stav_limitu" xsi:type="SQLMONEY"/>
</ROW>
</BCPFORMAT>

And XML file located on drive.

When i try bulk insert:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\organizpok.xml' /* my format file */)

I get error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row ....

This error occurs with format file created by bcp. When i try to mess a little with format file, i can get to this error:
Bulk load data conversion error (truncation) for row ...

Anyone has experience with this?
SEe this http://www.thescripts.com/forum/thread520822.html is any help, good explanation by Erland.|||Hm, i did not find solution for my problem, or i am blind.
|||It has to look this way:

DECLARE @.X XML
SELECT @.X = X.C

FROM OPENROWSET(BULK

'D:\organizace.xml',

SINGLE_BLOB) AS X(C)
INSERT INTO pokus.dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('*', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('*', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

Bulk insert errors

Hi.

I am trying following procedure:

I have table:

CREATE TABLE [dbo].[organiz] (
[cislo_subjektu] [int] NULL ,
[reference_subjektu] [varchar] (30) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_subjektu] [varchar] (100) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_zkraceny] [varchar] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ulice] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[psc] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[misto] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ico] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[dic] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[uverovy_limit] [money] NULL ,
[stav_limitu] [money] NULL
) ON [PRIMARY]
GO

Format File:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="cislo_subjektu" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="reference_subjektu" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="nazev_subjektu" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="nazev_zkraceny" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="ulice" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="6" NAME="psc" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="7" NAME="misto" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="8" NAME="ico" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="9" NAME="dic" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="10" NAME="uverovy_limit" xsi:type="SQLMONEY"/>
<COLUMN SOURCE="11" NAME="stav_limitu" xsi:type="SQLMONEY"/>
</ROW>
</BCPFORMAT>

And XML file located on drive.

When i try bulk insert:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\organizpok.xml' /* my format file */)

I get error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row ....

This error occurs with format file created by bcp. When i try to mess a little with format file, i can get to this error:
Bulk load data conversion error (truncation) for row ...

Anyone has experience with this?
SEe this http://www.thescripts.com/forum/thread520822.html is any help, good explanation by Erland.|||Hm, i did not find solution for my problem, or i am blind.
|||It has to look this way:

DECLARE @.X XML
SELECT @.X = X.C

FROM OPENROWSET(BULK

'D:\organizace.xml',

SINGLE_BLOB) AS X(C)
INSERT INTO pokus.dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('*', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('*', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

Bulk insert errors

Hi.

I am trying following procedure:

I have table:

CREATE TABLE [dbo].[organiz] (
[cislo_subjektu] [int] NULL ,
[reference_subjektu] [varchar] (30) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_subjektu] [varchar] (100) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_zkraceny] [varchar] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ulice] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[psc] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[misto] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ico] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[dic] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[uverovy_limit] [money] NULL ,
[stav_limitu] [money] NULL
) ON [PRIMARY]
GO

Format File:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="cislo_subjektu" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="reference_subjektu" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="nazev_subjektu" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="nazev_zkraceny" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="ulice" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="6" NAME="psc" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="7" NAME="misto" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="8" NAME="ico" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="9" NAME="dic" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="10" NAME="uverovy_limit" xsi:type="SQLMONEY"/>
<COLUMN SOURCE="11" NAME="stav_limitu" xsi:type="SQLMONEY"/>
</ROW>
</BCPFORMAT>

And XML file located on drive.

When i try bulk insert:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\organizpok.xml' /* my format file */)

I get error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row ....

This error occurs with format file created by bcp. When i try to mess a little with format file, i can get to this error:
Bulk load data conversion error (truncation) for row ...

Anyone has experience with this?
SEe this http://www.thescripts.com/forum/thread520822.html is any help, good explanation by Erland.|||Hm, i did not find solution for my problem, or i am blind.
|||It has to look this way:

DECLARE @.X XML
SELECT @.X = X.C

FROM OPENROWSET(BULK

'D:\organizace.xml',

SINGLE_BLOB) AS X(C)
INSERT INTO pokus.dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('*', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('*', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

Bulk Insert (type mismatch) on datetime field containing NULL

Can anyone help please, I am using bulk insert for the first time.
The statement I am running is:
BULK INSERT Titles
FROM 'c:\Titles.txt'
WITH (FIRSTROW = 3,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
KEEPNULLS,
FORMATFILE = 'c:\Titles.fmt')
Titles.txt contains tab delimited data like:
ID Description StartDate ExpiryDate ParentItemID
-- --
-- -- --
440 Doctor 1 Jan 1997 0:00 NULL NULL
441 Mr 1 Jan 1990 0:00 NULL 1
If I run the bulk insert statement I get the message:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 4
(ExpiryDate)
In the file Titles.txt, if I find and replace NULL with nothing and
then execute the statement the data inserts into the Titles table.
I need to be able to insert without having to do find and replace as I
have hundreds of files to bulk insert.
The format file Titles.fmt looks like this:
8.0
5
1 SQLCHAR 0 12 "\t" 1
ID ""
2 SQLCHAR 0 100 "\t" 2
Description Latin1_General_CI_AS
3 SQLCHAR 0 24 "\t" 3
StartDate ""
4 SQLCHAR 0 24 "\t" 4
ExpiryDate ""
5 SQLCHAR 0 12 "\t" 5
ParentItemID ""If this is not a one time deal, you'd be better off making sure that when
these files are generated, the value for column ExpireDate that is null does
not contain a string NULL.
With the existing data files, personally, I'd write a little utility to
find/replace all the 'NULL' string in the ExpireDate column with an empty
string. This can be esily done with any tool that supports regular
expressions.
Linchi
"rai_sk@.hotmail.com" wrote:
> Can anyone help please, I am using bulk insert for the first time.
> The statement I am running is:
> BULK INSERT Titles
> FROM 'c:\Titles.txt'
> WITH (FIRSTROW = 3,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> FORMATFILE = 'c:\Titles.fmt')
> Titles.txt contains tab delimited data like:
> ID Description StartDate ExpiryDate ParentItemID
> -- --
> -- -- --
> 440 Doctor 1 Jan 1997 0:00 NULL NULL
> 441 Mr 1 Jan 1990 0:00 NULL 1
> If I run the bulk insert statement I get the message:
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 4
> (ExpiryDate)
> In the file Titles.txt, if I find and replace NULL with nothing and
> then execute the statement the data inserts into the Titles table.
> I need to be able to insert without having to do find and replace as I
> have hundreds of files to bulk insert.
> The format file Titles.fmt looks like this:
> 8.0
> 5
> 1 SQLCHAR 0 12 "\t" 1
> ID ""
> 2 SQLCHAR 0 100 "\t" 2
> Description Latin1_General_CI_AS
> 3 SQLCHAR 0 24 "\t" 3
> StartDate ""
> 4 SQLCHAR 0 24 "\t" 4
> ExpiryDate ""
> 5 SQLCHAR 0 12 "\t" 5
> ParentItemID ""
>

bulk insert (again and again..)

Hi !
sorry to bother you again with that topic..but...
Bulk Insert inserts null value when it finds null string in my source
file to load...
is there any way to prevent this'
I would like to get null fields instead of NULL value in my base..
thanks again :)
++
VinceHi
You can do a post update of
UPDATE TABLE
SET Col1 = NULLIF('NULL')
or you should look at the source try to generate the file differently.
John
"Vince .>" <vincent@.<remove> wrote in message
news:38qk4150iaddn8ha1k7njss01466u2u8kv@.
4ax.com...
> Hi !
> sorry to bother you again with that topic..but...
> Bulk Insert inserts null value when it finds null string in my source
> file to load...
> is there any way to prevent this'
> I would like to get null fields instead of NULL value in my base..
>
> thanks again :)
> ++
> Vince

built in function

How is it possible to show null if the field is empty?

i.e.
select
ID,
name
from
table1

it should show something like:
1, 'jo'
2, NULL
3, NULL
4, 'Jack'
...

Thanks

use nullif(column,'')

Code Snippet

Create Table #data (

[Id] int ,

[Name] Varchar(100)

);

Insert Into #data Values('1','jo');

Insert Into #data Values('2','');

Insert Into #data Values('3',' ');

Insert Into #data Values('3','');

Insert Into #data Values('4','Jack');

Select Id, nullif(name,'') From #data

|||

Try:

Code Snippet

select

ID,

nullif(ltrim(rtrim(name)), '') as [name]

from

table1

-- or

select

ID,

case when ltrim(rtrim(name)) = '' then NULL else [name] end as [name]

from

table1

AMB

|||

Kent,

I am not sure how COALESCE function will fit for this issue.

NULLIF function is the ANSI standard and supported by most of the RDBMS databases.

Building a conditional WHERE clause

Hello experts, I have a sproc that gets several params, default is null. I'
d
like to build a WHERE clause on the fly for just the params with values.
Something like
SELECT * from foo
WHERE if( @.a IS NOT NULL foo.a = @.a) and if( @.b IS NOT NULL foo.b = @.b)IF is a control flow statement... You cannot use it in a query.
How about :
WHERE a = COALESCE(@.a, a)
AND b = COALESCE(@.b, b)
You can also use CASE but it's a bit more drawn out:
WHERE a = CASE WHEN @.a IS NOT NULL THEN @.a ELSE a END
AND b = CASE WHEN @.b IS NOT NULL THEN @.b ELSE b END
(And the latter will almost certainly lead to scans rather than ss, if a
and/or be is indexed.)
A
On 3/5/05 12:45 PM, in article
3AD07FB0-A316-4ED0-A29D-EB3E5F06499A@.microsoft.com, "Coffee guy"
<Coffeeguy@.discussions.microsoft.com> wrote:

> Hello experts, I have a sproc that gets several params, default is null.
I'd
> like to build a WHERE clause on the fly for just the params with values.
> Something like
> SELECT * from foo
> WHERE if( @.a IS NOT NULL foo.a = @.a) and if( @.b IS NOT NULL foo.b = @.b)
>
>|||Aaaah, thanks!
"Aaron [SQL Server MVP]" wrote:

> IF is a control flow statement... You cannot use it in a query.
> How about :
> WHERE a = COALESCE(@.a, a)
> AND b = COALESCE(@.b, b)
> You can also use CASE but it's a bit more drawn out:
> WHERE a = CASE WHEN @.a IS NOT NULL THEN @.a ELSE a END
> AND b = CASE WHEN @.b IS NOT NULL THEN @.b ELSE b END
> (And the latter will almost certainly lead to scans rather than ss, if
a
> and/or be is indexed.)
> A
>
> On 3/5/05 12:45 PM, in article
> 3AD07FB0-A316-4ED0-A29D-EB3E5F06499A@.microsoft.com, "Coffee guy"
> <Coffeeguy@.discussions.microsoft.com> wrote:
>
>

Build conditional WHERE clause

Hi All,

I'm building a simple stored proc to be the basis of an SQL Report.

I've set up my parameters to default to NULL, as the users will have the option to filter the parameters to build the report. I have 2 datetime params which are causing me a problem.

I have a condition which checks if these 2 params are NULL, if they are, it just continues and executes the SQL query, and all is well. However, if they contain dates, I am trying to build a where condition in the variable named @.where_clause, and then I append this to the end of my SQL statement.

- If my @.where_clause is empty, the stored proc returns the desired results
- If the @.where_clause is NOT empty, my stored proc executes but returns NO results
- And last, if I modify the stored proc and remove the WHERE clause completely, and just add the "AND tblProducts.start_time >= @.start_time AND tblProducts.start_time <= @.end_time". to the end of my statement in plain SQL, then it works.

** It only seems to not work when I put build my WHERE clause in a variable **

I hope this is not too confusing.

Here is the stored proc as it is now....not working.

ALTER PROCEDURE [dbo].[spReport_ProductSerialAssemblyResults]
-- Add the parameters for the stored procedure here
@.pec varchar(30) = NULL,
@.rel varchar(30) = NULL,
@.ver varchar(10) = NULL,
@.sn varchar(50) = NULL,
@.start_time datetime = NULL,
@.end_time datetime = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @.where_clause varchar(MAX)

set @.where_clause = '';

if ((@.start_time is not null) and (@.end_time is not null))
BEGIN
set @.where_clause = ' AND tblProducts.start_time >= @.start_time AND tblProducts.start_time <= @.end_time';
END



-- Insert statements for procedure here
SELECT * FROM tblProducts
WHERE (tblProducts.in_production = 1) AND tblProducts.pec = COALESCE(@.pec, tblProducts.pec)
AND tblProducts.release = COALESCE(@.rel, tblProducts.release)
AND tblProducts.version = COALESCE(@.ver, tblProducts.version)
AND tblProducts.serial = COALESCE(@.sn, tblProducts.serial) + @.where_clause;


END

To do what you are trying to do where you take a character variable and execute it as SQL code you need to use dynamic SQL. See the EXECUTE statement, and the sp_executesql stored procedure for info on dynamic SQL.

However, in your case dynamic SQL is not necessary (and it should be avoided unless necessary). What you can do in your SELECT statement is simply this

SELECT * FROM tblProducts
WHERE (tblProducts.in_production = 1)
AND tblProducts.pec = COALESCE(@.pec, tblProducts.pec)
AND tblProducts.release = COALESCE(@.rel, tblProducts.release)
AND tblProducts.version = COALESCE(@.ver, tblProducts.version)
AND tblProducts.serial = COALESCE(@.sn, tblProducts.serial)
AND (tblProducts.start_time >= @.start_time
AND tblProducts.start_time <= @.end_time
OR (@.start_time IS NULL OR @.end_time IS NULL))

|||

Please take a look at the link below for various solutions that doesn't require dynamic SQL code.

http://www.sommarskog.se/dyn-search.html

|||Thank you both for the great feedback...