case - when - update - insert into - end

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

case - when - update - insert into - end

Author

Topic

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-24 : 03:56:15

Hi guys,

My first question is, is it possible like this?

case
when a=b then
update table1
set columnA = convert(datetime,columnB,113)
from table2
where a=b
insert into table1 (columnames)
select (few columns, and few defined value)
from table2
where a=b
else
insert into table1 (columnames)
select (few columns)
from table2
where a=b
end

from there u can know im a newbie n know nothing bout sql :D

Thanks in advance guys!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-03-24 : 04:07:23

Case can't be used like that.
but you can use IF ...Else Logic

IF @A = @B
BEGIN
update table1
set columnA = convert(datetime,columnB,113)
from table2
where a=b
insert into table1 (columnames)
select (few columns, and few defined value)
from table2
where a=b
END
else
BEGIN
insert into table1 (columnames)
select (few columns)
from table2
where a=b
END

Duane.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-24 : 04:08:13

hi,
you cannot do like this..
You need to put an if statement

If <statement true>
Begin

update table1
set columnA = convert(datetime,columnB,113)
from table2
where a=b
insert into table1 (columnames)
select (few columns, and few defined value)
from table2
where a=b

END
ELSE
BEGIN

insert into table1 (columnames)
select (few columns)
from table2
where a=b

END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-24 : 04:09:30

Use IF ... ELSE instead. But the @a and @b should be a variable

if @a = @b
begin
    update table1
    set    columnA = convert(datetime,columnB,113)
    from   table2
    where  a = b

    insert into table1 (columnames)
    select (few columns, and few defined value)
    from    table2
    where   a = b
end
else
begin
    insert into table1 (columnames)
    select (few columns)
    from  table2
    where a = b
end

If the a and b in when a = b then refers to a column in a table, you should do this instead


-- update tabel1 if column a = b
update table1
set    columnA = convert(datetime,columnB,113)
from   table2
where  a = b

-- insert something to table1 if column a = b
insert into table1 (columnames)
select (few columns, and few defined value)
from   table2
where  a = b

-- insert something else when a not equal to b
insert into table1(columnnames)
select (few columns)
from   table2
where  a <> b

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-24 : 04:11:02

ohh..cant use case then...no wonder its not working..

because i thought case is faster than IF...ok..ill change my coding, see if it works or not..thanks guys!!!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-03-24 : 04:12:11

mrjack - read up on CASE in Books Online - it is quite handy within SELECT statements, but like illustrated here it can't be used for program flow logic.

Duane.

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-24 : 04:24:08

Thanks Duane,ill look it up.

OK..here my coding..is it going to work?

==start of code==
WHILE ((SELECT count(*) FROM tempdb.dbo.table1) > 0 )
begin

If db2.dbo.table1.column1 = tempdb.dbo.table1.column1 THEN
Begin

UPDATE db2.dbo.table1
SET db2.dbo.table1.column2 = convert(datetime,(left(tempdb.dbo.table1.column2,8)+' '+substring(tempdb.dbo.table1.column2,9,2)+':'+substring(tempdb.dbo.table1.column2,11,2)+':'+substring(tempdb.dbo.table1.column2,13,2)),113) <------ P/S: tempdb.dbo.table1.column2 = '20060304112015' going to convert into 2006/03/04 11:20:15
FROM db2.dbo.table1,tempdb.dbo.table1
WHERE db2.dbo.table1.column1 = tempdb.dbo.table1.column1

INSERT INTO db2.dbo.table1(column names)
SELECT (over here few items get from the tempdb.dbo.table1 but few is self define like 'YES' , '2006/05/05 11:12:27') <---but i not sure how to do that ;/
FROM tempdb.dbo.table1
WHERE db2.dbo.table1.column1 = tempdb.dbo.table1.column1

END
ELSE
BEGIN

