Bulk load doesn't preserve element order?

Hi,
I have an interesing issue with sqlxml bulk load. It's about the order in which bulk load inserts rows in the database. I assumed that the rows should be inserted in the same order as the elements appear in the xml file, but looks like it is not always the case.
We use SQL server 2000 and SQLXML 3.0 SP 3. The files are large(2GB) and look something like this:
<Betalingskrav>
<Krav>
<BetalingsKravStatus><![CDATA[1]]></BetalingsKravStatus>
<FakturaDato><![CDATA[04.02.2006]]></FakturaDato>
<CustomerAgreementReference>
<![CDATA[12345689]]></CustomerAgreementReference>
....
<Samtaler>
<Samtale_Gruppeheader>
<SamtaleType><![CDATA[Mobilsamtaler]]></SamtaleType>
<Benevning><![CDATA[Abonnentnr]]></Benevning>
<Telefonnr><![CDATA[12 34 56 78]]></Telefonnr>
<Periode><![CDATA[Periode03.01.06-02.02.06]]></Periode>
<Etikett_Dest><![CDATA[Destinasjon/Operator]]></Etikett_Dest>
<Etikett_Oppr_nr><![CDATA[Oppringt nummer]]></Etikett_Oppr_nr>
<Etikett_SamtDato><![CDATA[Dato]]></Etikett_SamtDato>
<Etikett_Samtstart><![CDATA[Start]]></Etikett_Samtstart>
<Etikett_lngd><![CDATA[Samtalelengde]]></Etikett_lngd>
<Etikett_Kost><![CDATA[Kostnad]]></Etikett_Kost>
</Samtale_Gruppeheader>
<Samtale_Data>
<Dta_Dest><![CDATA[Tele2 GSM]]></Dta_Dest>
<Dta_Oppr_nr><![CDATA[12345678]]></Dta_Oppr_nr>
<Dta_SamtDato><![CDATA[02.01.06]]></Dta_SamtDato>
<Dta_Samtsstart><![CDATA[11:13]]></Dta_Samtsstart>
<Dta_lngd><![CDATA[ 0:00:37]]></Dta_lngd>
<Dta_Kost><![CDATA[0.81]]></Dta_Kost>
</Samtale_Data>
<Samtale_Data>
....
</Samtale_Data>
...
</Samtaler>
</Krav>
...
</Betalingskrav>

The schema (i list a shortened version):
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Krav_T2KravLinje"
parent="Betalingskrav"
parent-key="BetalingskravID"
child="T2BetalingskravLinje"
child-key="BetalingskravID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="KravType">
<xsd:all>
<xsd:element name="BetalerID" default="9999" />
<xsd:element name="FakturaTypeID" default="0" />
<xsd:element name="MalID" default="0" />
<xsd:element name="BuntID" default="0" />
<xsd:element name="UtstederID" default="28" />
...
<xsd:element name="Samtaler" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Samtale_Gruppeheader" type="Samtale_GruppeheaderType"
sql:relation="T2BetalingskravLinje" sql:relationship="Krav_T2KravLinje" />
<xsd:element name="Samtale_Data" type="Samtale_DataType"
sql:relation="T2BetalingskravLinje" sql:relationship="Krav_T2KravLinje"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="MVATekst1" type="xsd:string" />
<xsd:element name="MVATekst2" type="xsd:string" />
...
</xsd:all>
</xsd:complexType>
<xsd:complexType name="Samtale_GruppeheaderType">
<xsd:sequence>
<xsd:element name="SamtaleType" type="xsd:string" />
<xsd:element name="Benevning" type="xsd:string" />
<xsd:element name="Telefonnr" type="xsd:string" />
<xsd:element name="Periode" type="xsd:string" />
<xsd:element name="Etikett_Dest" type="xsd:string" />
<xsd:element name="Etikett_Oppr_nr" type="xsd:string" />
<xsd:element name="Etikett_Samtant" type="xsd:string" />
<xsd:element name="Etikett_SamtDato" type="xsd:string" />
<xsd:element name="Etikett_Samtstart" type="xsd:string" />
<xsd:element name="Etikett_lngd" type="xsd:string" />
<xsd:element name="Etikett_Kost" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="Samtale_DataType">
<xsd:sequence>
<xsd:element name="Dta_Dest" type="xsd:string" />
<xsd:element name="Dta_Oppr_nr" type="xsd:string" />
<xsd:element name="Dta_Samtant" type="xsd:string" />
<xsd:element name="Dta_SamtDato" type="xsd:string" />
<xsd:element name="Dta_Samtsstart" type="xsd:string" />
<xsd:element name="Dta_lngd" type="xsd:string" />
<xsd:element name="Dta_Kost" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:element name="Betalingskrav" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Krav" type="KravType" sql:relation="Betalingskrav" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
XML data represents telephone bills with a listing of all calls, sms's, data services and so on. Samtale_Gruppheader elements define headers, and Samtale_Data elements contain the items that go below the headings. The point is that the sequence of elements inside <Samtaler> must be preserved in the database via identity column to provide correct formatting of bills. Not a good approach, I understand, but we are dealing with a legacy system that is not easy to modify...
The shema above maps xml data to two tables in the database that have primary-foreign relationship. The foreign-key table(T2BetalingskravLinje) contains both contents of Samtale_Gruppeheader and Samtale_Data elements. When the bills are rendered, rows in this table are shown in the sequence given by the identity column, in other words in the order they were inserted into the table.
KeepIdentity property is set to false on the bulk load component, the SQL server generates identity keys itself. This works fine, foreign keys are propagated correctly. But the order of rows is not the same as the order of elements in <Samtaler>! Not all the times, but in about 5 to 10% of the cases and I don't see any pattern here. I extracted a <krav> element that had this problem from a real file, and tried to bulk load the resulting short file. The effect disappered, rows were in correct order...
So the question is: Is this behaiviour normal for bulk load component and the order of row insertion into foreign-key tables is not guaranteed to be the same as the order of elements in the xml file? Is it a bug or a feature? :-) Is there any way to enable order preservation?
Thanks in advance for any ideas or suggestions!

