February 23, 2012 at 11:00 am
Is it possible to add column(s) to a temp table using a local variable to determine the column name? Here's what I'm attempting but instead of substituting the value for the variable, it names the column with the name of the variable as part of the column name.
DECLARE @COL_COUNT INT
DECLARE @MAX_COUNT INT
SET @COL_COUNT = 0
set @MAX_COUNT = (SELECT MAX_SID_COUNT FROM #WORK_MAX_SID_COUNT)
--PRINT @COL_COUNT
--PRINT @MAX_COUNT
WHILE @COL_COUNT < @MAX_COUNT
BEGIN
SET @COL_COUNT = @COL_COUNT + 1
alter table #WORK_TRANSFERS_COMBINED
add
[Transcript_Status_@COL_COUNT] varchar(10) NULL
,[Institution_@COL_COUNT] varchar(57) NULL
END
The column is created but it is named "Transcript_Status_@COL_COUNT" instead of "Transcript_Status_1"
Am I trying the impossible?
Thanks,
Paul J McKenna
Felician College
201-559-6123
February 23, 2012 at 11:08 am
mckennap (2/23/2012)
Is it possible to add column(s) to a temp table using a local variable to determine the column name? Here's what I'm attempting but instead of substituting the value for the variable, it names the column with the name of the variable as part of the column name.DECLARE @COL_COUNT INT
DECLARE @MAX_COUNT INT
SET @COL_COUNT = 0
set @MAX_COUNT = (SELECT MAX_SID_COUNT FROM #WORK_MAX_SID_COUNT)
--PRINT @COL_COUNT
--PRINT @MAX_COUNT
WHILE @COL_COUNT < @MAX_COUNT
BEGIN
SET @COL_COUNT = @COL_COUNT + 1
alter table #WORK_TRANSFERS_COMBINED
add
[Transcript_Status_@COL_COUNT] varchar(10) NULL
,[Institution_@COL_COUNT] varchar(57) NULL
END
The column is created but it is named "Transcript_Status_@COL_COUNT" instead of "Transcript_Status_1"
Am I trying the impossible?
Thanks,
Paul J McKenna
Felician College
201-559-6123
*Note: I don't condone this usage as creating tables this way is a bad idea.
You could try using a Tally table to avoid the loop and generate the dynamic sql all at once (I did not do that here)
Try this:
DECLARE @COL_COUNT INT
DECLARE @MAX_COUNT INT
DECLARE @Tmp AS NVARCHAR(MAX)
SET @COL_COUNT = 0
SET @MAX_COUNT = (SELECT MAX_SID_COUNT FROM #WORK_MAX_SID_COUNT)
--PRINT @COL_COUNT
--PRINT @MAX_COUNT
WHILE @COL_COUNT < @MAX_COUNT
BEGIN
SET @COL_COUNT = @COL_COUNT + 1
SET @Tmp = '
ALTER TABLE #WORK_TRANSFERS_COMBINED
ADD [Transcript_Status_' + CAST(@COL_COUNT AS VARCHAR(MAX)) + '] VARCHAR(10) NULL,
[Institution_' + CAST(@COL_COUNT AS VARCHAR(MAX)) + '] VARCHAR(57) NULL '
EXEC (@tmp)
END
February 23, 2012 at 11:08 am
You go to do this via Dynamic SQL
February 23, 2012 at 12:52 pm
Thank you very much. Mycolums were created named exactly how I wanted them. However, I now have a problem accessing the columns using variables. I need to update the values in these new columns based on the same count variable. I thought I was following the same structure but I keep getting errors. Here's my code:
DECLARE @ADDL_COLS INT; SET @ADDL_COLS = 1
DECLARE @MAX_COUNT2 INT; set @MAX_COUNT2 = (SELECT MAX_SID_COUNT FROM #WORK_MAX_SID_COUNT)
DECLARE @tmp2 AS NVARCHAR(MAX)
WHILE @ADDL_COLS < @MAX_COUNT2
BEGIN
SET @ADDL_COLS = @ADDL_COLS + 1;
SET @tmp2 ='
UPDATE #WORK_TRANSFERS_COMBINED
SET [Transcript_Status_' + CAST(@ADDL_COLS AS VARCHAR(MAX)) + ']
= #WORK_TRANSFERS_COMBINED_ROWS.Transcript_Status
,[Institution_' + CAST(@ADDL_COLS AS VARCHAR(MAX)) + ']
= #WORK_TRANSFERS_COMBINED_ROWS.Institution
from #WORK_TRANSFERS_COMBINED_ROWS
WHERE #WORK_TRANSFERS_COMBINED.SID =
#WORK_TRANSFERS_COMBINED_ROWS.SID
AND #WORK_TRANSFERS_COMBINED_ROWS.POS = @ADDL_COLS
'
EXEC (@tmp2)
PRINT @ADDL_COLS
END
I keep getting errors like this:
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ADDL_COLS".
2
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ADDL_COLS".
3
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ADDL_COLS".
4
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ADDL_COLS".
5
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ADDL_COLS".
6
Is there something about UPDATE that's inherently different from ALTER?
February 23, 2012 at 3:46 pm
mckennap (2/23/2012)
Thank you very much. Mycolums were created named exactly how I wanted them. However, I now have a problem accessing the columns using variables. I need to update the values in these new columns based on the same count variable. I thought I was following the same structure but I keep getting errors. Here's my code:DECLARE @ADDL_COLS INT; SET @ADDL_COLS = 1
DECLARE @MAX_COUNT2 INT; set @MAX_COUNT2 = (SELECT MAX_SID_COUNT FROM #WORK_MAX_SID_COUNT)
DECLARE @tmp2 AS NVARCHAR(MAX)
WHILE @ADDL_COLS < @MAX_COUNT2
BEGIN
SET @ADDL_COLS = @ADDL_COLS + 1;
SET @tmp2 ='
UPDATE #WORK_TRANSFERS_COMBINED
SET [Transcript_Status_' + CAST(@ADDL_COLS AS VARCHAR(MAX)) + ']
= #WORK_TRANSFERS_COMBINED_ROWS.Transcript_Status
,[Institution_' + CAST(@ADDL_COLS AS VARCHAR(MAX)) + ']
= #WORK_TRANSFERS_COMBINED_ROWS.Institution
from #WORK_TRANSFERS_COMBINED_ROWS
WHERE #WORK_TRANSFERS_COMBINED.SID =
#WORK_TRANSFERS_COMBINED_ROWS.SID
AND #WORK_TRANSFERS_COMBINED_ROWS.POS = @ADDL_COLS
'
EXEC (@tmp2)
PRINT @ADDL_COLS
END
I keep getting errors like this:
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ADDL_COLS".
2
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ADDL_COLS".
3
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ADDL_COLS".
4
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ADDL_COLS".
5
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@ADDL_COLS".
6
Is there something about UPDATE that's inherently different from ALTER?
More dynamic sql. When dealing with dml and variables that reference columns or tables you have to use dynamic sql. It can't just replace the variable with its value the way you are trying.
I have to say this whole thing you are doing screams of bad design and not normalized structures. Keep in mind you can't exceed 1024 columns in a single table. One of the things about normalization is not repeating values for the same entity in a single row. You are creating sets of duplicates in a loop. I think you would be a lot happier with 2 additional temp tables. Not only will you be rewarded with normalized data you can do your inserts in a single statement instead of a loop.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2012 at 7:21 am
I thought I was using dynamic sql. The same construct worked for defining the columns in an ALTER statement but does not seem to work in the UPDATE statement. I was trying to understand why UPDATE behaved differently from ALTER and what I need to do to make it work - if that's even possible.
These are temp tables for reporting purposes contained in a single report which will be deleted at the end so I'm not concerned with normalization. My reason for trying to push multiple rows into a single row as multiple columns is for the report so that each additional row's data appears on the same line in the report. Our user needs all this information on the same line in an excel spreadsheet so he can do a mail/merge. There is no worry about exceeding 1024 columns as there is a logical limit to the occurrences since they represent previous colleges attended. In our universe, the max to date is 9 for a single student.
February 24, 2012 at 7:29 am
mckennap (2/24/2012)
I thought I was using dynamic sql. The same construct worked for defining the columns in an ALTER statement but does not seem to work in the UPDATE statement. I was trying to understand why UPDATE behaved differently from ALTER and what I need to do to make it work - if that's even possible.These are temp tables for reporting purposes contained in a single report which will be deleted at the end so I'm not concerned with normalization. My reason for trying to push multiple rows into a single row as multiple columns is for the report so that each additional row's data appears on the same line in the report. Our user needs all this information on the same line in an excel spreadsheet so he can do a mail/merge. There is no worry about exceeding 1024 columns as there is a logical limit to the occurrences since they represent previous colleges attended. In our universe, the max to date is 9 for a single student.
Ah yes you are using dynamic sql. It was hard to see much of anything through all the bold text. For code it is best if you use the IFCode blocks. They are over there on the left when you are posting.
You had one of your variables inside your string.
Instead of your original...
ET @tmp2 = '
UPDATE #WORK_TRANSFERS_COMBINED
SET [Transcript_Status_' + CAST(@ADDL_COLS AS VARCHAR(MAX)) + ']
= #WORK_TRANSFERS_COMBINED_ROWS.Transcript_Status
,[Institution_' + CAST(@ADDL_COLS AS VARCHAR(MAX)) + ']
= #WORK_TRANSFERS_COMBINED_ROWS.Institution
from #WORK_TRANSFERS_COMBINED_ROWS
WHERE #WORK_TRANSFERS_COMBINED.SID =
#WORK_TRANSFERS_COMBINED_ROWS.SID
AND #WORK_TRANSFERS_COMBINED_ROWS.POS = @ADDL_COLS
'
You should use something like this:
SET @tmp2 = '
UPDATE #WORK_TRANSFERS_COMBINED
SET [Transcript_Status_' + CAST(@ADDL_COLS AS VARCHAR(MAX)) + ']
= #WORK_TRANSFERS_COMBINED_ROWS.Transcript_Status
,[Institution_' + CAST(@ADDL_COLS AS VARCHAR(MAX)) + ']
= #WORK_TRANSFERS_COMBINED_ROWS.Institution
from #WORK_TRANSFERS_COMBINED_ROWS
WHERE #WORK_TRANSFERS_COMBINED.SID =
#WORK_TRANSFERS_COMBINED_ROWS.SID
AND #WORK_TRANSFERS_COMBINED_ROWS.POS = ' + cast(@ADDL_COLS as varchar(10))
Hope that helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2012 at 7:55 am
It worked like a charm and gave me exactly what I wanted.
Thank you very much.
I'm new to this forum and will use the IFCode shortcuts in the future - thanks for the tip.
February 24, 2012 at 7:58 am
You are welcome. Glad that worked for you. The code blocks especially are useful since it does a decent job of even keeping the color coding. We always appreciate the feedback that you found a solution. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply