Added a Column to a table and fill it with existing column data from that table

Question

I am updating some code removing a relationship from some terms we have with credit limits. To do this I get to update the database!

The new schema will add a column for CreditLimitCode which will be the same as the old column TermsCodeId without that columns keys. I need to leave TermsCodeId intact for the time being so a rename is not possible.

I attempted the answer for this SO question but that give me syntax errors because CreditLimitCode does not exist and I do not like the default being added.

IF COL_LENGTH('CreditApp.dbo.CreditLimit', 'CreditLimitCode') IS NULL
BEGIN
    ALTER TABLE CreditApp.dbo.CreditLimit ADD CreditLimitCode VARCHAR(6) NOT NULL DEFAULT (0)

    UPDATE CreditApp.dbo.CreditLimit SET CreditLimitCode = CreditTermsId WHERE CreditLimitCode = 0

    PRINT 'Added CreditLimitCode to CreditLimt'
END

I am not sure a trigger for after insert here would be correct because I am altering the table not inserting rows.

@Lamak can you elaborate a bit. My skills with SQL fall into creating tables and some mild updates. — nerdlyist, Feb 19 '16 at 16:53
Is this for mysql or sql-server? They are different products that will likely have different answers. — Ben Thul, Feb 19 '16 at 19:27

score 1 · Accepted Answer · answered Feb 19 '16 at 17:11

1

exec('UPDATE CreditApp.dbo.CreditLimit 
  SET CreditLimitCode = CreditTermsId 
  WHERE CreditLimitCode = 0')

because at compilation time CreditLimitCode does not exist

answered Feb 19 '16 at 17:11

Ivan Starostin

8,798
5
21
39

Added a Column to a table and fill it with existing column data from that table

1 Answers1