The bulkloading component on which the SQLXML bulkloader is built does not guarantee order. So the behaviour is normal.

Best regards

Michael

sql

Bulk Insert.

Is there any way I can use the following command like that ?
BULK INSERT Northwind.dbo.[Order Details]
FROM (select CSVTextFile from tblCSV)
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
Thanks in advance
The syntax of BULK INSERT is

quote:

---

BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM
'data_file' }...
'data_file'
Is the full path of the data file that contains data to copy into the
specified table or view. BULK INSERT can copy data from a disk (including
network, floppy disk, hard disk, and so on).
data_file must specify a valid path from the server on which SQL Server is
running. If data_file is a remote file, specify the Universal Naming
Convention (UNC) name.

---

Please refer to Books Online for examples of how to use BULK INSERT
http://msdn.microsoft.com/library/de...ba-bz_4fec.asp
Cristian Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET
"Rogers" <Rogers@.mailstuff.com> wrote in message
news:Oyx71bHuFHA.2624@.TK2MSFTNGP12.phx.gbl...
> Is there any way I can use the following command like that ?
> BULK INSERT Northwind.dbo.[Order Details]
> FROM (select CSVTextFile from tblCSV)
> WITH
> (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> Thanks in advance
>
sql

BULK INSERT into table with identity column?

I have a file I'm trying to do some non-set-based processing with. In
order to make sure I keep the order of the results, I want to BULK
INSERT into a temp table with an identity column. The spec says that
you should be able to use either KEEPIDENTITY or KEEPNULLS, but I can't
get it to work. For once, I have full code - just add any file of your
choice that doesn't have commas/tabs. :)

Any suggestions, folks?

--create table ##Holding_Tank ( full_record varchar(500)) -- this
works
create table ##Holding_Tank (id int identity(1,1) primary key,
full_record varchar(500)) --that doesn't work

BULK INSERT ##Holding_Tank
FROM "d:\telnet_scripts\psaxresult.txt"
WITH
(
TABLOCK,
KEEPIDENTITY,
KEEPNULLS,
MAXERRORS = 0
)
select * from ##Holding_tankM Bourgon (bourgon@.gmail.com) writes:

Quote:

Originally Posted by

I have a file I'm trying to do some non-set-based processing with. In
order to make sure I keep the order of the results, I want to BULK
INSERT into a temp table with an identity column. The spec says that
you should be able to use either KEEPIDENTITY or KEEPNULLS, but I can't
get it to work. For once, I have full code - just add any file of your
choice that doesn't have commas/tabs. :)
>
Any suggestions, folks?

