T-SQL Alter Statement Quirk

To answer a recent question to the PSSUG user group e-mail, I put together a post instead of trying to answer via e-mail.

First, the question:

I have a staging table.

Stage_id identity
Stage_File_Name nvarchar(100)
AllData nvarchar(max)

I load a pipe delimited file into it. Placing the entire record into the nvarchar(max). This way I can load anything into it.

Instead of haveing a separate staging table for each differant file.

I am trying to Add the columns I need. Splitting out the file. Then moving it into the normal table for that file.

I have it working in a batch but cannot make it a stored procedure.

Alter table staging
Add colmumn A char(10),
B INT
C DATETIME
ETC…….

UPDATE staging
SET A = SS(ALLDATA,1,4)
B
C
ETC….

INSERT NORMAL TABLE (A,B,C,ETC…)
SELECT STAging

Alter table staging
drop column a,b,c,etc….

this works well until I try to make it so I can run it via stored procedure. Eventually I would like to create a job agent.

Any Ideas how I can make this work. Or am I going to have to write it to create a new staging table each time and drop it.

Here’s my (edited) answer, updated with working t-sql below.

…Assuming I understood your question correctly, I can give you a solution, but not an explanation. Perhaps someone else can chime in on the why.

If you wrap the alter statements (to add the columns) and update statements (on the new columns) in separate strings, and execute using the EXEC command, you can wrap it in a stored procedure. …

Here is the setup, with the script I whipped up based on the question.

CREATE DATABASE Testing_AlterStatementFun
GO

USE Testing_AlterStatementFun
GO

IF OBJECT_ID('Foo') IS NOT NULL
 DROP TABLE Foo

CREATE TABLE Foo
(
    PK INT IDENTITY(1,1)
    , FK INT NOT NULL
    , BigData VARCHAR(MAX) NOT NULL
)

INSERT INTO Foo ( FK, BigData ) VALUES ( 1, 'abcdefghijklmnopqrstuvwxyz' )


ALTER TABLE Foo
    ADD First5 VARCHAR(5)
    , Last5 VARCHAR(5)
   
UPDATE Foo
SET First5 = LEFT( BigData, 5 )

UPDATE Foo
SET Last5 = RIGHT( BigData, 5 )

The above code works as a script. Next, the part that doesn’t work. First, I recreate the table, since if you create Foo_Fail without doing that, it will see that table Foo has the referenced columns.

--Recreate and Load the table.
IF OBJECT_ID('Foo') IS NOT NULL
 DROP TABLE Foo

CREATE TABLE Foo
(
    PK INT IDENTITY(1,1)
    , FK INT NOT NULL
    , BigData VARCHAR(MAX) NOT NULL
)

INSERT INTO Foo ( FK, BigData ) VALUES ( 1, 'abcdefghijklmnopqrstuvwxyz' )

-- Can't create it, since First5 doesn't exist.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[Foo_Fail]') AND OBJECTPROPERTY(Object_id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[Foo_Fail]
GO

CREATE PROCEDURE [dbo].[Foo_Fail]
AS

    ALTER TABLE Foo
        ADD First5 VARCHAR(5)
        , Last5 VARCHAR(5)
       
    UPDATE Foo
    SET First5 = LEFT( BigData, 5 )

    UPDATE Foo
    SET Last5 = RIGHT( BigData, 5 )

GO

As advertised, this fails.

However, this does function (it’s ugly, but it works).

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[Foo_Pass]') AND OBJECTPROPERTY(Object_id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[Foo_Pass]
GO


CREATE PROCEDURE [dbo].[Foo_Pass]
AS

EXEC( '
ALTER TABLE Foo
    ADD First5 VARCHAR(5)
    , Last5 VARCHAR(5)' )
   
EXEC( '
UPDATE Foo
SET First5 = LEFT( BigData, 5 )' )

EXEC( '
UPDATE Foo
SET Last5 = RIGHT( BigData, 5 )' )


GO

Finally, here’s the script working.

IF OBJECT_ID('Foo') IS NOT NULL
 DROP TABLE Foo

CREATE TABLE Foo
(
    PK INT IDENTITY(1,1)
    , FK INT NOT NULL
    , BigData VARCHAR(MAX) NOT NULL
)

INSERT INTO Foo ( FK, BigData ) VALUES ( 1, 'abcdefghijklmnopqrstuvwxyz' )

EXEC [dbo].[Foo_Pass]

And finally, cleanup your mess.

USE Master
GO
DROP DATABASE Testing_AlterStatementFun
GO
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *