Insert query with a select subquery

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 query with a select subquery

Author

Topic

siorli
Starting Member

2 Posts

Posted - 2008-04-01 : 09:26:41

Hi.
I have an insert query which inserts record that are rturned from a select subquery:

INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...

col1 and col2 in tbl1 combined ,are a unique index.

So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.

The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).
How can I solve this ?

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-01 : 09:37:22

What is Column3 in tbl1? and what is your actual requirement?

Do you want to insert only the records which satisfies your unique key constraint?

If so, you need to make sure that the "Select" selects only unique data from tbl2.

Prakash.P
The secret to creativity is knowing how to hide your sources!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-01 : 09:47:08

You might have to use a INSTEAD OF INSERT trigger to check values prior to insertion & save violation records to some other table.

siorli
Starting Member

2 Posts

Posted - 2008-04-01 : 10:13:04

quote:
Originally posted by visakh16

You might have to use a INSTEAD OF INSERT trigger to check values prior to insertion & save violation records to some other table.

Thanks for the reply.
Can you please elaborate what you mean by INSTEAD OF INSERT trigger ?
My purpose is to insert all the records that maintain the constraint, and the records that violate it are not important.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-01 : 10:24:24

quote:
Originally posted by siorli

quote:
Originally posted by visakh16

You might have to use a INSTEAD OF INSERT trigger to check values prior to insertion & save violation records to some other table.

Thanks for the reply.
Can you please elaborate what you mean by INSTEAD OF INSERT trigger ?
My purpose is to insert all the records that maintain the constraint, and the records that violate it are not important.

INSTEAD OF TRIGGER if present will fire when you're trying to perform an INSERT action. Inside the trigger you can check for values you're going to insert and perform INSERT only if it is not violating the constraint.

something like

CREATE TRIGGER YourTriggger
ON YourTable
INSTEAD OF INSERT
AS
BEGIN
IF NOT EXISTS (SELECT * FROM YourTable t
               INNER JOIN INSERTED i
               ON i.Col=t.Val)
BEGIN
INSERT INTO YourTable
SELECT * FROM INSERTED
END
GO

this will check if the value of Col to be inserted is already present in table and if not there it will insert and so wont violate the unique constraint on Col.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-01 : 10:40:40

Why create a trigger?
set nocount on
declare @tbl1 table (col1 int, col2 int, col3 int, primary key (col1, col2) )
declare @tbl2 table (col1 int, col2 int, col3 int, primary key (col1, col2))
insert into @tbl1 select 1,1,1
insert into @tbl2 select 1,1,1 union select 2,2,2 union select 3,3,3

-- select * from @tbl1
-- select * from @tbl2

-- -- Causes error
-- INSERT into @tbl1 (col1,col2,col3) SELECT col1,col2,col3 FROM @tbl2
-- -- Server: Msg 2627, Level 14, State 1, Line 12
-- -- Violation of PRIMARY KEY constraint 'PK__@tbl1__44F46D52'. Cannot insert duplicate key in object '#44004919'.
-- -- The statement has been terminated.

-- Works
INSERT into @tbl1 (col1,col2,col3)
SELECT a.col1,a.col2,a.col3
FROM @tbl2 a left join @tbl1 b
on a.col1=b.col1 and a.col2=b.col2
where b.col1 is null

select * from @tbl1

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-01 : 10:49:06

quote:
Originally posted by jhocutt

Why create a trigger?
set nocount on
declare @tbl1 table (col1 int, col2 int, col3 int, primary key (col1, col2) )
declare @tbl2 table (col1 int, col2 int, col3 int, primary key (col1, col2))
insert into @tbl1 select 1,1,1
insert into @tbl2 select 1,1,1 union select 2,2,2 union select 3,3,3

-- select * from @tbl1
-- select * from @tbl2

-- -- Causes error
-- INSERT into @tbl1 (col1,col2,col3) SELECT col1,col2,col3 FROM @tbl2
-- -- Server: Msg 2627, Level 14, State 1, Line 12
-- -- Violation of PRIMARY KEY constraint 'PK__@tbl1__44F46D52'. Cannot insert duplicate key in object '#44004919'.
-- -- The statement has been terminated.

-- Works
INSERT into @tbl1 (col1,col2,col3)
SELECT a.col1,a.col2,a.col3
FROM @tbl2 a left join @tbl1 b
on a.col1=b.col1 and a.col2=b.col2
where b.col1 is null

select * from @tbl1

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Yup. Thats true James. This will do the job and is better than trigger approach. But somehow the trigger idea came to my mind quickly. Thats why i suggested it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources