Search code examples
sqlmigrationredgate

Is there an easy way to add a custom migration script to SQL Compare scripts?

At my work we are currently having some serious pains pushing our database changes across environments. The issue starts to show up when we create a new non-nullable column on an existing table. The script that SQL Compare generates creates the column as non-nullable, so it will always fail. I was hoping that there was some alternative to having to manually edit the script. Is there any way to get around this? If not, how do you guys handle it?

Solution

  • Create a table:

    create table #bingo ( id int )

    Add a value:

    insert into #bingo values (1)

    Add a new column:

    alter table #bingo add userid int

    Populate the new column:

    update #bingo set userid = 1 where id = 1

    Change the new column to not nullable:

    alter table #bingo alter column userid int not null

    You would have to manually edit the RedGate Sql Compare to make it work like this.