CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Bistro
Bistro

Reputation: 2025

How to limit execution of nested sql by if

I'm in process of migrating data. I have two tables linked with foreign key as below

master_table
------------
OldId char  -- Old Primary key 
Id    int   -- New Primary Key
....

detail_table
------------
Id    int
old_master_table_id char  -- old FK from master_table
master_table_id     int   -- new FK from master_table
....

What I need to do is fill detail_table.master_table_id with Id from master_table and delete old_master_table_id and OldId. I'm using following Update command and then dropping column. this command works fine for the first time. however I need to limit he execution of commands so it only run if the old columns are there.

IF NOT Exists(SELECT * FROM sys.columns WHERE 
  Object_ID = Object_ID('detail_table') AND Name = 'old_master_table_id') BEGIN
    UPDATE detail_table SET 
       master_table_id = (SELECT Id FROM master_table 
    WHERE 
       detail_table.old_master_table_id=master_table.OldId);
    ALTER TABLE detail_table DROP COLUMN old_master_table_id;
END

Although I have if and it works fine, Above command gives me error Invalid column name 'old_master_table_id'.

How can I avoid above error?

Upvotes: 0

Views: 83

Answers (1)

Dmitry Nogin
Dmitry Nogin

Reputation: 3750

You can try dynamic SQL. You will compile query in question only if applicable. 

IF NOT Exists(SELECT * FROM sys.columns WHERE 
  Object_ID = Object_ID('detail_table') AND Name = 'old_master_table_id') BEGIN
    EXEC sp_executesql "UPDATE detail_table SET " +
    "                   master_table_id = (SELECT Id FROM master_table " +
    "                   WHERE " +
    "                   detail_table.old_master_table_id=master_table.OldId); " +
    "                   ALTER TABLE detail_table DROP COLUMN old_master_table_id; "
END

Upvotes: 2

Related Questions