INSERT INTO db2.dbo.table1(column names)
SELECT (over here few items get from the tempdb.dbo.table1 but few is self define like 'YES' , '2006/05/05 11:12:27') <---but i not sure how to do that ;/
FROM tempdb.dbo.table1
WHERE db2.dbo.table1.column1 = tempdb.dbo.table1.column1

END
END
==End of Code==

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-24 : 04:28:22

"WHILE ((SELECT count(*) FROM tempdb.dbo.table1) > 0 )"
This will always be true right ? So you will get into infinite loop.

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-24 : 04:38:05

ohh..ya ohh..hmm i need to check all row in the tempdb.dbo.table1...hw to do it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-24 : 04:43:15

quote:
Originally posted by mrjack

ohh..ya ohh..hmm i need to check all row in the tempdb.dbo.table1...hw to do it?

Do you need to process all rows or do you need to check for existance of any rows in table1 ?

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-24 : 04:53:23

i need to process all rows in tempdb.dbo.table1...and if it exist in db2.dbo.table1..i will need to update db2.dbo.table1..

can get me ka?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-24 : 05:09:44

Yup. I understand what you need. Hope you can understand what i am doing here
--==start of code==[CODE]
~~WHILE ((SELECT count(*) FROM tempdb.dbo.table1) > 0 )~~
-- Removed. Not required.
begin
~~If db2.dbo.table1.column1 = tempdb.dbo.table1.column1 THEN~~
-- you can't use do this year. Just add this to the where clause which you already have done it
~~Begin~~

UPDATE db2.dbo.table1
SET db2.dbo.table1.column2 = convert(datetime,(left(tempdb.dbo.table1.column2,8)+' '+substring(tempdb.dbo.table1.column2,9,2)+':'+substring(tempdb.dbo.table1.column2,11,2)+':'+substring(tempdb.dbo.table1.column2,13,2)),113) <------ P/S: tempdb.dbo.table1.column2 = '20060304112015' going to convert into 2006/03/04 11:20:15
FROM db2.dbo.table1,tempdb.dbo.table1
WHERE db2.dbo.table1.column1 = tempdb.dbo.table1.column1

--(over here few items get from the tempdb.dbo.table1 but few is self define like 'YES' , '2006/05/05 11:12:27') <---but i not sure how to do that ;/
INSERT INTO db2.dbo.table1(column names)
SELECT col1, col2, col3, 'YES', 'NO', 1234, col4 -- example
FROM tempdb.dbo.table1
WHERE db2.dbo.table1.column1 = tempdb.dbo.table1.column1 -- Yes. Here you check for it.

~~END~~
~~ELSE~~ -- removed
~~BEGIN~~
-- So this code should be executed where db2.dbo.table1.column1 not equal to tempdb.dbo.table1.column1 right ?
INSERT INTO db2.dbo.table1(column names)
SELECT (over here few items get from the tempdb.dbo.table1 but few is self define like 'YES' , '2006/05/05 11:12:27') <---but i not sure how to do that ;/
FROM tempdb.dbo.table1
~~WHERE db2.dbo.table1.column1 = tempdb.dbo.table1.column1~~
WHERE db2.dbo.table1.column1 <> tempdb.dbo.table1.column1

~~END~~
END
--==End of Code==[/CODE]

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-24 : 05:27:58

ohh..i need to explain few things...and why we need the if-then-else

db1.table1
===========
members
non members

db2.table1
===========
members

so i need to check in db1 if member exist, then i need to update column in db1 plus need insert new row in db1 few info in db2..

in the ELSE statement..if cant find the member..then add the member into db1 from db2 wif few info..

sorry for the confusion..i also confuse..

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-24 : 19:15:53

ohh..now i can understand.. sorry..hehe

one more thing to add...table2 have 11mil rows and table1 5.9mils rows...beside using rowcount..is there anything else i can do to optimize the performace? cos sql server seems like hang/lock after few times..

thx mates.. :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources