In PostgreSQL I can create a table with some test data, and then in a transaction migrate it to a new column of a different type resulting in one table-rewrite upon COMMIT
,
CREATE TABLE foo ( a int );
INSERT INTO foo VALUES (1),(2),(3);
Followed by,
BEGIN;
ALTER TABLE foo ADD COLUMN b varchar;
UPDATE foo SET b = CAST(a AS varchar);
ALTER TABLE foo DROP COLUMN a;
COMMIT;
However, that same thing in Microsoft's SQL Server seems to generate an error. Compare this working db fiddle, where the ADD
(column) command is outside of the transaction,
-- txn1
BEGIN TRANSACTION;
ALTER TABLE foo ADD b varchar;
COMMIT;
-- txn2
BEGIN TRANSACTION;
UPDATE foo SET b = CAST( a AS varchar );
ALTER TABLE foo DROP COLUMN a;
COMMIT;
to this db fiddle which doesn't work,
-- txn1
BEGIN TRANSACTION;
ALTER TABLE foo ADD b varchar;
UPDATE foo SET b = CAST( a AS varchar );
ALTER TABLE foo DROP COLUMN a;
COMMIT;
But instead errors
Msg 207 Level 16 State 1 Line 2
Invalid column name 'b'.
Is there anyway to make this transaction visible, with regard to DDL, behave like PostgreSQL?