Subquery with INSERT? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Subquery with INSERT?

Author

Topic

Rauken
Posting Yak Master

108 Posts

Posted - 2005-05-23 : 10:39:49

This might be trivial to you all. I've imported an Excel file into a table which is a copy of a table with all its field structure. Now I want to import the content in my temporary table into the main table and add three rows into a child table.

My thought is to do a big cursor and loop the content, insert and get the identity and insert the three rows into the child table.

Can I do a bulk insert with a subquery in some way?

/Rauken

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2005-05-23 : 11:20:08

Can you explain the question in more with sample inputs?

Work smarter not harder take control of your life be a super achiever

Rauken
Posting Yak Master

108 Posts

Posted - 2005-05-23 : 15:51:01

Ok let's say we have an invoice table and a invoice_items table containing the invoice_id key. I get an Excel file with 800 invoices that match the field structure of the invoice table except no invoice_id key which is autogenerated. For each invoice I import into the invoice table I want to add 3 items in the invoice_items table. All 800 invoices will be having the same 3 invoice_items, that will be 2400 rows in the invoice_item table.

My solution was to create a copy of the table structure of the invoice table excluding the invoice_id key and call it invoice_tmp then import the Excelfil into it. After that make a cursor of the 800 invoices in invoices_tmp, loop and insert each row, get the invoice_id with @@identity and then insert the three rows into invoice_item.

My question was if this can be made with a batch sql statement, for example using a sub query in any way.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-23 : 16:12:10

>>My question was if this can be made with a batch sql statement, for example using a sub query in any way.

Yes, there should be no need for cursors or loops.

If you give us some sample data and what result you are you looking for it will be much, much easier to help you.

- Jeff

Rauken
Posting Yak Master

108 Posts

Posted - 2005-05-24 : 07:24:14

Ok here are the three tables with very little sample data. For each invoice I import from INVOICE_TMP I want to add the three rows in INVOICE_ITEM but with new invoice_id's

INVOICE
invoice_id	customer_id
1212		3423
1213		4556

INVOICE_TMP
customer_id
3455
6556

INVOICE_ITEM
invoice_item_id	invoice_id	item_id
3232		1212		3
3233		1212		4
3234		1212		5

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-05-24 : 09:05:22

Do you have any way of figuring out which invoices were imported in the current batch? Perhaps by a datetime value or an external ID column? In that case you could simply do this:


INSERT INTO Invoice_Item (InvoiceID, ItemID) 
 SELECT InvoiceID, 3 FROM Invoice WHERE BatchID = @CurrentBatchID (just an example condition)

INSERT INTO Invoice_Item (InvoiceID, ItemID) 
 SELECT InvoiceID, 4 FROM Invoice WHERE BatchID = @CurrentBatchID 

INSERT INTO Invoice_Item (InvoiceID, ItemID) 
 SELECT InvoiceID, 5 FROM Invoice WHERE BatchID = @CurrentBatchID (just an example condition)

This should be the quickest way to do it, but you need to have a way of identifying the latest imported records.

OS

Rauken
Posting Yak Master

108 Posts

Posted - 2005-05-25 : 04:38:51

Yes finding out the last inserted invoice and getting its invoice_id it's the tricky part. I did the cursor solution anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources