September 22, 2009 at 1:43 pm
Ok,
I have an existing SQL table with one column, and our software now requires two columns in the table. I have figured out how to add the column, add data to the column, then set it to be the primary key using seperate queries for everything. Is it possible to have it done in one query? When I run the query below it seems like it tries to loop through the cursor before the columns have actually been added, and obviously this doesn't work. I'd like to make the workload of our installers & support as low as possible.
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ENS_Stock_Messages' AND COLUMN_NAME='Description')
BEGIN
-- Add the colunmn we need
ALTER TABLE [dbo].[ENS_Stock_Messages]
ADD [Description] [varchar](30) NULL
END
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ENS_Stock_Messages' AND COLUMN_NAME='tempid')
BEGIN
-- Add a temporary identity column
ALTER TABLE [dbo].[ENS_Stock_Messages]
ADD [tempid][int]IDENTITY(1,1) NOT NULL
END
-- Declare our variables
DECLARE @i INT,
@temp varchar(max)
-- Set our counter to 1
SET @i = 1
-- Declare the cursor we need for this operation and make sure we select only distinct values from the setting column
DECLARE myCursor CURSOR FOR
SELECT DISTINCT(tempid) FROM [ENS_Stock_Messages] ORDER BY tempid
-- Open the cursor
OPEN myCursor
-- Read the first record into the cursor
FETCH NEXT FROM myCursor
INTO @temp
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Insert what will be our new primary key value
UPDATE [ENS_Stock_Messages] SET [Description] = ('Message ' + cast(@i as varchar)) WHERE tempid = @temp
-- Increment our counter
SET @i = @i + 1
-- Read the next record from the cursor
FETCH NEXT FROM myCursor
INTO @temp
END
-- Close the cursor
CLOSE myCursor
-- Deallocate the cursor
DEALLOCATE myCursor
ALTER TABLE [dbo].[ENS_Stock_Messages]
ALTER COLUMN [Description] varchar(30) NOT NULL
ALTER TABLE [dbo].[ENS_Stock_Messages] ADD PRIMARY KEY ([Description])
ALTER TABLE [dbo].[ENS_Stock_Messages]
DROP COLUMN [tempid]
And to create the table
USE [DB1]
GO
/****** Object: Table [dbo].[ENS_Stock_Messages] Script Date: 09/22/2009 14:44:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ENS_Stock_Messages](
[Message] [varchar](250) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [ENS_Stock_Messages]
VALUES ('Message 1')
INSERT INTO [ENS_Stock_Messages]
VALUES ('Message 2')
INSERT INTO [ENS_Stock_Messages]
VALUES ('Message 3')
INSERT INTO [ENS_Stock_Messages]
VALUES ('Message 4')
INSERT INTO [ENS_Stock_Messages]
VALUES ('Message 5')
INSERT INTO [ENS_Stock_Messages]
VALUES ('Message 6')
September 22, 2009 at 2:27 pm
By adding
USE [DB1]
GO
Before I start the cursor, convert the column to a non-nullable, set it as the primary key & delete the temp column I got it working.
September 22, 2009 at 3:28 pm
You might issue your fill logic dynamically using dynamic SQL. It will execute in a slightly different context and will be recompiled (which is the important part) by default.
CEWII
September 22, 2009 at 3:35 pm
Elliott W (9/22/2009)
You might issue your fill logic dynamically using dynamic SQL. It will execute in a slightly different context and will be recompiled (which is the important part) by default.CEWII
That went way above my head....
September 22, 2009 at 3:44 pm
I think your problem is the optimizer doesn't realize what you've done and generates an error.
But if you execute your update logic, that whole section of code in something like
EXEC ('all that code')
The optimizer won't even look at it until the moment it is run, and at that point the column is there.
Be sure to replace and single quotes with 2 single quotes and it should work.
CEWII
September 22, 2009 at 3:47 pm
Elliott W (9/22/2009)
I think your problem is the optimizer doesn't realize what you've done and generates an error.But if you execute your update logic, that whole section of code in something like
EXEC ('all that code')
The optimizer won't even look at it until the moment it is run, and at that point the column is there.
Be sure to replace and single quotes with 2 single quotes and it should work.
CEWII
So place each important block in an EXEC ('code') ?
September 22, 2009 at 3:50 pm
I would only do the blocks that are causing you problems.
CEWII
September 22, 2009 at 3:55 pm
By changing the query to what is below, I got it working without doing that. Now to just try to remember it for next time.....
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ENS_Stock_Messages' AND COLUMN_NAME='Description')
BEGIN
-- Add the colunmn we need
ALTER TABLE [dbo].[ENS_Stock_Messages]
ADD [Description] [varchar](30) NULL
END
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ENS_Stock_Messages' AND COLUMN_NAME='tempid')
BEGIN
-- Add a temporary identity column
ALTER TABLE [dbo].[ENS_Stock_Messages]
ADD [tempid][int]IDENTITY(1,1) NOT NULL
END
USE [DB1]
GO
-- Declare our variables
DECLARE @i INT,
@temp varchar(max)
-- Set our counter to 1
SET @i = 1
-- Declare the cursor we need for this operation and make sure we select only distinct values from the setting column
DECLARE myCursor CURSOR FOR
SELECT DISTINCT(tempid) FROM [ENS_Stock_Messages] ORDER BY tempid
-- Open the cursor
OPEN myCursor
-- Read the first record into the cursor
FETCH NEXT FROM myCursor
INTO @temp
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Insert what will be our new primary key value
UPDATE [ENS_Stock_Messages] SET [Description] = ('Message ' + cast(@i as varchar)) WHERE tempid = @temp
-- Increment our counter
SET @i = @i + 1
-- Read the next record from the cursor
FETCH NEXT FROM myCursor
INTO @temp
END
-- Close the cursor
CLOSE myCursor
-- Deallocate the cursor
DEALLOCATE myCursor
USE [DB1]
GO
ALTER TABLE [dbo].[ENS_Stock_Messages]
ALTER COLUMN [Description] varchar(30) NOT NULL
USE [DB1]
GO
ALTER TABLE [dbo].[ENS_Stock_Messages] ADD PRIMARY KEY ([Description])
ALTER TABLE [dbo].[ENS_Stock_Messages]
DROP COLUMN [tempid]
September 22, 2009 at 4:07 pm
Great..
CEWII
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply