Simultaneous Entry in both master & detail

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

Transact-SQL (2005)

Simultaneous Entry in both master & detail

Author

Topic

ashok_gos
Starting Member

2 Posts

Posted - 2008-04-07 : 02:44:16

Hi, can anyone help me? I want to know whether is it possible to insert records simultaneously in both master & detail tables?
For elaboration, say there is a master table contains (orderid,orderdate,amount) and details table contains (orderid, productid,qty,price). 1 record of master table associated with n records of details table. Can it be possible to insert both the 1 record at master table with n records in details table in a single sql statement?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-07 : 03:50:26

[code]-- Prepare sample data
DECLARE @Master TABLE (MasterID INT IDENTITY(1, 1), Name VARCHAR(20))

DECLARE @Slave TABLE (SlaveID INT IDENTITY(1, 1), MasterID INT, Name VARCHAR(20))

-- Insert first dummy record
INSERT @Master
(
Name
)
SELECT 'Master 1'

-- Insert second dummy record
INSERT @Master
(
Name
)
OUTPUT inserted.MasterID,
'Slave 1'
INTO @Slave
SELECT 'Master 2'

-- Show results
SELECT *
FROM @Master

SELECT *
FROM @Slave[/code]

E 12°55'05.25"
N 56°04'39.16"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2008-04-07 : 07:13:18

quote:
Originally posted by ashok_gos

Hi, can anyone help me? I want to know whether is it possible to insert records simultaneously in both master & detail tables?
For elaboration, say there is a master table contains (orderid,orderdate,amount) and details table contains (orderid, productid,qty,price). 1 record of master table associated with n records of details table. Can it be possible to insert both the 1 record at master table with n records in details table in a single sql statement?

Inserting records in multiple table with a single sql statement is not possible in MS SQL Server. This feature is in Oracle9i.

Thanks,

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-07 : 07:40:35

I think I proved you wrong 3 hours, 22 minutes and 52 seconds earlier.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources