inserting rows only if some key dosent exist

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

SQL Server Development (2000)

inserting rows only if some key dosent exist

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-03-09 : 14:35:31

i have a table
col1 col2
i want to enter a pair only once.
can i do this in 1 query like :
"insert into table1
select val1,val2 from table1 where (if values dosent exist)"
i hope i am cear
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-09 : 14:43:11

I don't understand what you want. Could you provide a data example?

Tara Kizer
aka tduggan

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-03-09 : 14:56:33

for example
col1 col2
3 9
4 4

and i want to add the pair (4,6) if it dosnet exist (like in this example)
but the pari (3,9) icant beacuse its already there
i hope i more clear now
thnajks in adbvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-09 : 14:59:31

Is the pair 9,3 considered a duplicate?

Tara Kizer
aka tduggan

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-03-09 : 15:00:09

nope its not duplicate beacuse that values are in opposite columns

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-09 : 15:11:33

DECLARE @var1 int, @var2 int, @var3 int
SELECT @var1 = 3, @var2 = 9

SELECT @var3 = Column2
FROM Table1
WHERE Column1 = @var1

IF @@ROWCOUNT <> 0 AND @var3 <> @var2
insert into table1
values (@var1, @var2)

Tara Kizer
aka tduggan

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-03-09 : 15:15:22

i didnt wantto use a SP just a simple insert select code

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-09 : 15:19:12

That isn't a stored procedure.

Tara Kizer
aka tduggan

X002548
Not Just a Number

15586 Posts

Posted - 2006-03-09 : 15:20:58

Tara, you been drinking?


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 int)
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 3,9 UNION ALL SELECT 4,4
GO

SELECT * FROM myTable99

DECLARE @col1 int, @col2 int
SELECT @Col1 = 4, @Col2 = 6

IF NOT EXISTS(SELECT * FROM myTable99 WHERE Col1 = @Col1 AND Col2 = @Col2)
	INSERT INTO myTable99(Col1, Col2)
	SELECT @Col1, @Col2

SELECT * FROM myTable99

SELECT @Col1 = 3, @Col2 = 9

IF NOT EXISTS(SELECT * FROM myTable99 WHERE Col1 = @Col1 AND Col2 = @Col2)
	INSERT INTO myTable99(Col1, Col2)
	SELECT @Col1, @Col2

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-03-09 : 15:21:37

can put this in asp cose as 1 sql sentence?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-09 : 15:25:42

quote:

Tara, you been drinking?

I was just making a point about it requiring more than just an INSERT statement.

peleg,
I'm not sure if you can do the IF NOT EXISTS plus the INSERT as one statement in your ASP.NET code. It seems to me that Brett's code would need to be wrapped into a stored procedure for use in your application. So if you don't want to use a stored procedure, you'll need to check for the existence of the row using a SELECT, then perform the INSERT if it wasn't found. So you'll need two statements.

Tara Kizer
aka tduggan

X002548
Not Just a Number

15586 Posts

Posted - 2006-03-09 : 15:51:26

But you bother at all? Just handle the error if it throws a dup key error message.

In your model you perform 2 operations everytime.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-09 : 16:19:44

This should do it.


insert into Table1
	(
	COL1,
	COL2
	)
select
	a.COL1,
	a.COL2
from
	(
	select
		-- Values to be inserted
		COL1 = 9,
		COL2 = 3
	) a
	left join
	Table1 b
	on	a.COL1 = b.COL1 and
		a.COL2 = b.COL2
where
	-- Verify there is no matching row
	b.COL1 is null and
	b.COL2 is null

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-10 : 01:11:18

quote:
Originally posted by pelegk2

can put this in asp cose as 1 sql sentence?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Why do you need one sql sentence?
If you use not exists then it is more clear to you

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-03-12 : 02:24:41

thnaks alot Michael Valentine Jones
your code solved it!

tkizer : i understand what u said but i wanted to do it in 1 step!
withought the need to make select and by the result an insert

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-03-13 : 09:12:52

[code]IF NOT EXISTS(SELECT * FROM table WHERE Col1 = @Col1 AND Col2 = @Col2)
INSERT INTO table (Col1, Col2) SELECT @Col1, @Col2[/code]This will work great, done it a million times...Michaels code works great also but is a lot less readable if you ask me. No offence!

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-13 : 15:08:59

quote:
Originally posted by Lumbago

IF NOT EXISTS(SELECT * FROM table WHERE Col1 = @Col1 AND Col2 = @Col2)
INSERT INTO table (Col1, Col2) SELECT @Col1, @Col2
This will work great, done it a million times...Michaels code works great also but is a lot less readable if you ask me. No offence!

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

You have to understand that these two pieces of code actually work differently.

The way I coded it is the way to do it if you are inserting multiple rows, either with a union or from a temp or permanent table. That way, the rows that are not in the table get inserted, and the ones that are not there get left out. Of course, it also works with a single row.

If you use the method with not exists, none of the rows from a multi-row insert would get loaded, unless you use a cursor or loop to do one insert at a time.

CODO ERGO SUM

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-03-13 : 17:07:38

Hm, good point Michael! I didn't realise this until now but the code works, also in an asp.net app. Just wanted to make that point...yours will probably perform better also wouldn't it? hm

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-13 : 17:23:29

quote:
Originally posted by Lumbago

Hm, good point Michael! I didn't realise this until now but the code works, also in an asp.net app. Just wanted to make that point...yours will probably perform better also wouldn't it? hm

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

It hard to say which will perform better, but the query plan will definitely not be the same, so there could be differences. With inserts of small numbers of rows where you are joining on an index, I doubt that you would notice much difference. If you are inserting large numbers of rows you would really want to test it.

You can run this code to take a look at the difference in query plans for a similar insert.


drop table #t
go
create table #t ( num int not null primary key clustered )
go
-- Load some numbers in the table.
insert into #t
select number*2
-- Function available in script library forum
from dbo.F_TABLE_NUMBER_RANGE(1,1000)

GO
insert into #t
	(
	NUM
	)
select
	a.NUM
from
	(
	-- Values to be inserted
	select	NUM = 2
	) a
	left join
	#t b
	on	a.NUM = b.NUM
where
	-- Verify there is no matching row
	b.NUM is null
GO
IF NOT EXISTS( SELECT * FROM #t WHERE NUM = 2)
	INSERT INTO #t (NUM) SELECT NUM = 2

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources