How to update data immediatly after column has been added

Question

5.00/5 (1 vote)

See more:

I've a task:

Create SQL script to add the column to the database and update it immediately. But this script could be started more then one time - so it is required to this (add and update) only first time.

Here is my solution:

SQL

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance')
BEGIN
	ALTER TABLE [OmniCalc].[dbo].[Table1]
	ADD [PlateDistance] decimal(18,0) default 0 NOT NULL;
	
	UPDATE [OmniCalc].[dbo].[Table1]
	SET [PlateDistance] = [bauteilAbstand] / 2
END

GO

But in result i get:

Msg 207, Level 16, State 1, Line 13
Invalid column name 'PlateDistance'.

I've tried to do some changes to allow GO batch processing, but in this case it is impossible to avoid situation when scripts overwrite user data by scenario:

1. Start script

2. User changes some data

3. Start script again

SQL

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance')
BEGIN
	ALTER TABLE [dbo].Table1
	ADD [PlateDistance] decimal(18,0) default 0 NOT NULL;
	
	Select [PlateDistance] from [dbo].Table1
END

GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance')
BEGIN
	UPDATE [dbo].Table1
	SET [PlateDistance] = [bauteilAbstand] / 2
END

GO

And even if I add variable to the construction - It does not help because variable is loss after GO operator.

SQL

DECLARE @created BIT;

SET @created = 0;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance')
BEGIN
	ALTER TABLE [dbo].Table1
	ADD [PlateDistance] decimal(18,0) default 0 NOT NULL;
	
	Select [PlateDistance] from [dbo].Table1
	
	SET @created = 1;
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance') AND @created = 1
BEGIN
	UPDATE [dbo].Table1
	SET [PlateDistance] = [bauteilAbstand] / 2
END

GO

So. Question - how to ADD column and UPDATE its data immediately only on the first script run?

Posted 18-Apr-12 0:21am

White_Scorpion

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

farhan.mycom · Answer 1 · 2012-04-18T03:54:00

Solution 1

Use Sql Triggers...

SQL

delimiter $$
CREATE TRIGGER stu_insert
AFTER insert ON stu_table FOR EACH ROW
BEGIN

	INSERT into stu_log(user_id, description)
        VALUES (user(), CONCAT('Insert Student record ',
	new.stu_id,' ',new.stu_name,' ',new.stu_class));

END$$
delimiter ;

Posted 18-Apr-12 3:54am

farhan.mycom

Updated 18-Apr-12 3:56am

v2

Pablo Aliskevicius · Answer 2 · 2012-04-18T04:11:00

If you don't want a trigger, you may try this:

SQL

create table Table1 ([bauteilAbstand] int)
insert Table1 values (2)
insert Table1 values (4)
insert Table1 values (6)
go
-- Use a temporary constraint to initialize the column to an invalid value.
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance')
BEGIN
    ALTER TABLE [dbo].[Table1]
    ADD [PlateDistance] decimal(18,0) not null, 
    constraint df_PlateDistance_tmp default -12345678987654321 for PlateDistance;
END
GO
-- Set the new column to the desired value wherever it has the invalid value.
update Table1 
set [PlateDistance] = [bauteilAbstand] / 2 
where [PlateDistance] = -12345678987654321
go
select * from Table1
go
-- If the temporary constraint still exists...
if exists (select * from sys.objects where object_id=object_id(N'df_PlateDistance_tmp') )
begin
    -- Modify the default constratint in the new column to be what it should be: notice its name also changes.
    ALTER TABLE [dbo].[Table1]
    drop constraint df_PlateDistance_tmp;

    ALTER TABLE [dbo].[Table1]
    add constraint  df_PlateDistance default 0 for PlateDistance
end
go
-- exec sp_help table1
go
-- DROP TABLE Table1
go

Hope this helps,
Pablo.