Inserting master detail table records in a single query in sql server 2008

Question

Friends,

I have to insert 1200 records in the master detail table directly.

i have written 2 separate queries to insert records in the master and detail table.

insert into student_receipt
(pre_vocdt,pre_cbcode,pre_customer,pre_amount,pre_narrat,pre_astatus)
values
('20141014','1001','14TAP001',20000,'Tuition Fees','S')

in the above student_receipt table..pre_vocno is autoincrement...

i want to insert that value in the below table (student_receipt_detail) and also i want to make it as a single query...

insert into student_receipt_detail
(prd_vocno,prd_sno,prd_billno,prd_glcode,prd_amount)
values
(101,'1','1','3010101',20000)

the static values in master table is pre_vocdt,pre_cbcode,pre_amount,pre_narrat and pre_astatus

the static values in detail table is prd_sno,prd_billno,prd_glcode,prd_amount

the prd_vocno should have the same value as the pre_vocno of master table (autoincrement field)

thanks

Answer 1

friends,

otherwise , can i insert the records in the master table first and then in the detail table?

is there any error will occur?

i will insert in the master table first then i will copy all the vocno from the master table then i will insert the detail table using the master table's vocno..will it work?

thanks

Answer 2

Hi sathyguy1,

We cannot use a single query to insert into two tables at the same time. But we can do that in one transaction.

As I have tested in my local environment, the sample query below is for your reference:

BEGIN TRANSACTION;
DECLARE @vocno [int];
INSERT INTO student_receipt (pre_vocdt,pre_cbcode,pre_customer,pre_amount,pre_narrat,pre_astatus) VALUES ('20141014','1001','14TAP001',20000,'Tuition Fees','S');
SELECT @vocno  = SCOPE_IDENTITY();
INSERT INTO student_receipt_detail (prd_vocno,prd_sno,prd_billno,prd_glcode,prd_amount) VALUES (@vocno,'1','1','3010101',20000);
COMMIT TRANSACTION;

If you have any question, please feel free to let me know.

Regards,
Donghui Li

Answer 3

You can achieve by below steps-

1. Pass all 1200 records in SP as tvp parameter.

2. Insert records in Master table. (This will insert 1200 records in master table by using tvp)

3. Insert records in Details table. (Join tvp with Master table, this will give you all fields of tvp as well as primary key generated in master table). This will insert 1200 records in Details table with PK generated in Master table.

---

dharampal sikhwal

Answer 4

use output clause for capturing ids and use it to populate child table

http://visakhm.blogspot.in/2010/04/using-xml-to-batch-load-master-child.html

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs