question

jelenas3 avatar image
jelenas3 asked

How to resolve "Invalid column name 'XXXXXX' " on query to persist data in new format in new column when dropping 'old' column not working

Hi, everyone,

I am trying to add a column to a table to save time in seconds. I already have a TIME column, lets call it column A, from which I want to seed data to my new column, lets call it column B.Queries are being generated via Entity Framework Code First Migrations.

They boil down to something like this, and if I try to run the script created by it, I get an error that says Invalid column name. Please help in clarifying why this is happening.

It renders all queries with an idea to do something on a column if it exists a bit impossible to run.\

I really can't understand what is wrong here.

ALTER TABLE [dbo].[TheTable] ADD [ColumnB] [float] NOT NULL DEFAULT 0 IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'ColumnA' AND Object_ID = Object_ID(N'TheTable')) AND EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'ColumnB' AND Object_ID = Object_ID(N'TheTable')) BEGIN UPDATE dbo.TheTable SET ColumnB = -DATEDIFF(SECOND, ColumnA, 0); END
ALTER TABLE [dbo].[TheTable] DROP COLUMN [ColumnA]
dmlddl-changes
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered

You just need a batch separator ("GO") between your update IF block and your second ALTER TABLE

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Actually, you also need one before the IF block too!

0 Likes 0 ·
erlokeshsharma08 avatar image
erlokeshsharma08 answered

or you could run the statements one by one as I assume there are one off scripts.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.