| Author |
 Topic  |
|
|
GavinD1977
Yak Posting Veteran
United Kingdom
83 Posts |
 Posted - 08/07/2006 : 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)
Singapore
16745 Posts |
Posted - 08/07/2006 : 04:59:41
|
you can't use IF .. ELSE here. user CASE statement instead.
KH
|
 |
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 08/07/2006 : 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" |
|
|
|
GavinD1977
Yak Posting Veteran
United Kingdom
83 Posts |
Posted - 08/07/2006 : 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. |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 08/07/2006 : 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 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 08/07/2006 : 05:58:30
|
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
|
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 08/07/2006 : 06:00:46
|
opps good check TAN
Chirag |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 08/07/2006 : 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
|
|
|
|
GavinD1977
Yak Posting Veteran
United Kingdom
83 Posts |
Posted - 08/07/2006 : 06:10:10
|
Brilliant. That works exactly as required. Thank you very much.
Gav  |
|
|
| |
Topic |
|
Using IF's within an UPDATE
