CODEWITHSUNDEEP
sqlsql-server
Ctrl_Alt_Defeat
Ctrl_Alt_Defeat

Reputation: 4009

SQL Server Pre and Post Deployment Scripts

Bit of a SQL newbie so hopefully explain this ok:

So I have a USCars and a USCars_AUD (for Audit) table. Then I have a Cars and a Cars_AUD table.

In my USCars and USCars_AUD table I had a column Description. I am deleting this column from the table and adding it to the Main Cars and Cars_AUD table.

We have a database project to do this and there is Pre and Post Deployment scripts. The Id column in the USCars and USCars_AUD will be the same Id as the Cars and Cars_AUD table has.

So I have updated my schema objects in the database project to reflect that USCars and a USCars_AUD will have description deleted and that it is added to Cars and Cars_AUD.

I am struggling a bit with the query to run pre-deployment and post-deployment. Ideally I want an IF type condition - to say only run if USCars table contains description column. (not sure if that is possible)

I then need to copy all the data from both tables into temp tables in pre-deploy

    -- need an If conditional to only run if USCars table contains Description
    -- then begin /end
print 'Moving USCars and Cars and Audit tables and related data to temp tables'
        exec sp_executesql N'
select * into Upgrade_USCars from USCars
select * into Upgrade_USCars_AUD from USCars_AUD
'

Now Cars table has some extra info in it that is not in USCars so would I need to select everything from it into a temp table joined by the Id? So the result will be I want my final Cars and Cars_AUD table to contain everything it had in it originally but where it had a Description in the USCars and USCars_AUD table I want to copy across the description to the row with the same Id

In the Post Deploy then would I insert the values from this Joined temp table back into Cars Table and then Drop the temp tables

Upvotes: 1

Views: 1223

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Syntax to check for column existence is:

IF EXISTS (SELECT * FROM sys.columns 
           WHERE object_id = OBJECT_ID('YourTableName') 
              AND name = 'YourColumnName')
BEGIN
  -- your logic goes here
END

Upvotes: 2

Related Questions