July 23, 2010 at 2:06 pm
I have an existing table with rows. I need to:
1. add a column.
2. set values of that column
3. no default value on the column (also not null)
4. script such that it can be rerun without harm
I received an error I think because the column insert has not been committed by the time I try to insert data. How can I rewrite this so it works? Adding a go in there won't work.
Also, Is there a way to default a column in a table to the value of another column? This may make no sense from a db design perspective but I have good reasons. I want to default it to an ID that is the primary key unless the value is overridden for a special scenario. I know I could make a separate mapping table but don't want to do that for this one little thing.
IF NOT EXISTS ( SELECT * FROM syscolumns WHERE name='new_col' AND id=OBJECT_ID('old_table') )
BEGIN
ALTER TABLE dbo.old_table
ADD new_col int
***Error here - Invalid column name "new_col"
update dbo.old_table
set new_col = existing_col
ALTER TABLE dbo.old_table
ALTER COLUMN new_col int NOT NULL
update dbo.old_table
set new_col = 33
where existing_col = 119
update dbo.old_table
set new_col = 111
where existing_col = 129
update dbo.old_table
set new_col = 4
where existing_col = 125
END
July 23, 2010 at 2:14 pm
IF NOT EXISTS ( SELECT * FROM syscolumns WHERE name='new_col' AND id=OBJECT_ID('old_table') )
BEGIN
ALTER TABLE dbo.old_table
ADD new_col int
END
-- a GO here should work (??)
GO
Scott Pletcher, SQL Server MVP 2008-2010
July 23, 2010 at 2:16 pm
Also, Is there a way to default a column in a table to the value of another column?
Sorry, don't think so. It must be a constant or certain system values (such as GETDATE()); it cannot be another column.
Scott Pletcher, SQL Server MVP 2008-2010
July 23, 2010 at 2:20 pm
I know the GO will work outside of the if begin...end statement but I want the whole thing to run as a batch. Basically this part is just one little part of a huge script that may be run over again so it has to be intelligent enough not to break if run twice, not to do things it has already done, etc. So how can I do this? If I put a variable in with the column addition it goes out of scope after the GO so I can't use it to determine whether or not to run the rest. Thanks.
July 23, 2010 at 2:24 pm
Hmm, don't think SQL will recognize the new column within the same batch. It's already "compiled" the batch, so the new column name won't have been there at compile time.
You could use CONTEXT_INFO to do it in two separate batches. Or use something like a temp table creation.
Scott Pletcher, SQL Server MVP 2008-2010
July 23, 2010 at 3:21 pm
I think you can also switch to dynamic SQL; in that case the ALTER would be fully committed for the next state,ment.
Lowell
July 23, 2010 at 3:25 pm
I would think you would run into the same issue even with dynamic SQL. SQL will not "know" the other table exists in the column at the time he "compiles" the batch.
Scott Pletcher, SQL Server MVP 2008-2010
July 23, 2010 at 3:37 pm
I think the solution is just to only add the column in the if statement. I found out it doesn't matter if you alter the table column as NOT NULL even if it is already that way. So all the rest can go outside the if exists block. Even if it is run over a few times in a setup script it doesn't hurt anything. A little extra processing but the important part is the script doesn't fail.
July 23, 2010 at 3:39 pm
this seems to work for me...
create table old_table(columnOne varchar(30),existing_col int )
insert into old_table
SELECT '119',119 union all
SELECT '129',129 union all
SELECT '125',125 union all
select '100',100
IF NOT EXISTS ( SELECT * FROM syscolumns WHERE name='new_col' AND id=OBJECT_ID('old_table') )
BEGIN
EXEC('ALTER TABLE dbo.old_table ADD new_col int')
EXEC('update dbo.old_table set new_col = existing_col')
EXEC('ALTER TABLE dbo.old_table ALTER COLUMN new_col int NOT NULL')
EXEC('update dbo.old_table set new_col = 33 where existing_col = 119')
EXEC('update dbo.old_table set new_col = 111 where existing_col = 129')
EXEC('update dbo.old_table set new_col = 4 where existing_col = 125')
END
Lowell
July 23, 2010 at 3:42 pm
True, if you do *everything* in dynamic SQL the batch won't matter, since every statement will be run in its own batch. Big overhead for what's being done, but it should work.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply