Count Before Delete & Insert data 1

[eselhassan]

Hi all,

From SQL Server I linked to an iSeries AS400 and everything works just fine. I can query the AS400 file/table with the following count statement:

select * from openquery(MYLINKEDSERVER, 'select count(*) from myFile/myLib')

I would like to use an IF statement first to check whether the AS400 file/table has data or not. If it has then the following statements follow:

Delete from mySQLtable
GO
Insert into mySQLtable(R1, R2, R3)
Select * from openquery(MYLINKEDSERVER, 'select W1, W2, W3 from myFile/myLib')
GO

And if the AS400 file/table is empty then nothing get executed and a message stating that would be nice. Any help is greatly appreciated.

 

[ookete]

You can use the EXISTS keyword.

IF EXISTS (select * from openquery(MYLINKEDSERVER, 'select count(*) from myFile/myLib'))
BEGIN
    DELETE mySQLtable
    INSERT INTO mySQLtable(R1, R2, R3)
    SELECT * from openquery(MYLINKEDSERVER, 'select W1, W2, W3 from myFile/myLib')
END

 

[stevexff]

SELECT COUNT(*) always returns a row. So it will always EXIST...

Can't you

IF (select * from openquery(MYLINKEDSERVER, 'select count(*) from myFile/myLib')) = 0

?

 

[stevexff]

Alternatively you could argue that there isn't much of a performance overhead in doing a DELETE FROM Table if it's empty, so you may as well do it anyway...

 

[SQLSister]

No Steve, he would then delete his data and have no new data to fill in it's place.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[eselhassan]

Thanks to all for their response. I end up with the following:

IF (select * from openquery(MYLINKEDSERVER, 'select count(*) from myFile/myLib')) > 0
BEGIN
    DELETE mySQLtable
    INSERT INTO mySQLtable(R1, R2, R3)
    SELECT * from openquery(MYLINKEDSERVER, 'select W1, W2, W3 from myFile/myLib')
END

Now if myFile in the AS400 is empty I don't get mySQLtable in SQL Server updated. It did work just fine. Thank you all.