troubleshooting Question

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

Avatar of ScuzzyJo
ScuzzyJoFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server
17 Comments1 Solution140 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
lcohan
Database Analyst
Join our community to see this answer!
Unlock 1 Answer and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros