CODEWITHSUNDEEP
sqlsql-serversql-delete
codeApprentice
codeApprentice

Reputation: 31

Delete data from table, which may not exist

I have a scenario where I have to delete records from a table, that may or may not exist in a different database. For example, the table is called Md5SumJob. Which exists in database A but not database B. So what I already have in place is a check to see if the table exists or not in the database. 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'Md5SumJob')

But now I need to figure out how to delete the actual data in the table. The table that may or may not exist only has 1 column (nvarchar). So this is where I cannot figure out how to delete the record based on that 1 column and if the table exists or not. This is what i have tried so far but does not work:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'Md5SumJob')
        DELETE FROM x FROM (select * from INFORMATION_SCHEMA.COLUMNS i where i.TABLE_NAME = 'Md5SumJob') x
        JOIN @pid p on p.CtpJobId = (select * from INFORMATION_SCHEMA.COLUMNS i 
        where i.COLUMN_NAME = 'CtpJobId' and i.TABLE_NAME = 'Md5SumJob')

Upvotes: 1

Views: 1619

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

It is tempting to use dynamic SQL:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'Md5SumJob')
BEGIN
    exec sp_executesql 'delete from Md5SumJob where CtpJobId in (select CtpJobId from @pid)'
END;

The one caveat is that table variables are not scoped in the exec. So, replace it with a temporary table and use:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'Md5SumJob')
BEGIN
    exec sp_executesql 'delete from Md5SumJob where CtpJobId in (select CtpJobId from #pid)'
END;

Upvotes: 0

Ctznkane525
Ctznkane525

Reputation: 7465

Use sp_executesql to execute a dynamic query:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'Md5SumJob')
    EXEC sp_executesql ('DELETE From Md5SumJob');

Reference: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

Upvotes: 2

Related Questions