Even if you want the identity values to be generated for you, the column
still has to be in the file if you don't use a format file.

There are two way to go: add a 0 and a delimiter on each line in the
file, or use this format file:

8.0
1
1 SQLCHAR 0 0 "\r\n" 2 col ""

What I don't really remember is whether you actually can trust the
IDENTITY values to match the order the rows appear in the file. If you
want to play safe, you may want to write a program that reads the file
and adds a line number on each line in the file.

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

Quote:

Originally Posted by

Even if you want the identity values to be generated for you, the column
still has to be in the file if you don't use a format file.

Aha.

Quote:

Originally Posted by

What I don't really remember is whether you actually can trust the
IDENTITY values to match the order the rows appear in the file. If you
want to play safe, you may want to write a program that reads the file
and adds a line number on each line in the file.

Okay, thanks again, Erland. I was afraid of that.

bulk insert in the exact sequence as the text file

I'm having an issue when doing a bulk insert on a SQL 2000 server. I need
the table rows to load in the exact order that the rows in the text file are
sequenced.
So far, the bulk insert appears to be loading the file in 2 different
orders. One of the orders is the same order as the text file. The other
order is completely different and does not appear to have any reason to be i
n
the different order.
I've tried setting the BATCHSIZE argument to 1 and that doesn't fix it.
Using the ORDER argument doesn't help either because the destination table
doesn't have a primary key plus I don't want to change the order of the text
file anyways.
Is there a way to ensure the BULK INSERT loads the file in the exact order
that the text file is in?How do you know that table is not loaded in sequence from your text file? A
table is an unordered set of rows so you must specify ORDER BY when
selecting from the table in order to guarantee sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"eieed" <eieed@.discussions.microsoft.com> wrote in message
news:5A497B7A-E3EC-4C30-B1A4-D13C0E23DEA1@.microsoft.com...
> I'm having an issue when doing a bulk insert on a SQL 2000 server. I need
> the table rows to load in the exact order that the rows in the text file
> are
> sequenced.
> So far, the bulk insert appears to be loading the file in 2 different
> orders. One of the orders is the same order as the text file. The other
> order is completely different and does not appear to have any reason to be
> in
> the different order.
> I've tried setting the BATCHSIZE argument to 1 and that doesn't fix it.
> Using the ORDER argument doesn't help either because the destination table
> doesn't have a primary key plus I don't want to change the order of the
> text
> file anyways.
> Is there a way to ensure the BULK INSERT loads the file in the exact order
> that the text file is in?

Bulk Insert Excel File

Hi there,
What parameters do I need to define in order to bulk insert data in an
Excel file into a SQL table?
FIELDTERMINATOR = ?
ROW TERMINATOR = ?
Are there any other parameters I need to specify?
Thanks!What version of SQL are you using? If you are using 2000, then create
a DTS package. If it's 2005, then an SSIS package/job.
On May 22, 3:57 pm, Yvette <yvetteyc...@.gmail.com> wrote:
> Hi there,
> What parameters do I need to define in order to bulk insert data in an
> Excel file into a SQL table?
> FIELDTERMINATOR = ?
> ROW TERMINATOR = ?
> Are there any other parameters I need to specify?
> Thanks!

Bulk Insert Excel File

Hi there,
What parameters do I need to define in order to bulk insert data in an
Excel file into a SQL table?
FIELDTERMINATOR = ?
ROW TERMINATOR = ?
Are there any other parameters I need to specify?
Thanks!What version of SQL are you using? If you are using 2000, then create
a DTS package. If it's 2005, then an SSIS package/job.
On May 22, 3:57 pm, Yvette <yvetteyc...@.gmail.com> wrote:
> Hi there,
> What parameters do I need to define in order to bulk insert data in an
> Excel file into a SQL table?
> FIELDTERMINATOR = ?
> ROW TERMINATOR = ?
> Are there any other parameters I need to specify?
> Thanks!

Bulk Insert Excel File

Hi there,
What parameters do I need to define in order to bulk insert data in an
Excel file into a SQL table?
FIELDTERMINATOR = ?
ROW TERMINATOR = ?
Are there any other parameters I need to specify?
Thanks!
What version of SQL are you using? If you are using 2000, then create
a DTS package. If it's 2005, then an SSIS package/job.
On May 22, 3:57 pm, Yvette <yvetteyc...@.gmail.com> wrote:
> Hi there,
> What parameters do I need to define in order to bulk insert data in an
> Excel file into a SQL table?
> FIELDTERMINATOR = ?
> ROW TERMINATOR = ?
> Are there any other parameters I need to specify?
> Thanks!

Bulk insert and vb.net - URGENT

Hello.
I want to use bulk insert in order to import a huge file, lets say
"Source.txt" in my db, lets say "MyDB".
The data in "Source.txt" are numerical and date format. Values are seperated
with the tab character.
When I use :
BULK INSERT [owner].[dbname].[tablename] FROM
'c:\Source.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
KEEPNULLS )
everything works fine.
Now, I want to add a datetime field in the beginning of each line in
source.txt.
I do this by :
<...open Source.txt as 1...>
<...open Altered.txt as 2...>
OldLine=LineInput(1)
NewLine="...DateVariable..." & vbTab &
OldLine
Print 2, OldLine & vbNewLine
<...close files...>
Why does
BULK INSERT [owner].[dbname].[tablename] FROM
'c:\Altered.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
KEEPNULLS )
doesn't work?
The error message I get is
Bulk insert data conversion error (type mismatch) for row 1,
column 385 (...column description...).
I have tried with different row terminators but no success.
I suspect that while creating the Altered.txt, 2 row terminator characters
are added instead of 1.
Please any help will be much appreciated.
Thank you,
VasilisDid you also recreate the table with the datetime as the first column? File
fields are mapped to target table columns by ordinal position unless you
specify a format file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1d59$2pa3$1@.ulysses.noc.ntua.gr...
> Hello.
> I want to use bulk insert in order to import a huge file, lets say
> "Source.txt" in my db, lets say "MyDB".
> The data in "Source.txt" are numerical and date format. Values are
> seperated with the tab character.
> When I use :
> BULK INSERT [owner].[dbname].[tablename] FROM
> 'c:\Source.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
> KEEPNULLS )
> everything works fine.
> Now, I want to add a datetime field in the beginning of each line in
> source.txt.
> I do this by :
> <...open Source.txt as 1...>
> <...open Altered.txt as 2...>
> OldLine=LineInput(1)
> NewLine="...DateVariable..." & vbTab & OldLine
> Print 2, OldLine & vbNewLine
> <...close files...>
>
> Why does
> BULK INSERT [owner].[dbname].[tablename] FROM
> 'c:\Altered.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
> KEEPNULLS )
> doesn't work?
> The error message I get is
> Bulk insert data conversion error (type mismatch) for row
> 1, column 385 (...column description...).
>
> I have tried with different row terminators but no success.
> I suspect that while creating the Altered.txt, 2 row terminator characters
> are added instead of 1.
> Please any help will be much appreciated.
> Thank you,
> Vasilis
>
>
>|||Yes. The table stracture was changed accordingly.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23WQyROGPGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Did you also recreate the table with the datetime as the first column?
> File fields are mapped to target table columns by ordinal position unless
> you specify a format file.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
> news:du1d59$2pa3$1@.ulysses.noc.ntua.gr...
>
>|||>>> Print 2, OldLine & vbNewLine
Reviewing your pseudo-code, it looks to me like your are writing out the
original record instead of the new one with the date. Check the output file
to ensure it is as expected. If you still have problems, post the actual
code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1gjs$14o$1@.ulysses.noc.ntua.gr...
> Yes. The table stracture was changed accordingly.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23WQyROGPGHA.2124@.TK2MSFTNGP14.phx.gbl...
>
>|||Yes, you are right.
I actually use Print 2, Newline & vbNewLine.
Following is the actual code :
FileOpen(2, Bulk_File, OpenMode.Output)
While Not EOF(1)
Input(1, lineText)
'create new line
Dim NewLine As String
NewLine = NewDateVariable & vbTab & lineText
'print new line
Print(2, NewLine & vbCrLf) 'Also tried with NewLine, NewLine &
vbNewLine, even with a simple terminator, eg "!" and changed bulk command
syntax to ...ROWTERMINATOR='!'...
End While
FileClose()
Dim bulkCom As String
bulkCom = "BULK INSERT [mydatabase].[owner].[mytable] FROM '" & Bulk_File &
"' WITH ( FIELDTERMINATOR = '\t' , ROWTERMINATOR = '\n'' , KEEPNULLS )"
bulkCom &= WED.ExecuteCommand(bulkCom, False)
End If
WED is a class to communicate with the server. It doesn't add anything to
the command that is to be executed
Bulk_File is the file to be imported|||Did you view the output file to make sure the format is as expected?
Unfortunately, this code snippet doesn't include the NewDataVariable
assignment, which may be part of the problem. I'd like to have enough code
so that I can reproduce your problem in my environment.
I notice that you use NewLine as a variable name. Perhaps
System.Environment.NewLine is being used instead so the output is not as
expected.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1kki$c2q$1@.ulysses.noc.ntua.gr...
> Yes, you are right.
> I actually use Print 2, Newline & vbNewLine.
> Following is the actual code :
> FileOpen(2, Bulk_File, OpenMode.Output)
> While Not EOF(1)
> Input(1, lineText)
> 'create new line
> Dim NewLine As String
> NewLine = NewDateVariable & vbTab & lineText
> 'print new line
> Print(2, NewLine & vbCrLf) 'Also tried with NewLine, NewLine
> & vbNewLine, even with a simple terminator, eg "!" and changed bulk
> command syntax to ...ROWTERMINATOR='!'...
> End While
> FileClose()
> Dim bulkCom As String
> bulkCom = "BULK INSERT [mydatabase].[owner].[mytable] FROM '" & Bulk_File
> & "' WITH ( FIELDTERMINATOR = '\t' , ROWTERMINATOR = '\n'' , KEEPNULLS )"
> bulkCom &= WED.ExecuteCommand(bulkCom, False)
> End If
>
> WED is a class to communicate with the server. It doesn't add anything to
> the command that is to be executed
> Bulk_File is the file to be imported
>
>|||The first thing I'd check is that the file you are getting has the correct
separator and terminator bytes. Possibly Print is adding its own line
terminators, e.g., vbCrLf. The easiest way to check is to open a Command
window, enter debug source.txt, and then type d at the - prompt. That will
dump the start of the file in ASCII and Hex. Enter d again to dump the next
128 bytes. Enter q to quit out of debug.
Your lines should be terminated by 0A. If they are terminated by 0D 0A, for
example, VB is adding a CR and LF (the standard Windows text file line
terminator), and you will have to modify your program or the BULK INSERT
accordingly.
HTH,
Mike Abraham
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1kki$c2q$1@.ulysses.noc.ntua.gr...
> Yes, you are right.
> I actually use Print 2, Newline & vbNewLine.
> Following is the actual code :
> FileOpen(2, Bulk_File, OpenMode.Output)
> While Not EOF(1)
> Input(1, lineText)
> 'create new line
> Dim NewLine As String
> NewLine = NewDateVariable & vbTab & lineText
> 'print new line
> Print(2, NewLine & vbCrLf) 'Also tried with NewLine, NewLine
> & vbNewLine, even with a simple terminator, eg "!" and changed bulk
> command syntax to ...ROWTERMINATOR='!'...
> End While
> FileClose()
> Dim bulkCom As String
> bulkCom = "BULK INSERT [mydatabase].[owner].[mytable] FROM '" & Bulk_File
> & "' WITH ( FIELDTERMINATOR = '\t' , ROWTERMINATOR = '\n'' , KEEPNULLS )"
> bulkCom &= WED.ExecuteCommand(bulkCom, False)
> End If
>
> WED is a class to communicate with the server. It doesn't add anything to
> the command that is to be executed
> Bulk_File is the file to be imported
>
>

BULK INSERT - Inserting txt file with columns in different order than define in table

How can I use bulk insert to insert a text file where the columns in the text file is in different order than the columns in the table?

I have a ZIP table with Zip_Code, Zip_City, Zip_State and the text file has the fields in Zip_City, Zip_State, Zip_Code. The instructions were to keep the order as defined in the Entity Definition which would be the first order.

My code for the bulk insert is usually

Code: ( text )

BULK INSERT DB2914.dbo.[ZIP] FROM 'C:\Documents and Settings\Jthep\My Documents\SQL Server Management Studio\Projects\S2914-HW3\ZIP_data.txt'WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

Is there a way to format the columns so I can actually set any column in the text file to any column in the table using Bulk Insert?You can insert into a temp table first and map as you wish...

Good Luck.|||Thanks, I figured I couldnt do it using bulk insert unless I created a temp. However, I'm now allowed to create the table with attributes of the same order as the data in the text file as many of my classmates were asking the professor about it. =D