Add column to table in stored procedure
-
Hi,
Say I have a table (named A) which has 5 column. In a stored procedure, I use:
ALTER TABLE A ADD column1 int null
ALTER TABLE A ADD column3 varchar(40) null
to add additional columns to existing table. I got an error when compiled the sp:
Invalid column name 'ConversionFlag'.
Server: Msg 207, Level 16, State 1, Procedure xxxx, Line 84
It seems to me that I need GO between ALTER statement when running the same statement (not within the sp). Is there any additional step I have to do inside sp? Thanks.
Chris
-
I have tested in on my db and I have no problems adding two cols to a table in a stored proc. Try cutting off everything else in your sp but the addition of the cols. If it still fails to compile, please post the table definition along with your stored proc.
By the way, I think it is unwise to modify a table in a stored procedure. This should be done with ordinary SQL commands instead, as it is something you only wish to do once...
-
Thanks for help.
The table:
CREATE TABLE SOP_stgDataConversionBudgetRevenueSellIn (
Column1 varchar(18) NULL,
Column2 varchar(40) NULL,
Column3 smallint NULL,
Column4 varchar(5) NULL,
Column5 decimal(17,3) NULL,
Column6 money NULL
)
go
Part of the sp:
SELECT @TableID = ID FROM sysobjects WHERE Name = 'MyTable'
IF EXISTS(SELECT * FROM syscolumns WHERE ID = @TableID AND Name = 'FinanceType') AND
EXISTS(SELECT * FROM syscolumns WHERE ID = @TableID AND Name = 'ConversionFlag') AND
EXISTS(SELECT * FROM syscolumns WHERE ID = @TableID AND Name = 'Comment')
BEGIN
ALTER TABLE MyTable DROP COLUMN FinanceType
ALTER TABLE MyTable DROP COLUMN ConversionFlag
ALTER TABLE MyTable DROP COLUMN Comment
END
BULK INSERT SOP.dbo.MyTable FROM '\\Dir\Sub Dir\Import.txt' WITH (FIRSTROW = 2)
ALTER TABLE MyTable ADD FinanceType INT NULL
ALTER TABLE MyTable ADD ConversionFlag BIT NULL
ALTER TABLE MyTable ADD Comment VARCHAR(2000) NULL
/**********************************************************************/
/* Validate the staging table. */
/**********************************************************************/
UPDATE MyTable
SET ConversionFlag = 0
,Comment = 'Invalid xxxxxx.'
WHERE Column1 NOT IN (SELECT Column3
FROM dbo.SecondTable)
AND ConversionFlag IS NULL
I could not even compile the sp.
Chris
-
Hi,
I believe I resolve the problem. SQL Server expects all columns (as well as other objects) exist when the sp gets compiled. Compiling error due to the columns don't exists at the time. Changed:
ALTER TABLE MyTable ADD....
to:
SET @AddColumnSQL = 'ALTER TABLE MyTable ADD.....'
EXECUTE @AddColumnSQL
Use dynamic SQL to update those added columns.
Thanks for the help.
Chris
-
Glad to hear that you solved the problem
I still don't like changing a table in a stored procedure. What happens if a user accesses the table while the columns are dropped? If I were you I would go for one of the following:
1. Modify the import file such that the columns match those of the target table.
2. Use a table with the same columns as those in the file and bulk insert into that, then copy data from here into the target table. (I don't know if you can bulk insert into a temp table, if so, that might be a solution).
3. Redesign your db such that instead of the target table, you have a table with the same columns as in the file (and bulk insert into that) and another table with "the extra" columns that you fill out after the bulk insert.
But then again, I don't know your set up and you probably have good reasons for choosing the solution that you have
Good luck.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply