Insert in two tables with foreign key

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

General SQL Server Forums

New to SQL Server Programming

Insert in two tables with foreign key

Author

Topic

ujjawaljha
Starting Member

3 Posts

Posted - 2010-09-15 : 02:13:00

I have two tables say TableA(col1A int ,col2A int) and TableB(col1B int,col2B int).
col1A is Primary Key and col1B is foreign key for the same.
col2B is primay key and col1B is foreign key for the same.
How to insert the records in these tables in single quary ?

Sachin.Nand

2937 Posts

Posted - 2010-09-15 : 02:40:29

It's not possible to insert data in both the tables with a single insert statement.You can create an Insert trigger on TableA that will insert related data into TableB.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

ujjawaljha
Starting Member

3 Posts

Posted - 2010-09-15 : 02:45:35

I agree that we need to create trigger for that.
I tried but still not working. Its giving error like
The INSERT statement conflicted with the FOREIGN KEY constraint The statement has been terminated.

Sachin.Nand

2937 Posts

Posted - 2010-09-15 : 03:27:48

Can you post what you have used?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

ujjawaljha
Starting Member

3 Posts

Posted - 2010-09-15 : 04:01:03

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER trigger [dbo].[trg] on [dbo].[emp] Instead of INSERT as
begin

insert into age
select * from inserted

end

NeilG
Aged Yak Warrior

530 Posts

Posted - 2010-09-15 : 04:05:10

It sounds to me that you breaking a table constraint, are you adding the value to the primary table before then trying to add the value to the table with the foreign key?

Sachin.Nand

2937 Posts

Posted - 2010-09-15 : 04:33:07

quote:
Originally posted by ujjawaljha

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER trigger [dbo].[trg] on [dbo].[emp] Instead of INSERT as
begin

insert into age
select * from inserted

end

You are using an Instead of trigger.
So your trigger basically is trying to insert the data into age with no related id in the emp table & since you a refrential constraint with emp table it is causing an error.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources