Variable Column Names

[mhoyt]

I imported an Excel spreadsheet which had 46 columns of genuine data, and another 100 or so columns of nothing, but the DTS Wizard assigned all those bogus columns field names (F47, F48, F49...) and inlcuded them with NULL values in my table anyway.

Now the easy thing to do would be to just drop the table, delete the columns in Excel and re-import, but where would be the fun in that?

I did successfully ALTER the table to drop F47, then thought it might be fun to inlcude the ALTER statement in a loop that would save me defining each column.

DECLARE @CN INT
SET @CN = 48
WHILE @CN <50
	BEGIN
		ALTER TABLE SBD_CDE_FILE
		DROP COLUMN ('F'+@CN)
		SET @CN = @CN + 1
	CONTINUE
END

Sadly SQL does not like my syntax for defining the column. I suspect the problem is in my attempt to concatanate the F to the variable. Any ideas?

TIA Mike

 

[mhoyt]

Ha! Found the answer just a few posts back, need the EXECUTE statement to concatanate the command and variable. Following works.

USE SANDBOX

CREATE TABLE TBLCRUD
	(F1 VARCHAR(2)
	,F2 VARCHAR(2)
	,F10 VARCHAR(2)
	,F11 VARCHAR(2)
	,F12 VARCHAR(2))

INSERT INTO TBLCRUD VALUES ('A','B','C','D','E')
INSERT INTO TBLCRUD VALUES ('B','B','C','D','E')
INSERT INTO TBLCRUD VALUES ('C','B','C','D','E')

SELECT * FROM TBLCRUD

DECLARE @CN INT
SET @CN = 10
WHILE @CN <12
	BEGIN	
		EXECUTE ('ALTER TABLE TBLCRUD DROP COLUMN F' + @CN)
		SET @CN = @CN + 1
	CONTINUE
END

SELECT * FROM TBLCRUD