ALTER Table Statement doesn''t work in Stored Procedure

Question

ALTER Table Statement doesn''t work in Stored Procedure

melanbe

SSC Enthusiast

Points: 194
More actions
December 14, 2005 at 7:55 am

#364427

I have the following stored procedure where I am trying to alter the table after a bulk insert of data from a flat file. Why doesn't the stored procedure recognize the alter statement or is there a way to add this to the bulk insert even though this field does not exist in the flat file.

ALTER PROCEDURE Photo_Extract2
AS
DROP TABLE tmpPhotos
DROP TABLE ALL_LISTINGS_PHOTO_EXTRACT
CREATE TABLE tmpPhotos( ID varchar (25), Picture varchar (1000), Thumbnail varchar(1000), Sort varchar (3),
   UpdateDate datetime)
BULK INSERT REO.dbo.[tmpPhotos]
from 'd:\websites\reo_photo_list_extract.txt'
with(
  fieldterminator = '|',
  rowterminator = '\n',
  firstrow = 2
      )
ALTER TABLE tmpPhotos ADD ImageID int identity
SELECT * FROM tmpPhotos WHERE ImageID = NULL ORDER BY ID DESC
SELECT O.ID
        , MIN(CASE O.Rank WHEN 1 THEN O.Picture ELSE NULL END) AS ImageURL1
        , MIN(CASE O.Rank WHEN 1 THEN O.Thumbnail ELSE NULL END) AS Thumbnail1
        , MIN(CASE O.Rank WHEN 2 THEN O.Picture ELSE NULL END) AS ImageURL2
        , MIN(CASE O.Rank WHEN 2 THEN O.Thumbnail ELSE NULL END) AS Thumbnail2
        , MIN(CASE O.Rank WHEN 3 THEN O.Picture ELSE NULL END) AS ImageURL3
        , MIN(CASE O.Rank WHEN 3 THEN O.Thumbnail ELSE NULL END) AS Thumbnail3
        , MIN(CASE O.Rank WHEN 4 THEN O.Picture ELSE NULL END) AS ImageURL4
        , MIN(CASE O.Rank WHEN 4 THEN O.Thumbnail ELSE NULL END) AS Thumbnail4
, MIN(CASE O.Rank WHEN 5 THEN O.Picture ELSE NULL END) AS ImageURL5
        , MIN(CASE O.Rank WHEN 5 THEN O.Thumbnail ELSE NULL END) AS Thumbnail5
, MIN(CASE O.Rank WHEN 6 THEN O.Picture ELSE NULL END) AS Picture6
        , MIN(CASE O.Rank WHEN 6 THEN O.Thumbnail ELSE NULL END) AS Thumbnail6
, MIN(CASE O.Rank WHEN 7 THEN O.Picture ELSE NULL END) AS Picture7
        , MIN(CASE O.Rank WHEN 7 THEN O.Thumbnail ELSE NULL END) AS Thumbnail7
, MIN(CASE O.Rank WHEN 8 THEN O.Picture ELSE NULL END) AS Picture8
        , MIN(CASE O.Rank WHEN 8 THEN O.Thumbnail ELSE NULL END) AS Thumbnail8
, MIN(CASE O.Rank WHEN 9 THEN O.Picture ELSE NULL END) AS Picture9
        , MIN(CASE O.Rank WHEN 9 THEN O.Thumbnail ELSE NULL END) AS Thumbnail9
, MIN(CASE O.Rank WHEN 10 THEN O.Picture ELSE NULL END) AS Picture10
, MIN(CASE O.Rank WHEN 10 THEN O.Thumbnail ELSE NULL END) AS Thumbnail10
, MIN(CASE O.Rank WHEN 11 THEN O.Picture ELSE NULL END) AS Picture11
, MIN(CASE O.Rank WHEN 11 THEN O.Thumbnail ELSE NULL END) AS Thumbnail11
, MIN(CASE O.Rank WHEN 12 THEN O.Picture ELSE NULL END) AS Picture12
, MIN(CASE O.Rank WHEN 12 THEN O.Thumbnail ELSE NULL END) AS Thumbnail12
, MIN(CASE O.Rank WHEN 13 THEN O.Picture ELSE NULL END) AS Picture13
, MIN(CASE O.Rank WHEN 13 THEN O.Thumbnail ELSE NULL END) AS Thumbnail13
, MIN(CASE O.Rank WHEN 14 THEN O.Picture ELSE NULL END) AS Picture14
, MIN(CASE O.Rank WHEN 14 THEN O.Thumbnail ELSE NULL END) AS Thumbnail14
, MIN(CASE O.Rank WHEN 15 THEN O.Picture ELSE NULL END) AS Picture15
, MIN(CASE O.Rank WHEN 15 THEN O.Thumbnail ELSE NULL END) AS Thumbnail15
INTO ALL_LISTINGS_PHOTO_EXTRACT
        -- add case statements for the total number of columns
FROM (SELECT TOP 100 PERCENT ID
                , Picture, Thumbnail, Rank = (SELECT COUNT(*) FROM tmpPhotos R
                        WHERE I.ID = R.ID AND R.ImageID < I.ImageID)+1
        FROM tmpPhotos I ORDER BY ID, Rank) O
GROUP BY O.ID

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ron k SSCertifiable Points: 6103 More actions · Answer 1

The ALTER statement will only take affect at the end of the batch. The end of the batch in this case is the entire stored procedure.

If you put your code into a script and add a "GO" after the Alter it would work. However, that is not an option in the stored procedure as the "GO" will terminate the proc.

I've seen this problem posted before but never have seen a solution. You might try putting the Alter statement in a separate stored procedure and call it from your proc. Not sure if that works but its worth a try. Let us know.

ron

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

I'd say the design is flawed. The purpose of the ALTER TABLE is to add an IDENTITY column to data that has just been loaded. Don't fight the tool.

Create a staging table whose columns exactly match the file being loaded. Process then becomes:

Truncate staging table

Bulk Insert to staging table

INSERT INTO [PermanentTable]... SELECT FROM [Stagingtable]

The permanent table already has an Identity, so no need for Table dropping/re-creating and no need for altering tables.

melanbe SSC Enthusiast Points: 194 More actions · Answer 3

I followed PW's suggestion regarding the design and added another table to the stored proc to accomodate the imageid identity field.

Thanks to ron k for letting me know that this was not just me.