asked
Deleting multiple columns
I have just imported a table which has dozens of columns that I don't need (imported from .csv so wasn't given the option to select only certain fields). Rather than delete them by hand I thought I'd write a little loop and pass each column through to be deleted. This is the code: declare @count int = 1 declare @column varchar(10) while @count < 53 begin set @column = 'Column ' + cast(@count as varchar(3)) alter table postcodellsoa drop column @column -- print @column set @count = @count + 1 end However I get the following error: "Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '@column'." Is it not possible to pass parameters through to a DROP command? Thanks in advance, PB
This construction is not working. It should be something like: (I assume column 1 is called "1", column 2 is called "2" etc)
declare @count int = 1
declare @sqlcmd nvarchar(max)
while @count < 53
begin
set @sqlcmd = 'alter table postcodellsoa drop column ' + quotename(cast(@count as varchar(3)))
exec (@sqlcmd)
set @count = @count + 1
end
2nd thought: Maybe it's easier to select to columns you want into a new table like select [1],[17],[23],[24] into from and drop the oldtable
I would select just the columns you want into a new table and then drop the original table.
If you want to import from CSV sources frequently then I'd recommend using LogParser, you can then grab only the columns you want to import and avoid this extra processing. Some details in the seriesd starting here: https://www.red-gate.com/simple-talk/blogs/using-logparser-part-1/
