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
 Using IF's within an UPDATE

Author  Topic 

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-07 : 04:56:39
Hi all, first post so be nice :)

I'm trying to insert values into a table (from a linked server - a .XLS file). One of the fields on the .XLS file is a text field which contains values: Y or N.

I am trying to insert these into a bit type coumn in a table. Is there anyway i can convert these to 1 or 0? the code i had though was along the lines of:


INSERT INTO EMPLOYEE (
EMPLOY_REF,
EMPLE_NOT_MN)
SELECT
CAST(EMPLOY_REF AS varchar(10)),
if (select EMPLE_NOT_MN from mockdownload_dd...[EMPLOYEE$]) = 'Y'
begin
'1' --INSERT THE VALUE 1 TO EMPLOYEE
end
else
begin
'2' --INSERT THE VALUE 1 TO EMPLOYEE
end
FROM mockdownload_dd...[EMPLOYEE$]
WHERE EMPLOY_REF NOT IN (SELECT EMPLOY_REF FROM EMPLOYEE)
AND EMPLOY_REF IS NOT NULL




Thanks in advance people.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 04:59:41
you can't use IF .. ELSE here. user CASE statement instead.


KH

Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-07 : 05:01:34
Insert into Employee(...)
select case EMPLE_NOT_MN when 'Y' then 1 else 0 end
from mockdownload_dd...[EMPLOYEE$]
....

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-07 : 05:40:26
Hi. Thanks for the reply.......however I am still a lttle confused by the syntax. I tried the following but it didnt like it:



INSERT INTO EMPLOYEE (
EMPLOY_REF,
EMPLE_NOT_MN,
NOTICE,
SORT_CODE,
NI_NUMBER)
SELECT
CAST(EMPLOY_REF AS varchar(10)),
CASE EMPLE_NOT_MN when 'Y' then 1 else 0 end
CAST(SORT_CODE AS varchar(8)),
CAST(NI_NUMBER AS varchar(9))
FROM mockdownload_dd...[EMPLOYEE$]
WHERE EMPLOY_REF NOT IN (SELECT EMPLOY_REF FROM EMPLOYEE)
AND EMPLOY_REF IS NOT NULL



Thanks again people.

Thanks again people.
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-07 : 05:43:39
Try This..

INSERT INTO EMPLOYEE (EMPLOY_REF,EMPLE_NOT_MN,NOTICE,SORT_CODE,NI_NUMBER)
SELECT
CAST(EMPLOY_REF AS varchar(10)),
CASE EMPLE_NOT_MN when 'Y' then 1 else 0 end As EMPLE_NOT_MN,
CAST(SORT_CODE AS varchar(8)),
CAST(NI_NUMBER AS varchar(9))
FROM mockdownload_dd...[EMPLOYEE$]
WHERE EMPLOY_REF NOT IN (SELECT EMPLOY_REF FROM EMPLOYEE)
AND EMPLOY_REF IS NOT NULL


If this query is genric then you can create the UDF, passing
EMPLE_NOT_MN to it and return 1 or 0 based on the selection

Chirag
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 05:58:30
[code]
INSERT INTO EMPLOYEE ( EMPLOY_REF, EMPLE_NOT_MN, NOTICE, SORT_CODE, NI_NUMBER) -- You have 5 columns here
SELECT CAST(EMPLOY_REF AS varchar(10)),
CASE EMPLE_NOT_MN when 'Y' then 1 else 0 end, -- missing comma
-- But you only have 4 in your select statement. You miss out NOTICE
CAST(SORT_CODE AS varchar(8)),
CAST(NI_NUMBER AS varchar(9))
FROM mockdownload_dd...[EMPLOYEE$] e
WHERE NOT EXISTS (SELECT * FROM EMPLOYEE x WHERE x.EMPLOY_REF = e.EMPLOY_REF)
AND EMPLOY_REF IS NOT NULL[/code]


KH

Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-07 : 06:00:46
opps good check TAN

Chirag
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 06:02:43
"I tried the following but it didnt like it:"
Gavin, you should post the error message. It will be easier for us to catch the mistake.


KH

Go to Top of Page

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-07 : 06:10:10
Brilliant. That works exactly as required. Thank you very much.

Gav
Go to Top of Page
   

- Advertisement -