Using "Insert Into" with a dynamic column

Transact-SQL
1

Hi there,

Not sure if this question has been asked before but I have searched around and can't seem to find anything to solve my problem.

I have a blank static table called March_2015 with 36 columns across that are all int type named 1, 2, 3, ...., 35, 36

The Emergency_Utilization table has records like this:

sheltercode adult_adm ....
1 25
2 19
. .
. .
. .

I want the March_2015 table to look like this:

1 2 ...
NULL NULL ...
25 19 ...

This is what I have been attempting but receive an error "Invalid column name '@shelter'."

DECLARE @shelter INT = 1;

WHILE (@shelter < 36)

BEGIN;

INSERT INTO March_2015 (@shelter)
VALUES (NULL);

INSERT INTO March_2015 (@shelter)
SELECT adult_adm FROM Emergency_Utilization
WHERE sheltercode = @shelter;

SET @shelter = @shelter + 1;
END;

Do I need to use Dynamic SQL and, if so, what would that look like?

Thank you for your help!

2

Yes dynamic SQL is required for this. Here is an example:

declare @sql nvarchar(500), @tablename sysname;

set @tablename = 'table1';
set @sql = 'select column1 from ' + @tablename;

exec sp_executesql @sql;

Please read this in its entirety to understand the curse and blessings of dynamic SQL: http://www.sommarskog.se/dynamic_sql.html

3 Likes
3

I might be misreading the requirements but I don't believe you need dynamic SQL for this. I do need some clarification though.... why do you have a row of NULLs in the example result set???

4

Following on from Jeffs point it looks like the O/P has multiple columns instead of multiple rows?

LOOP

INSERT INTO March_2015 (@shelter)
VALUES ...

...

SET @shelter = @shelter + 1

If the "@shelter" columns were moved to a child-table, with an ItemNo column, then I suspect that the problem would solve itself

5

Thanks Tara I will try that way

6

Hi Jeff,

There will be multiple rows and 36 columns.

Some of the rows need to be blank so I just put a Null there for now.

Some of the rows need data from other tables.

It will be recursive for example if shelter id is 1 put it under this column 1, if shelter id is 2 put it under this column, etc

Does that make sense?

7

Yes. And that's pretty much as I figured. No dynamic SQL required for that... just a decent CROSSTAB.

Also, how are you determining which rows will be populated for the March_2015 from the data in the Emergency_Utilization table?

8

Hi All,

Sorry I haven't been really clear. There are a bunch of tables and Emergency_Utilization is one of them. All the tables have the unique id field for shelter code so where shelter code is 1 put this value under column 1 in the March_2015 table. Same for other tables.

The rows are determined in order of Insert Into statements that I hope to put in a while loop.

So my process is populating all of column 1 based on several tables and shelter code 1 then looping around and doing the same for the rest of the shelter codes until the loop ends.

9

Forget the loop for a minute. Is there anything that determines where rows will be placed in the March_2015 table or is it all just "stacked vertically" without regard by key or by temporal nature?

10

The rows in the March_2015 table are just stacked vertically. They are just a bunch of insert into statements one after the other that pull from various tables.

I want to simplify all this instead of writing all this same code for every column (ie shelter) so it would be 36 times. That's why I thought of looping it.

My goal is just to copy the end table and paste it into an Excel template that organization uses. They have been doing this monthly data entry manually for years looking at hundreds of csv files which is time consuming and error prone. My solution is a temporary fix until they figure out how they want to proceed in the future.

11

My solution is a temporary fix until they figure out how they want to proceed in the future.

So you have a solution, then. Let us know "how they want to proceed in the future."

12

Hi Jeff,

Sorry I wasn't clear. My solution is vertically stacking all the insert into statements but I am unable to figure out how to loop these statements to move to the next column. So it does all the insert statements for column 1 (shelter 1) and goes back and does the same for column 2 etc

The insert into statements will be the same except it will say for example insert into column 2 where shelter code = 2.

13

Oooo.... I hate getting used to new forums. My most serious apologies for the month-late reply, I thought I was setup for automatic notification but apparently not.

Are you all set or do you still need help with this?