Alter Table Then Update in Single Statement

alter table then update in single statement

You can't do this exactly in a single statement (or batch) and it seems the tool you are using does not support GO as a batch delimiter.

You can use EXEC to run it in a child batch though.

ALTER TABLE A
  ADD c1 INT, c2 VARCHAR(10);

EXEC('
UPDATE A
SET    c1 = 23,
       c2 = ''ZZXX'';
    ');

NB: All single quotes in the query need to be doubled up as above to escape them inside a string literal.

Or alternatively you could achieve similar results in a single statement with the aid of some default constraints.

ALTER TABLE A
  ADD c1 INT NULL CONSTRAINT DF_A_c1 DEFAULT 23 WITH VALUES, 
     c2 VARCHAR(10) CONSTRAINT DF_A_c2 NULL DEFAULT 'ZZXX' WITH VALUES;

But this is not exactly the same as the original query as the default constraints will be left behind and may need to be dropped.

Alter Table and Update column in single stored procedure?

The problem is that the procedure is compiled before it is executed. I think the only way around this in a stored procedure is dynamic SQL:

CREATE PROCEDURE alter_then_update AS 
BEGIN
    ALTER TABLE table_1 ADD bundle_type NVARCHAR(10);

    EXEC sp_executesql 'UPDATE table_1 SET bundle_type = ''Small''';
END;

Alter table then update

You can add the new column and populate it at the same time by adding a default and using the WITH VALUES clause. You can then drop the default at the end if no longer needed. This approach can be used for multiple columns as below.

ALTER TABLE [myTable]
ADD [my_new_column] [bit] NULL CONSTRAINT DF_TMP DEFAULT 0 ,
    [my_new_column2] [bit] NULL CONSTRAINT DF_TMP2 DEFAULT 1 WITH VALUES;

ALTER TABLE [myTable] DROP DF_TMP, DF_TMP2

Add column to table and then update it inside transaction

GO is not a T-SQL command. Is a batch delimiter. The client tool (SSM, sqlcmd, osql etc) uses it to effectively cut the file at each GO and send to the server the individual batches. So obviously you cannot use GO inside IF, nor can you expect variables to span scope across batches.

Also, you cannot catch exceptions without checking for the XACT_STATE() to ensure the transaction is not doomed.

Using GUIDs for IDs is always at least suspicious.

Using NOT NULL constraints and providing a default 'guid' like '{00000000-0000-0000-0000-000000000000}' also cannot be correct.

Updated:

• Separate the ALTER and UPDATE into two batches.
• Use sqlcmd extensions to break the script on error. This is supported by SSMS when sqlcmd mode is on, sqlcmd, and is trivial to support it in client libraries too: dbutilsqlcmd.
• use XACT_ABORT to force error to interrupt the batch. This is frequently used in maintenance scripts (schema changes). Stored procedures and application logic scripts in general use TRY-CATCH blocks instead, but with proper care: Exception handling and nested transactions.

example script:

:on error exit

set xact_abort on;
go

begin transaction;
go

if columnproperty(object_id('Code'), 'ColorId', 'AllowsNull') is null
begin
    alter table Code add ColorId uniqueidentifier null;
end
go

update Code 
  set ColorId = '...'
  where ...
go

commit;
go

Only a successful script will reach the COMMIT. Any error will abort the script and rollback.

I used COLUMNPROPERTY to check for column existance, you could use any method you like instead (eg. lookup sys.columns).

Alter and update statement executed together

You need to alter like this using GO:

BEGIN TRANSACTION
GO
ALTER TABLE ProcedureMaster
add TierId smallint null
GO
update ProcedureMaster set TierId=3 where TierId is null   
COMMIT TRANSACTION    

How to alter table and update column values in the same area / stored procedure?

To accomplish what you are trying to do you need to defer the compilation of the update statement.

You can do this using exec sp_executesql, try the following:

IF COL_LENGTH('dbo.raw_customer', 'LegalName') IS NULL
BEGIN
   exec sp_executesql N'ALTER TABLE [raw_customer] ADD [LegalName] varchar(255)'

   exec sp_executesql N'UPDATE [raw_customer] SET [LegalName] = [Name]'
END

Alter then update causes an error

You need to ensure that that UPDATE isn't compiled until after you're actually created the column.

Put it in a separate context by using EXEC:

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.Columns 
               WHERE table_name = 'T1' AND column_name = 'C1')
BEGIN
    ALTER Table T1
        ADD C1 BIT NOT NULL CONSTRAINT DF_T1_C1 DEFAULT 0   

   EXEC('UPDATE Table T1 
         SET C1 = 1')
END
GO

---

Related Topics