Avatar of ScuzzyJo
ScuzzyJo
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

SQL How to add a variable name as part of a new column name

Hi

I hope someone can help me with this.  I have a table in which I need to add new columns.  The data is dynamic and the number of columns I have to add will depend on a count of some other data.  I've got that count and have, so far, written but not tested this:

SELECT @maxoldSSN = max(cnt) FROM LFRD_Old_SSN_Counter;
SET @countoldSNN = 0;
--
WHILE @countoldSNN < @maxoldSSN
BEGIN
      ALTER TABLE LFRD
      ADD old_SSN varchar (15);
      SET @countoldSNN = @countoldSNN + 1
END

My question is how do I add the count part into the new column names?  I want them to be old_SSN1, oldSSN2 and so on.  I realise I can use the value in @countoldSSN but don't know how to concatenate it into a column name.

Hopefully, this won't be too difficult :-).

As an aside, and I hope it's OK to ask this as part of my question, when I had 2012, I had a watch window so that I could see the variables changing when I stepped through.  I can't find that in 2014!  I have looked it up but I don't have the options under the menus that Microsoft seem to think I should.  Does anyone know how to get this to show?

Thanks in advance for your help.

Sarah
Microsoft SQL Server

Avatar of undefined
Last Comment
lcohan

8/22/2022 - Mon