Solved

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

Posted on 2016-09-13
17
73 Views
Last Modified: 2016-09-15
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
0
Comment
Question by:ScuzzyJo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 2
  • +2
17 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 41796323
You can use dynamic SQL to build the string that will be executed to add the actual column names and you can use the "magic" sysname SQL own datatype for the variable containing the count.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41796331
you will need to use dynamic SQL (concatenate a string and then execute it): https://msdn.microsoft.com/en-us/library/ms188001.aspx
0
 

Author Comment

by:ScuzzyJo
ID: 41796333
Hi Icohan

Thanks for the comment, but it's gone straight over my head!  How do I do any of that?

Thanks
Sarah
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ScuzzyJo
ID: 41796342
Hi Éric

Thanks.  I'm looking it up now to try to understand how it works.

Sarah
0
 
LVL 40

Expert Comment

by:lcohan
ID: 41796353
So please run script below and note it will not change anything but just PRINT commands to be executed later - hope that's what you mean:

declare @maxcnt int;
declare @crtcnt sysname;
declare @sqlstr varchar(max);

--set @maxcnt = SELECT count(*) FROM LFRD_Old_SSN_Counter;
set @maxcnt = 10;


WHILE @maxcnt > 0
BEGIN
      SET @crtcnt = @maxcnt;
      SET @sqlstr = 'ALTER TABLE LFRD ADD old_SSN' + @crtcnt + ' varchar (15);'
      PRINT @sqlstr;
      --EXEC @sqlstr;
      SET @maxcnt = @maxcnt - 1;
END
0
 

Author Comment

by:ScuzzyJo
ID: 41796461
Hi Icohan

Thanks for this.  I'm working through it so that I understand it but will carry on tomorrow as it's nearly 6:00 pm here (UK).

Thanks
Sarah
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41796720
Modern coding preference is to avoid loops.  Typically FOR XML is used to concat in SQL while avoiding looping:

DECLARE @maxoldSSN int;
DECLARE @sql varchar(8000)

SELECT @maxoldSSN = MAX(cnt) FROM LFRD_Old_SSN_Counter;

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) numbers(number)
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
)
SELECT @sql = STUFF((
    SELECT ',oldSSN' + CAST(t.number AS varchar(4)) + ' varchar(15)'
    FROM cteTally100 t
    WHERE t.number BETWEEN 1 AND @maxoldSSN
    FOR XML PATH('')
    ), 1, 1, '')

SET @sql = 'ALTER TABLE dbo.LFRD ADD ' + @sql + ';'
PRINT @sql
EXEC(@sql)
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41797215
@Author - Is your task completed ? Do you need more help with this ? If yes please let me know. Shall write the complete script for you !
0
 

Author Comment

by:ScuzzyJo
ID: 41797545
Hi Scott

Thanks for your post.  I appreciate what you're saying but, at the moment, I'm struggling to understand it as you've used terms and logic that I haven't come across before.

When I'm more advanced, I'll look back at it and try to avoid loops but, for now, it's not my main concern and adds to my confusion.

Thanks
Sarah
0
 

Author Comment

by:ScuzzyJo
ID: 41797555
Hi Pawan

I'm just starting back on this today.  Thanks for your offer.  I might well take you up on understanding it, but I'd rather write it myself or I won't learn.

Thanks
Sarah
0
 

Author Comment

by:ScuzzyJo
ID: 41797596
Hi Icohan

I've worked through that it works fine.  I don't want to just print it though.  I want to actually add the columns into the LFRD table.  I've tried adapting it myself, but it didn't work.

Thanks
Sarah
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41797605
@ScuzzyJo - Excellent attitude. One you fight and do it yourself, you will learn and grow.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 41798460
All you need now is to comment the print commands and uncomment the EXEC line so it will actualy perform the action like this:


declare @maxcnt int;
declare @crtcnt sysname;
declare @sqlstr varchar(max);

--you need to put the actual statement here that returns the MAX number of columns to be added
set @maxcnt = (SELECT count(*) FROM LFRD_Old_SSN_Counter);



WHILE @maxcnt > 0
BEGIN
      SET @crtcnt = @maxcnt;
      SET @sqlstr = 'ALTER TABLE LFRD ADD old_SSN' + @crtcnt + ' varchar (15);'
      --PRINT @sqlstr;
      EXEC @sqlstr;
      SET @maxcnt = @maxcnt - 1;
END
0
 

Author Comment

by:ScuzzyJo
ID: 41799374
Hi Icohan

That's what I tried, among other things, but it didn't work.  It said it couldn't find the stored procedure:

Msg 2812, Level 16, State 62, Line 118
Could not find stored procedure 'ALTER TABLE LFRD ADD old_SSN1 varchar (15);'.

I understand why this should work but have no idea why it doesn't.

Thanks
Sarah
0
 

Author Comment

by:ScuzzyJo
ID: 41799423
Hi Icohan

Thanks for all your help.  I've got it working.  I needed some brackets so that:

EXEC @sqlstr;

needed to be:

  EXEC (@sqlstr);

Thanks
Sarah
0
 

Author Closing Comment

by:ScuzzyJo
ID: 41799426
The solution needed some extra brackets but, other than that, works fine.  This is great as I've now learned how to do something else which, I think will be very, very useful in the future.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 41800255
Sorry I missed those and I'm glad you learned something. That PRINT command is extremely useful to "debug" any SQL dynamic code before actually executing it.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question