| Author |
Topic |
|
00kevin
Yak Posting Veteran
78 Posts |
 Posted - 2009-10-02 : 13:19:05
|
| Hi,Does anyone have any interesting ways to insert header and detail records(1 to N relationship) into a database when the keys are set to auto increment?For example. Header -->HeaderId (auto) PKDetail-->DetailId (auto) PK-->HeaderId (FK)Usually I would simply insert the detail records and then update them after I insert all the header records. Is there another more efficient way to do this? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-02 : 13:23:26
|
you can do likedeclare @headerid int INSERT into header (header) values (@header) select @headerid=scope_identity() insert into detail (headerid,..) values(@headerid,..) |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-02 : 13:27:48
|
| if you want to pass lots of detail records then pass them as xml or comma seperated values and then use string parsing or xml parsing to save |
|
|
|
00kevin
Yak Posting Veteran
78 Posts |
Posted - 2009-10-02 : 14:00:50
|
| well I have many header and detail records to insert from another table. I would rather not use a cursor and iterate through each record (as per your first suggestion. Is it possible to insert records into a view? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-02 : 14:02:56
|
| nope. view doesnt have physical existence. its just a virtual table. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-02 : 14:03:22
|
| you can insert from another table to your table using insert...select |
|
|
|
00kevin
Yak Posting Veteran
78 Posts |
Posted - 2009-10-02 : 14:41:09
|
| With that method, I would have to insert one header record, grab the new id, and then insert all the associated detail records. The problem with this method is that I would have to iterate through each header record and then insert each associated detail record. Does that mean I would have to use a cursor? Is there not another way to perform this kind of operation with one clean statement? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-02 : 14:52:21
|
quote:
Originally posted by visakh16
nope. view doesnt have physical existence. its just a virtual table.
Of course you can insert into a view!
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-02 : 14:56:06
|
quote:
Originally posted by 00kevin
With that method, I would have to insert one header record, grab the new id, and then insert all the associated detail records. The problem with this method is that I would have to iterate through each header record and then insert each associated detail record. Does that mean I would have to use a cursor? Is there not another way to perform this kind of operation with one clean statement?
You can't insert in two tables in ONE Statement.In your first post in this thread you have given the answer on how to do it.Two inserts and one update after them is the right way.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-10-02 : 15:47:55
|
| I think we need more detail. It sounds like you have a table with information and you want to take the data from that table and insert it into a Header table and Detail table. Presumably there is some sort of Key on that Source table like a HeaderID..?If so it's pretty simple, you insert the data you want into the Header table so you get a new PK, but you also store the Source Key. Then you simply join the Header to the Source on the SourceKey and insert that into the Detail table and the newly created HeaderID will be there to insert with all the detail data from the Source table. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-10-02 : 15:50:34
|
quote:
Originally posted by webfred[br}You can't insert in two tables in ONE Statement.
Not in 2005, but in 2008 you can...[url]http://weblogs.sqlteam.com/peterl/archive/2009/07/29/How-to-insert-into-two-tables-in-one-statement.aspx[/url] |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-02 : 15:57:52
|
Thanks for that!
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
inserting header and detail records