September 13, 2011 at 10:01 am
Have an existing table on a database. A new version Upgrade involve adding a new column to this table.
Trying to create a generic script, to check if table does not exist, create new, else, alter with new column. The create new table step also involves adding some master data rows.
Script layout below.
--Existing table
CREATE TABLE [dbo].[TestA](
[ID] [int] NULL,
[IdDes] [varchar](20) NULL
)
go
insert into testa([ID],[IdDes]) values(1,'One')
insert into testa([ID],[IdDes]) values(2,'two')
insert into testa([ID],[IdDes]) values(3,'three')
go
--==================================
New upgrade script will be
--==========
if object_id('dbo.TestA') is null
begin
CREATE TABLE [dbo].[TestA](
[ID] [int] NULL,
[IdDes] [varchar](20) NULL,
[IsActive] bit
)
insert into testa([ID],[IdDes],[IsActive]) values(1,'One',1)
insert into testa([ID],[IdDes],[IsActive]) values(2,'two',1)
insert into testa([ID],[IdDes],[IsActive]) values(3,'three',1)
end
else
begin
Alter table dbo.TestA add [IsActive] bit
end
go
The upgrade script is causing errors, indicating invalid column(new column), even though the insert scripts are inside the If block.
Any suggestion, how to re-arrange the common script.
September 13, 2011 at 10:10 am
Replace the insert scripts as below.
exec ('insert into testa([ID],[IdDes],[IsActive]) values(1,''One'',1)')
exec ('insert into testa([ID],[IdDes],[IsActive]) values(2,''two'',1)')
exec ('insert into testa([ID],[IdDes],[IsActive]) values(3,''three'',1)')
This worked.
September 13, 2011 at 11:23 am
Hi
I'd try to avoid the dynamic SQL and move the DML statements into another batch, where IsActive is available.
-- ...
-- New upgrade script will be
--==========
IF OBJECT_ID('dbo.TestA') IS NULL
BEGIN
CREATE TABLE [dbo].[TestA](
[ID] [int] NULL,
[IdDes] [varchar](20) NULL,
[IsActive] bit
);
END
ELSE
BEGIN
ALTER TABLE dbo.TestA add [IsActive] BIT;
END;
GO
IF NOT EXISTS (SELECT * FROM TestA WHERE ID = 1)
BEGIN
INSERT INTO TestA([ID],[IdDes],[IsActive]) values(1,'One',1);
INSERT INTO TestA([ID],[IdDes],[IsActive]) values(2,'two',1);
INSERT INTO TestA([ID],[IdDes],[IsActive]) values(3,'three',1);
END
ELSE
BEGIN
UPDATE TestA SET IsActive = 1;
END;
Greets
Flo
September 13, 2011 at 11:57 am
I like Flo's approach better since it allows you to better error handle things and I think dynamic SQL is harder to read for anyone else following in your work.
September 14, 2011 at 7:49 am
The other question on the original posting, is why it would cause error, even when the insert scripts are inside the "If Object is null" block.
At the compilation time, does SQL checks for existing objects and their structures, and thus causing this error. Any Ideas..
September 14, 2011 at 7:57 am
SQL Server parser validates the existence of all database objects (tables, columns, procedures, ...) before the execution starts. Even if you encapsulate not existing objects into an IF-ELSE block, or create the missing objects within the script, it raises an error.
Greets
Flo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply