query to insert data into two table from same page

Question

Post reply

query to insert data into two table from same page

rajeevgupta40

Old Hand

Points: 372
More actions
June 10, 2010 at 12:36 am

#143579

i have two table .one table has a column as primary key and second table has that column as foreign key .i want to insert data into these two table from same page.
plz help me.
thanx

Viewing 5 posts - 1 through 4 (of 4 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

First of all very less descripttion provided. Anyways use

DML insert trigger

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

You can use OUTPUT clause.

INSERT Table1 (Col1, Col2, ... Coln)

OUTPUT INSERTED.Col1, [anything else]

INTO Table2

VALUES (Val1, Val2, ... Valn)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

OUTPUT clause, as stated above, is the best approach, especially because it allows for multiple rows. But if you're only ever dealing with a single row, you can capture the generated id using SCOPE_IDENTITY() and then use that value in the insert of the child table.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

shield_21 Ten Centuries Points: 1033 More actions · Answer 4

Hi there,

I'll just add something.. 🙂

When there's primary and foreign key relationship between two tables, output clause would yield an error.. Sample code below:

USE tempdb

GO

CREATE TABLE table1 (

id INT IDENTITY(10,10) PRIMARY KEY,

col1 VARCHAR(10)

)

CREATE TABLE table2(

id INT IDENTITY PRIMARY KEY,

logdate DATETIME,

fk INT CONSTRAINT fk_table2 FOREIGN KEY REFERENCES table1(id)

)

INSERT INTO table1

OUTPUT GETUTCDATE(),INSERTED.id INTO table2

SELECT 'a' UNION ALL

SELECT 'b' UNION ALL

SELECT 'c' UNION ALL

SELECT 'd'

The target table 'table2' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'fk_table2'.

The trick here is to use sp_executesql or dynamic SQL(which is not recommended).. Thanks to article by Itzik Ben-Gan!:-D

INSERT INTO table2

EXEC sp_executesql N'INSERT INTO table1

OUTPUT GETUTCDATE(), INSERTED.id

SELECT ''a'' UNION ALL

SELECT ''b'' UNION ALL

SELECT ''c'' UNION ALL

SELECT ''d'''

SELECT * FROM table1

SELECT * FROM table2

DROP TABLE table2, table1

Cheers,

shield_21