question

Patsy avatar image
Patsy 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
t-sqlddl
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
1 Like 1 ·
Thanks for this Wilfred, it worked perfectly. Your 2nd thought would have been good too. Thanks again.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

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/

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you for that. P.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.