SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using IF's within an UPDATE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GavinD1977
Yak Posting Veteran

United Kingdom
83 Posts

Posted - 08/07/2006 :  04:56:39  Show Profile  Reply with Quote
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)

Singapore
17601 Posts

Posted - 08/07/2006 :  04:59:41  Show Profile  Reply with Quote
you can't use IF .. ELSE here. user CASE statement instead.


KH

Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 08/07/2006 :  05:01:34  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

United Kingdom
83 Posts

Posted - 08/07/2006 :  05:40:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/07/2006 :  05:43:39  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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)

Singapore
17601 Posts

Posted - 08/07/2006 :  05:58:30  Show Profile  Reply with Quote

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



KH

Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/07/2006 :  06:00:46  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
opps good check TAN

Chirag
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17601 Posts

Posted - 08/07/2006 :  06:02:43  Show Profile  Reply with Quote
"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

United Kingdom
83 Posts

Posted - 08/07/2006 :  06:10:10  Show Profile  Reply with Quote
Brilliant. That works exactly as required. Thank you very much.

Gav
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000