build sql script: varchar

Showing posts with label varchar. Show all posts

Sunday, March 25, 2012

Bulk Insert with only one column

Hi,

I have a question on inserting data for only a specific column in a table.

I have a table as follows

Table <MyTable>

{

Name varchar,

DateUpdate DateTime

}

I wanted to insert the the from a file to table.

The File contains the list of name as follows (line by line)

name1

name2

name3

name4

......

The file name actually contains the DateTime

I would like to insert the names in the file as wellas the DateTime (i.e. part of the file name ) into <MyTable>

I guess "Bulk insert " doens't allow to insert values for only one column

If i change the contents of my data file to

name1 | DateTime1

name2 | DateTime2

name3 | DateTime3

name4 | DateTime4

Then the follwoin query works fine for me.

Bulk Insert <MyTable> FROM <filePath>

With

{

FIELDTERMINATOR = '|'

ROWTERMINATOR = '\n'

}

But my original file will contains only Names and the file name contains the date that commom for all the names in the file. And also the file may contains millions of names

Is there any way this can be accomplised using " Bulk Insert" ? Or is there any alternative that i can do it fastly

Your answer will be appreciated

~mohan

Instead of achieving this task in one step, can you do in two steps making use of a staging table that matches the file structure and insert to the main table after that. There might be other solutions also, lets hear from other people.

|||

Hi Mohan,

Answer for your First Question :

Table <MyTable>

{

Name varchar,

DateUpdate DateTime

}

Create a view like :

create view vwMyTable

select Name from MyTable

Use this view in Bulk Insert to load the data instead of the table.

Here the assumption is your DataUpdate allows Null or any default assigned to it.

Answer for your Second Question :

Insert all your data file names into a temp table using xp_cmdshell output.

Read from the table one by one and execute the bulk insert and update the datetime from table.

Thanks

Naras.

|||

If your use SQL Server 2005, you coult use OPENROWSET with BULK option:

Code Snippet

INSERT INTO youTable

SELECT a.Name, <some expression for datetime> FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS a;

|||

You can build an SS Integraton Services package

1. For get a new datatime column you can use Derived Column Data Flow Transformation

2. For separating your data in Name and DateUpdate you can use Fixed Width Format in Flat File Connection Manager; in this way you put a column delimiter in desired position of initial string , depends on the size of DateUpdate.

This package can be put in a job a launched periodically if your business logic need it.

|||

I would also suggest that you use a stage table for the initial load, then do whatever 'massaging' needs to be done, and after that update your production table.

/Kenneth

Thursday, March 22, 2012

BULK INSERT to a Temporary Table

I wanto to execute a BULK INSERT command into a Temporary Table:
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'c\Projects.txt'
But when I execute this command a recieve the following error message:
"The current user is not the database or object owner of table
'#textfile'. Cannot perform SET operation."
Im logged in a Microsoft SQL Server 2000, with a login account that
belogs to the role db_owner of the database Im working, but does not
belong to the role db_owner of tempdb database. Is it necessary?
What am I doing wrong?
Thanks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***
Hi
You may wantr to check out
http://support.microsoft.com/default...b;en-us;302621 regarding
permissions needed.
John
"Paulo Andre Ortega Ribeiro" wrote:

> I wanto to execute a BULK INSERT command into a Temporary Table:
> CREATE TABLE #textfile (line varchar(8000))
> -- Read the text file into the temp table
> BULK INSERT #textfile FROM 'c\Projects.txt'
> But when I execute this command a recieve the following error message:
> "The current user is not the database or object owner of table
> '#textfile'. Cannot perform SET operation."
> I4m logged in a Microsoft SQL Server 2000, with a login account that
> belogs to the role db_owner of the database I4m working, but does not
> belong to the role db_owner of tempdb database. Is it necessary?
> What am I doing wrong?
> Thanks,
> Paulo
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

BULK INSERT to a Temporary Table

I wanto to execute a BULK INSERT command into a Temporary Table:
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'c\Projects.txt'
But when I execute this command a recieve the following error message:
"The current user is not the database or object owner of table
'#textfile'. Cannot perform SET operation."
Im logged in a Microsoft SQL Server 2000, with a login account that
belogs to the role db_owner of the database Im working, but does not
belong to the role db_owner of tempdb database. Is it necessary?
What am I doing wrong?
Thanks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***Hi
You may wantr to check out
http://support.microsoft.com/defaul...kb;en-us;302621 regarding
permissions needed.
John
"Paulo Andre Ortega Ribeiro" wrote:

> I wanto to execute a BULK INSERT command into a Temporary Table:
> CREATE TABLE #textfile (line varchar(8000))
> -- Read the text file into the temp table
> BULK INSERT #textfile FROM 'c\Projects.txt'
> But when I execute this command a recieve the following error message:
> "The current user is not the database or object owner of table
> '#textfile'. Cannot perform SET operation."
> I4m logged in a Microsoft SQL Server 2000, with a login account that
> belogs to the role db_owner of the database I4m working, but does not
> belong to the role db_owner of tempdb database. Is it necessary?
> What am I doing wrong?
> Thanks,
> Paulo
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

BULK INSERT to a Temporary Table

I wanto to execute a BULK INSERT command into a Temporary Table:
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'c\Projects.txt'
But when I execute this command a recieve the following error message:
"The current user is not the database or object owner of table
'#textfile'. Cannot perform SET operation."
I´m logged in a Microsoft SQL Server 2000, with a login account that
belogs to the role db_owner of the database I´m working, but does not
belong to the role db_owner of tempdb database. Is it necessary?
What am I doing wrong?
Thanks,
Paulo
*** Sent via Developersdex http://www.developersdex.com ***Hi
You may wantr to check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;302621 regarding
permissions needed.
John
"Paulo Andre Ortega Ribeiro" wrote:
> I wanto to execute a BULK INSERT command into a Temporary Table:
> CREATE TABLE #textfile (line varchar(8000))
> -- Read the text file into the temp table
> BULK INSERT #textfile FROM 'c\Projects.txt'
> But when I execute this command a recieve the following error message:
> "The current user is not the database or object owner of table
> '#textfile'. Cannot perform SET operation."
> I4m logged in a Microsoft SQL Server 2000, with a login account that
> belogs to the role db_owner of the database I4m working, but does not
> belong to the role db_owner of tempdb database. Is it necessary?
> What am I doing wrong?
> Thanks,
> Paulo
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Tuesday, March 20, 2012

bulk insert question

Hi! We are in the process of adding few columns to our large tables (200
million rows) and altering few columns from varchar to char. One of the
option I am thinking is to bcp data out, change the schema and bulk insert
data in. Looks like with either bcp.exe or bulk insert command, you won't be
able to load the data in, if the schema of table get changed. Is this true?
I know that I can use DTS export/import to do this task but since bulk
insert is the fastest method I would like to try that option if possible.
Besides, Bulk insert I could also change schema with Alter table command. I
don't know if thats better than unloading/change/reload method that I
mentioned above.
I would appreiciate it, if anyone who have export/change schema/import large
table, give me some direction here.
thanksIf you use the Native mode I don't think you can do it but have you actually
tried? BCP out a few thousand rows, create anew table and Bulk Insert it
back in. If native wont work I am pretty sure char mode will.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> Hi! We are in the process of adding few columns to our large tables (200
> million rows) and altering few columns from varchar to char. One of the
> option I am thinking is to bcp data out, change the schema and bulk insert
> data in. Looks like with either bcp.exe or bulk insert command, you won't
> be
> able to load the data in, if the schema of table get changed. Is this
> true?
> I know that I can use DTS export/import to do this task but since bulk
> insert is the fastest method I would like to try that option if possible.
> Besides, Bulk insert I could also change schema with Alter table command.
> I
> don't know if thats better than unloading/change/reload method that I
> mentioned above.
> I would appreiciate it, if anyone who have export/change schema/import
> large
> table, give me some direction here.
> thanks
>|||I have tried both native and character mode and both erroed out. Which is
kind of expected, since schema got changed, the program doesn't have any way
of knowing which column in datafile (native or char) maps to which column in
table.
I haven't tried format file so far, which is what I am going to do next and
see if I can alter format file and make this thing work.
Thanks for your Input.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> If you use the Native mode I don't think you can do it but have you
actually
> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
> back in. If native wont work I am pretty sure char mode will.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> > Hi! We are in the process of adding few columns to our large tables (200
> > million rows) and altering few columns from varchar to char. One of the
> > option I am thinking is to bcp data out, change the schema and bulk
insert
> > data in. Looks like with either bcp.exe or bulk insert command, you
won't
> > be
> > able to load the data in, if the schema of table get changed. Is this
> > true?
> > I know that I can use DTS export/import to do this task but since bulk
> > insert is the fastest method I would like to try that option if
possible.
> > Besides, Bulk insert I could also change schema with Alter table
command.
> > I
> > don't know if thats better than unloading/change/reload method that I
> > mentioned above.
> > I would appreiciate it, if anyone who have export/change schema/import
> > large
> > table, give me some direction here.
> >
> > thanks
> >
> >
>|||Yes if you change the columns around you need to use a format file. But I
don't see why it wont work with the format file.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>I have tried both native and character mode and both erroed out. Which is
> kind of expected, since schema got changed, the program doesn't have any
> way
> of knowing which column in datafile (native or char) maps to which column
> in
> table.
> I haven't tried format file so far, which is what I am going to do next
> and
> see if I can alter format file and make this thing work.
> Thanks for your Input.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>> If you use the Native mode I don't think you can do it but have you
> actually
>> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
>> back in. If native wont work I am pretty sure char mode will.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "james" <kush@.brandes.com> wrote in message
>> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
>> > Hi! We are in the process of adding few columns to our large tables
>> > (200
>> > million rows) and altering few columns from varchar to char. One of the
>> > option I am thinking is to bcp data out, change the schema and bulk
> insert
>> > data in. Looks like with either bcp.exe or bulk insert command, you
> won't
>> > be
>> > able to load the data in, if the schema of table get changed. Is this
>> > true?
>> > I know that I can use DTS export/import to do this task but since bulk
>> > insert is the fastest method I would like to try that option if
> possible.
>> > Besides, Bulk insert I could also change schema with Alter table
> command.
>> > I
>> > don't know if thats better than unloading/change/reload method that I
>> > mentioned above.
>> > I would appreiciate it, if anyone who have export/change schema/import
>> > large
>> > table, give me some direction here.
>> >
>> > thanks
>> >
>> >
>>
>|||With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ov1hlWrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Yes if you change the columns around you need to use a format file. But I
> don't see why it wont work with the format file.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> >I have tried both native and character mode and both erroed out. Which is
> > kind of expected, since schema got changed, the program doesn't have any
> > way
> > of knowing which column in datafile (native or char) maps to which
column
> > in
> > table.
> > I haven't tried format file so far, which is what I am going to do next
> > and
> > see if I can alter format file and make this thing work.
> > Thanks for your Input.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> >> If you use the Native mode I don't think you can do it but have you
> > actually
> >> tried? BCP out a few thousand rows, create anew table and Bulk Insert
it
> >> back in. If native wont work I am pretty sure char mode will.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "james" <kush@.brandes.com> wrote in message
> >> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> >> > Hi! We are in the process of adding few columns to our large tables
> >> > (200
> >> > million rows) and altering few columns from varchar to char. One of
the
> >> > option I am thinking is to bcp data out, change the schema and bulk
> > insert
> >> > data in. Looks like with either bcp.exe or bulk insert command, you
> > won't
> >> > be
> >> > able to load the data in, if the schema of table get changed. Is this
> >> > true?
> >> > I know that I can use DTS export/import to do this task but since
bulk
> >> > insert is the fastest method I would like to try that option if
> > possible.
> >> > Besides, Bulk insert I could also change schema with Alter table
> > command.
> >> > I
> >> > don't know if thats better than unloading/change/reload method that I
> >> > mentioned above.
> >> > I would appreiciate it, if anyone who have export/change
schema/import
> >> > large
> >> > table, give me some direction here.
> >> >
> >> > thanks
> >> >
> >> >
> >>
> >>
> >
> >
>

Monday, March 19, 2012

Bulk Insert into a Table Variable

I am attempting to use a table variable as a destination for a bulk insert.

DECLARE @.TEXTFILE_1 TABLE

(CHAR_FIELD1 varchar(1) ,

CHAR_FIELD2 varchar(1) ,

CHAR_FIELD3 varchar(1) ,

CHAR_FIELD4 varchar(1) )

BULK INSERT @.TEXTFILE_1 FROM 'C:\TRASH.TXT'

WITH (FIELDTERMINATOR =' | ',ROWTERMINATOR =' \n')

The input file looks like:

A | B | C | D
E | F | G | H

But the errors indicate that the input is not the issue (or at least not yet...).

The errors look like:

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '@.TEXTFILE_1'.

Msg 319, Level 15, State 1, Line 8

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

I couldn't find any documentation that stated a table varable is an invalid destination for a bulk insert, but it is looking like that is the case. Any suggestions would be appreciated.

Thanks

Yeah, bulk insert is designed for actual tables, not table variables. Silly really, as you would think the system shouldn't really differentiate between the two... but unfortunately it does. Try creating a table in your database and seeing if it works with only that changed - if it does, then there's your problem for sure.

Rob|||You can't BULK INSERT into a table variable. If you are on SQL Server 2005 you can use INSERT...SELECT OPENROWSET(BULK) instead. See BOL for more help on how to use OPENROWSET(BULK).|||Unfortunately this client is on an older version of SQL and will not be upgrading until late this year at the earliest.

Is there an alternative to bulk insert for SQL 2000? We were trying to use a temporary table. That worked until the stored procedure was run under a login different than the user who ran the script that created the SP. That produced an error: "The current user is not the database or object owner of table 'name of temp table here'. Cannot perform SET operation." In understand this is a known issue. http://laneys.info/node/487

We didn't want to create a permanent scratch table since we would have to deal with the multi-user aspect and cleanup.

Because of the format of the data stored in the text file we cannot import directly to the destination table. We are trying to contain all of the activity in the base application which means we cannot write a parser in .NET. These are the hazards providing third party support.

Thanks so much for your help,
Richard|||

Does the workaround in the KB article referenced in the link above help? The KB article is:

http://support.microsoft.com/default.aspx?scid=kb;en-us;302621

Is it possible for you to give a detailed description of your setup? Any code will also help.

1. Who owns the SP?

2. How is the temporary table created? Is it inside the SP?

3. What privileges does the user calling the SP have in the user database? Is he part of some roles?

4. Does the user belong to any roles in the tempdb?

Bulk Insert into a Table Variable

I am attempting to use a table variable as a destination for a bulk insert.

DECLARE @.TEXTFILE_1 TABLE

(CHAR_FIELD1 varchar(1) ,

CHAR_FIELD2 varchar(1) ,

CHAR_FIELD3 varchar(1) ,

CHAR_FIELD4 varchar(1) )

BULK INSERT @.TEXTFILE_1 FROM 'C:\TRASH.TXT'

WITH (FIELDTERMINATOR =' | ',ROWTERMINATOR =' \n')

The input file looks like:

A | B | C | D
E | F | G | H

But the errors indicate that the input is not the issue (or at least not yet...).

The errors look like:

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '@.TEXTFILE_1'.

Msg 319, Level 15, State 1, Line 8

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

I couldn't find any documentation that stated a table varable is an invalid destination for a bulk insert, but it is looking like that is the case. Any suggestions would be appreciated.

Thanks

We didn't want to create a permanent scratch table since we would have to deal with the multi-user aspect and cleanup.

Thanks so much for your help,
Richard|||

Does the workaround in the KB article referenced in the link above help? The KB article is:

http://support.microsoft.com/default.aspx?scid=kb;en-us;302621

Is it possible for you to give a detailed description of your setup? Any code will also help.

1. Who owns the SP?

2. How is the temporary table created? Is it inside the SP?

3. What privileges does the user calling the SP have in the user database? Is he part of some roles?

4. Does the user belong to any roles in the tempdb?

Sunday, March 11, 2012

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 HELP

I am trying to do the following

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

DECLARE @.FilePath varchar(50)

SELECT @.FilePath = 'C:/Test/Test.log'

SELECT

@.doc = c from OpenRowset (BULK + @.Filepath + , SINGLE_BLOB) as T (c)

I get the following error

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near '@.Filepath'.

I am sure this is really basic.Can some one let me know what am i doing wrong

Thanks

You will have to use dynamic sql.

declare @.doc varbinary(max)

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = c from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql, N'@.doc varbinary(max) output', @.doc

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||

Thanks for the reply

Cna i do something like :

declare @.doc varbinary(max)

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

declare @.hdoc int

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = c from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql, N'@.doc varbinary(max) output', @.doc

exec sp_xml_preparedocument @.hdoc OUTPUT,@.doc

Thanks

|||

Use the new XML datatype, If you use XML datatype, you need not to use the sp_xmlpreparedocuement(which uses more expensive resources & legecy com objects).

See XQuery on BOL to understand how to work with new XML data typed values. if you failed to use XML datatype / XML casting you only get xml content as binary..

Code Snippet

declare @.doc XML

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

declare @.hdoc int

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = convert(xml,c) from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql , N'@.doc XML OUTPUT', @.doc OUTPUT

Select @.doc

|||

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

--DECLARE @.doca varchar(max)

DECLARE @.SQL nvarchar(200)

DECLARE @.Filepath varchar(50)

SET @.Filepath = 'C:\XML_Processing\Test.LOG'

SET @.SQL = N'select @.doc = c from OpenRowset(BULK''' + @.Filepath + N''', SINGLE_CLOB) as T(c)'

exec

sp_executesql @.SQL, N'@.doc varchar(max) output' ,@.doc

SELECT @.doc

print @.doc

print @.SQL

I was expecting @.doc to have the whole XML from Test,LOG but it is returning me NULL

Any ideas whats going on?

Thanks

Hemanshu

Thursday, March 8, 2012

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('* Devil ', 'char(15)') AS 'psc'

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

,C.value('* Music ', '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:

Format File:

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 ...

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('* Devil ', 'char(15)') AS 'psc'

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

,C.value('* Music ', '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:

Format File:

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 ...

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('* Devil ', 'char(15)') AS 'psc'

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

,C.value('* Music ', '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)

Saturday, February 25, 2012

Bulk Insert

I have a problem with bulk insert importation. I have a txt file with 3400 columns and 1 table with two fields.

data - varchar(5000)
id - numeric (identity yes - 1)

when I import the file the system return this error message:

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.

my bulk insert sintaxe is:

BULK INSERT eflex.dbo.Import_Data
FROM 'C:\HP\HP_Receive\FBSJ01D5.txt'
WITH (FIELDTERMINATOR = '\n')

can anybody help-me?

thanks,Sure
1. What is your row terminator?
2. Maybe the data row exceeds your definition.
3-n any number of data or schema errors

Try this ... create a input table with 1 column defined as varchar(8000), then bcp in the file. You can then use SQL Server to inspect column lengths, and even parse the data into your working table.

Tuesday, February 14, 2012

Built table problem

Hi.

I am a beginner of SQL server.
I have a table namely Customer with a column "CustomerName" with data type varchar and length 50.
Column Name-Customer Name
Data Type- varchar
Length- 50.

May I know how to ENSURE that every customer's name only contain ALPHABET and strictly avoid the numeric character and
other characters such as "&, !,#...".

Please help. Thank you.See the BOL for Check Constraints !!!|||What about this idea?

create table test(id int,
code varchar(50)
CONSTRAINT mycheck check(code+replicate('Z',50-datalength(code)) like replicate('[A-Z]',50) or
code+replicate('Z',50-datalength(code)) like replicate('[a-z]',50)
))|||Very Nice

INSERT INTO test(code) SELECT 'ABC'
INSERT INTO test(code) SELECT '123'
INSERT INTO test(code) SELECT 'AB.'|||INSERT INTO test(code) SELECT 'Brett Kaiser'|||Originally posted by Enigma

INSERT INTO test(code) SELECT 'Brett Kaiser'

Now it is even more simple:

drop table test
go
create table test(id int,
code varchar(50)
CONSTRAINT mycheck check(lower(replace(code,' ','Z')+replicate('Z',50-datalength(code))) like replicate('[a-z]',50)
)
)
go
INSERT INTO test(code) SELECT 'Brett Kaiser'|||That looks cool !!! :)

Sunday, March 25, 2012

Bulk Insert with only one column

Thursday, March 22, 2012

BULK INSERT to a Temporary Table

BULK INSERT to a Temporary Table

BULK INSERT to a Temporary Table

Tuesday, March 20, 2012

bulk insert question

bulk insert question

bulk insert question

Monday, March 19, 2012

Bulk Insert into a Table Variable

Bulk Insert into a Table Variable

Sunday, March 11, 2012

BULK INSERT HELP!

BULK INSERT HELP!

BULK INSERT HELP

Thursday, March 8, 2012

Bulk insert errors

Bulk insert errors

Bulk insert errors

Saturday, February 25, 2012

Bulk Insert

Tuesday, February 14, 2012

Built table problem

build sql script

Blog Archive

About Me