Bulk inserting into table(s)

Question

Post reply

Bulk inserting into table(s)

jussi

Valued Member

Points: 60
More actions
February 27, 2003 at 4:18 am

#64108

Hello everyone,
My problem is this. I am trying to do an xml bulk insert that inserts rows into two tables. The trick here is that the first tables primary key is generated automatically. I want to, and have to, use this identity key as a foreign key in the second table. Is there any way to do this?
Example:
I have the tables
Rows{@rowid int; somefield int}
Rowdata{@dataid int; @rowid int; datafield int}
Where rowid and dataid are identity fields.
Now I'd like to bulk insert the following data:
<row>
<somefield>1</somefield>
<rowdata>
<datafield>123</datafield>
<datafield>231</datafield>
<datafield>312</datafield>
</rowdata>
</row>
Is there a way to do this using the xml bulk insert functionality of sqlxml?
Thanks,
Jussi

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Try this article

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

Thanks

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

jussi Valued Member Points: 60 More actions · Answer 2

Thanks, Phill!

That article was indeed interesting, but it didn't quite solve my problem. What I'm effectively trying to do is to with one insert statement insert data into two tables one of which has a foreign key that is an automatically generated key from the first table and thus not known at the time I make my sql statement. I am not certain if this is possible at all, at least through a view it cannot be done; a view is not updatable if the update affects more than one of the base tables.

The article above illustrates a mechanism to feed xml data to a stored procedure (I'm absolutely certain I will thank you for this in the future ;)), but the inserts in it only worked on one table.

I would like to, with one sql statement,

1) insert a row into the Rows table - here the rowid is automatically generated

2) insert corresponding rows into the Rowdata table, using the automatically generated rowid from (1) as a foreign key

I know I *could* do this with a cursor, inserting one row at a time, but this is cumbersome and not very efficient, so I would prefer not to do it that way.

Thank you,

- Jussi

philcart SSC-Forever Points: 47794 More actions · Answer 3

What is the structure of the data like? How are the two tables related?

Thanks

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

jussi Valued Member Points: 60 More actions · Answer 4

Heh, seems I've been unable to describe the structure without actually writing it out :-). Here are the create scripts for the tables (the actual ones have a bit more data, but that is irrelevant):



CREATE TABLE [Line] (

[LineID] [int] IDENTITY (1, 1) NOT NULL ,

[MessageID] [int] NOT NULL,

[Position] [int] NOT NULL,

CONSTRAINT [PK_Line] PRIMARY KEY  NONCLUSTERED 

(

[LineID]

) WITH  FILLFACTOR = 90  ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [LineItem] (

[LineItemID] [int] IDENTITY (1, 1) NOT NULL ,

[LineID] [int] NOT NULL ,

[Position] [int] NOT NULL ,

[DataID] [int] NOT NULL

CONSTRAINT [PK_LineItem] PRIMARY KEY  NONCLUSTERED 

(

[LineItemID]

) WITH  FILLFACTOR = 90  ON [PRIMARY] ,

CONSTRAINT [FK_LineItem_Line] FOREIGN KEY 

(

[LineID]

) REFERENCES [Line] (

[LineID]

) NOT FOR REPLICATION 

) ON [PRIMARY]

GO

alter table [dbo].[LineItem] nocheck constraint [FK_LineItem_Line]

GO

Now I already implemented the solution you suggested before (using the article). Using a cursor to loop through the lines and inserting corresponding lineitems wasn't as expensive as I feared, so it is a servicable solution. Thank you for teaching me something new :). However, doing just a single insert for the line, or even a larger set of lines would of course be the optimal case, ie. I'd like to be able to insert the following in one go:



<line messageid="1" position="1">

 <lineitem position="1" dataid="1"/>

 <lineitem position="1" dataid="2"/>

</line>

<line messageid="1" position="2">

 <lineitem position="2" dataid="1"/>

 <lineitem position="2" dataid="2"/>

</line>

Where the position attribute does not reflect the lineid created. Like I said, I already solved the problem, so the rest is just academic interest. Thank you for the help so far, it has been invaluable!

- Jussi

Edited by - jussi on 02/28/2003 07:03:31 AM

Edited by - jussi on 02/28/2003 07:04:37 AM

tymberwyld SSCertifiable Points: 7810 More actions · Answer 5

My first impression is...you can't do this without some manual programming effort. I develop very large scale Synchronization programs that transfer data from one SQL Server to another. Personally, from experience, Bulk Inserts have to have ALL the data pre-defined. There is no way to figure out the RecordID while inserting and use it for another Foreign Table. A Bulk insert to me means that "this is the first time I am inserting data, so don't do any validation, just stuff it in there."

I think what you are looking for is maybe a Synchronization program. Your best bet is to go with something like VB.NET because it's XML handling is the best! You should also download the SQLXML Libraries for SQL Server if you go that route.

Now...if anyone knows how to BULK EXPORT data, I am all ears. I don't care about Table Relationships, I just need all the Data Exported to XML as fast as possible!

RyanRandall SSChampion Points: 13623 More actions · Answer 6

You can probably do something with a view and an instead of trigger. Something like this (on top of your xml parsing stuff)?...

 

create view vw_lineitem as

select a.*, b.LineItemID, item_position = b.Position, b.DataID from line a inner join lineitem b on b.lineid = a.lineid

 

create trigger trig_vw_lineitem on vw_lineitem

INSTEAD OF INSERT

AS

BEGIN

  INSERT INTO line select MessageID, Position FROM inserted

  insert into LineItem select a.LineID, item_position, DataID from line a inner join inserted c on c.MessageID = a.MessageID and c.Position = a.Position

END

Then...

 

insert into vw_lineitem

select -1, 100, 100, -1, 100, 100

union

select -1, 200, 200, -1, 200, 200

Gives...



LineID      MessageID   Position    

----------- ----------- ----------- 

1           100         100

2           200         200

(2 row(s) affected)

LineItemID  LineID      Position    DataID      

----------- ----------- ----------- ----------- 

1           1           100         100

2           2           200         200

(2 row(s) affected)

I think this would require the MessageID and Position columns of Line to be unique, but you can probably adapt it if that's not a constraint.

Let me know if it's of any use.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

gljjr SSChampion Points: 10080 More actions · Answer 7

OK, If I read what you want correctly I think the following will give you what you want.

DECLARE @xmldoc nvarchar(4000)

, @idoc int

SELECT @xmldoc = '<data>

</line>

</line>

</line>

</line>

</data>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

SELECT * FROM OpenXML(@idoc, '/data/line/lineitem',0)

WITH(intMessageID int '../@messageid'

, intPosition int '../@position'

, intItemDataID int '@dataid'

, intItemPosition int '@position'

)

-- remove the XML document from memory

EXEC sp_xml_removedocument @idoc

Returns

intMessageID intPosition intItemDataID intItemPosition

------------ ----------- ------------- ---------------

1 2 1 2

1 2 2 2

1 1 1 1

1 1 2 1

2 1 1 1

2 1 2 1

2 2 1 2

2 2 2 2

Now in order to do this I had to add a "Root" node. But I think you get the idea. Once you have this you can insert the data into a temp table and then insert your Line data into its table and then join it to the temp table to insert the lineitem data. I'm also asuming that you have a unique index on your messageid field.

Gary Johnson

DBA

Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.