Conditional INSERT - 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

General SQL Server Forums

New to SQL Server Programming

Conditional INSERT

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-08 : 10:20:38

Hello all. I am inserting data into a table from a linked server (.XLS file). One of the columns deals with dates. In the .XLS file some ofthe dates are invalid (either wrong format or wrond data). What i am looking for is how to insert only the rows where the dates are correct. My basic structure is:

INSERT INTO PENSIONS(EMPLOY_REF, CONTRIB_CHANGE)
SELECT CAST(EMPLOY_REF AS VARCHAR(10)),
CAST(CONTRIB_CHANGE AS DATETIME)
FROM mockdownload_dd...[Pensions$]

thanks people

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-08 : 10:30:17

Try incorporating "Case" & "ISDATE" in ur Select Query

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-08 : 10:31:27

add a WHERE clause that check for the format and data

example
-- EMPLOY_REF must exists in another table (table2)

WHERE exists (select * from table2 where col = EMPLOY_REF)

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-08 : 10:37:23

thanks for the reply guys.

khtan.........its not the EMPLOY_REF i'm having problems wirth. Its the CONTRIB_CHANGE.

Srinika......i'm not exactly sure how to do that. Could you explain a little more please.

Thanks.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-08 : 10:40:20

[code]Create table #XLTbl (d varchar(30))

Insert into #XLTbl values ('08/08/2006')
Insert into #XLTbl values ('18/08/2006')
Insert into #XLTbl values ('01/01/2006')
Insert into #XLTbl values ('ABCD')
Insert into #XLTbl values ('08/48/2006')
Insert into #XLTbl values ('07/07/2006')

Create table #DestinationTbl (d Datetime)

Insert into #DestinationTbl
Select Case when isdate(d) = 1 then d
Else Null
End
from #XLTbl

Select * from #DestinationTbl[/code]

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-08 : 10:49:26

quote:
Originally posted by GavinD1977

thanks for the reply guys.

khtan.........its not the EMPLOY_REF i'm having problems wirth. Its the CONTRIB_CHANGE.

Srinika......i'm not exactly sure how to do that. Could you explain a little more please.

Thanks.

The EMPLOY_REF is just an example. What is the problem with CONTRIB_CHANGE ? What are the example of wrong format ?

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-08 : 10:54:25

Hi Srinka.......thanks very much. The only problem with that code is that it still inserts the record where the dat is invalid. It resets the date to 01/01/1900. I am really looking for that particular row to not be entered at all.

The code i have is:

INSERT INTO PENSIONS(EMPLOY_REF,
CONTRIB_CHANGE)
SELECT CAST(EMPLOY_REF AS VARCHAR(10)),
CASE when ISDATE(PEN_DATE) = 1 THEN PEN_DATE
ELSE 0 END
FROM mockdownload_dd...[Pensions$]
WHERE EMPLOY_REF IN (SELECT EMPLOY_REF FROM EMPLOYEE)

Thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-08 : 10:59:46

place the condition in WHERE

INSERT INTO PENSIONS(EMPLOY_REF, CONTRIB_CHANGE)
SELECT CAST(EMPLOY_REF AS VARCHAR(10)), PEN_DATE
FROM   mockdownload_dd...[Pensions$]
WHERE  EMPLOY_REF IN (SELECT EMPLOY_REF FROM EMPLOYEE)
AND    ISDATE(PEN_DATE) = 1

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-08 : 11:02:26

Excellant guys. Works perfectly.

thanks a lot to both of you. Apprecaite it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources