error adding a column to a table

am trying to add a temporary column to a table(for the duration of the stored procedure)

code

ALTER TABLE [EpiSurv].[ECC] ADD tCaseMigrationID INT NULL

SET IDENTITY_INSERT [EpiSurv].[ECC] ON

INSERT INTO [EpiSurv].[ECC]

( [Name]

, [IsEnabled]

, [CreatedByUserID]

, [CreatedDateTime]

, [tCaseMigrationID]

)

SELECT DISTINCT placeofwk as Workplace

, 0 as [IsEnabled]

, 0 as [CreatedByUserID]

, getdate()

, CaseMigrationID

FROM tCaseMigration

WHERE (WSP1 = 3)

AND (placeofwk is not null and placeofwk <> '')

order by workplace

get back

Msg 207, Level 16, State 1, Procedure spMigration_Step4_Populate_ECCtable, Line 18

Invalid column name 'tCaseMigrationID'.

So your alter table works, and then your insert works, and then the proc fails during the query?

SELECT DISTINCT placeofwk as Workplace
, 0 as [IsEnabled]
, 0 as [CreatedByUserID]
, getdate()
, CaseMigrationID <<<<<-- should this be your newly created column name "tCaseMigration"?
FROM tCaseMigration <<<<<-- is this a correct table name. This is the column you created, is
there a table with the same name or should this be [EpiSurv].[ECC]?
WHERE (WSP1 = 3)
AND (placeofwk is not null and placeofwk <> '')
order by workplace

|||

dan

thx for the reply

i think the t-sql is "correct" - i am trying to add a temporary column on the ECC table to store the CaseMigrationID from the tCaseMigration table - the new temporary column name is tCaseMigrationID on the EpiSurv.ECC table

|||

Maybe you have just to add a semicolon (;) to your line:

ALTER TABLE [EpiSurv].[ECC] ADD tCaseMigrationID INT NULL ;

When i run the alter Table Statement and the Insert statement in the same batch, then i get a similar error message. By adding the semicolon, i have solved it.

But i haven't tried it using a stored procedure.

|||

Yes ..

You will get a compile time error.

When you create the SP the Table ECC doesn;t have the column tCaseMigrationID and the compilation will be fail and you SQL Server won't allow to create a SP..

The possible alternate solution is,

ALTER TABLE [EpiSurv].[ECC] ADD tCaseMigrationID INT NULL

SET IDENTITY_INSERT [EpiSurv].[ECC] ON

Exec ('INSERT INTO [EpiSurv].[ECC]

( [Name]

, [IsEnabled]

, [CreatedByUserID]

, [CreatedDateTime]

, [tCaseMigrationID]

)

SELECT DISTINCT placeofwk as Workplace

, 0 as [IsEnabled]

, 0 as [CreatedByUserID]

, getdate()

, CaseMigrationID

FROM tCaseMigration

WHERE (WSP1 = 3)

AND (placeofwk is not null and placeofwk <> '''')

order by workplace')