Translator please - SQL Server Forums

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

Transact-SQL (2000)

Translator please

Author

Topic

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-10 : 15:47:18

I need that in t-sql
insert into table values (a,b..) when a and b doesn't exist in the table.

Thanx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 15:49:11

if not exists (select * from table where ColA = a and ColB = b)
begin
.... your insert here ...
end

Go with the flow & have fun! Else fight the flow

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-10 : 15:50:43

Or

Declare @var variant

@var = select * from db where conditiona and conditionb
if @var = null
then
insert into.......
else
update............

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-10 : 15:51:29

Thank you.
Fast reply.
Iwas wrinting while you posted!
Thanx.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 15:54:48

We're trying to get the point where we reply BEFORE the question is actually posted.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 16:02:49

yeah TG is right. my well known ESP server is usually up and running for that kind of stuff...

Go with the flow & have fun! Else fight the flow

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-10 : 16:05:20

Here is the result (sql analyser):

IF NOT EXIST (SELECT * FROM Presence_BCV_App WHERE [Date] ='2005-02-23' and Carte = '12:30269') BEGIN INSERT INTO Presence_BCV_App ([Date], Carte, Temps_arrondi) VALUES ('2005-02-23','12:30269','08:00:00') END ELSE BEGIN UPDATE Presence_BCV_App SET Temps_arrondi = '08:00:00' WHERE Carte = '12:30269' and [Date] = '2005-02-23' End

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ELSE'.

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-10 : 16:08:22

Ok I think i got it!

IF (SELECT * FROM Presence_BCV_App WHERE [Date] ='2005-02-23' and Carte = '12:30269') = null) BEGIN INSERT INTO Presence_BCV_App ([Date], Carte, Temps_arrondi) VALUES ('2005-02-23','12:30269','08:00:00') END ELSE BEGIN UPDATE Presence_BCV_App SET Temps_arrondi = '08:00:00' WHERE Carte = '12:30269' and [Date] = '2005-02-23' End

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-10 : 16:10:32

No. not yet!!

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-10 : 16:13:06

Nevermind!! forgot the "S" to exist!
Seventh reply
Shame on me
See ya nice community!

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-10 : 16:17:41

Perhaps


IF NOT EXISTS ( SELECT * 
       		  FROM Presence_BCV_App 
      		 WHERE DATEDIFF(dd,[Date],'2005-02-23')=0 
		   AND Carte = '12:30269')
	INSERT INTO Presence_BCV_App ([Date], Carte, Temps_arrondi) 
 	     SELECT '2005-02-23','12:30269','08:00:00'
  ELSE 
	     UPDATE Presence_BCV_App 
	        SET Temps_arrondi = '08:00:00' 
	      WHERE Carte = '12:30269' and [Date] = '2005-02-23'

Brett

8-)

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-10 : 16:21:11

Oh thanx.
Isaid Iforgot the "S" but still bugging but i'm on the way!
I'll use your sentences.

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-10 : 16:23:53

Brett you got it right.
Thank you very much!

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-10 : 16:26:55

Well, if you're always going to the expense of running the WHERE clause in the IF EXISTS ...

 
UPDATE Presence_BCV_App 
SET Temps_arrondi = '08:00:00' 
WHERE Carte = '12:30269' AND [Date] = '2005-02-23'

IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO Presence_BCV_App ([Date], Carte, Temps_arrondi) 
  VALUES ('2005-02-23','12:30269','08:00:00')
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 16:29:24

not bad.... never thought of doing it this way

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-10 : 16:33:40

Yup..that would potentially save 1 access to the database depending on the percentage of inserts as compared to updates...

If you're doing a lot of updates then that's really the way to go...

Matter of fact, I'd dare say, the only way to go

Brett

8-)

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-10 : 17:17:00

yeah, pretty good!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources