Creating an internal table with a variable number of fields

To do this with Dynamic SQL, you will need to either create a global temp
table, or a permanent table.

Here is some code for doing it for a global temp table.


DECLARE @sql varchar(8000),

@NumFields int, @i int

SET @NumFields = 5

SET @sql = 'CREATE TABLE ##ChartData2 ([Date] [nvarchar](100) '

SET @i = 1

WHILE @i <= @NumFields

BEGIN

SET @sql = @sql + ', [Field' + CONVERT(varchar, @i) + '] [int] '

SET @i = @i + 1

END

SET @sql = @sql + ')'

EXEC (@sql)



SELECT * FROM ##ChartData2

DROP TABLE ##ChartData2





Rick Sawtell

On Mar 19, 1:13*pm, "Rick Sawtell"
wrote:
> To do this with Dynamic SQL, you will need to either create a global temp
> table, or a permanent table.
>
> Here is some code for doing it for a global temp table.
>
> DECLARE @sql varchar(8000),
>
> @NumFields int, @i int
>
> SET @NumFields = 5
>
> SET @sql = 'CREATE TABLE ##ChartData2 ([Date] [nvarchar](100) '
>
> SET @i = 1
>
> WHILE @i <= @NumFields
>
> BEGIN
>
> SET @sql = @sql + ', [Field' + CONVERT(varchar, @i) + '] [int] '
>
> SET @i = @i + 1
>
> END
>
> SET @sql = @sql + ')'
>
> EXEC (@sql)
>
> SELECT * FROM ##ChartData2
>
> DROP TABLE ##ChartData2
>
> Rick Sawtell

Wow - that worked really well. Just so I learn a little more...

Why won't a regular (non-global) temp table work? I have never used
global temp tables before - are they visible in Enterprise Manager? I
noticed that if you don't DROP the global table before creating it, it
give you a "already exists" error.

Thank you very much for your help!

>> I typically create internal tables in stored procedures to hold some data I collate, like this: <<

This is a horrible SQL programming technique. You are mimicking a
1950's file system "Scratch tape" right now to storing the improperly
named "date" (it is both a reserved word and too vague to a valid data
element name) as a character string and calling the columns "fields
[sic]", leaving everything NULL-able, etc.

CREATE TABLE #ChartData2
(foobar_date NVARCHAR (100),
field1 INTEGER,
field2 INTEGER,
field3 INTEGER,
field4 INTEGER,
field5 INTEGER,
field6 INTEGER,
field7 INTEGER,
field8 INTEGER);

>> However, I need to be able to make the number of fields [sic:
columns are not fields] a variable. <<

NO! A table be definition has a known fixed number of columns.
Columns have a known data type and constraints, which are two of the
many ways they are different from fields. There is no such thing a
generic, universal, "Automobiles, Squids and Britney Spears" table.

>> For example in certain case, I will only have a 3 fields [sic], and others I will have 20. I need to create the table this way because it feeds a third-party graph control. The graph control requires this format (1 row for each department, which is a "field" in my above example). <<

I would have to see the interface. But why do you have a row in a
column? Why not put a row in a row? Why materialize it when you can
use auery or even a constant table with your list of departments?

On Mar 19, 2:51*pm, --CELKO-- wrote:
> >> I typically create internal tables in stored procedures to hold some data I collate, like this: <<
>
> This is a horrible SQL programming technique. You are mimicking a
> 1950's file system "Scratch tape" right now to storing the improperly
> named "date" (it is both a reserved word and too vague to a valid data
> element name) as a character string and calling the columns "fields
> [sic]", leaving everything NULL-able, etc.
>
> CREATE TABLE #ChartData2
> (foobar_date NVARCHAR (100),
> *field1 INTEGER,
> *field2 INTEGER,
> *field3 INTEGER,
> *field4 INTEGER,
> *field5 INTEGER,
> *field6 INTEGER,
> *field7 INTEGER,
> *field8 INTEGER);
>
> *>> However, I need to be able to make the number of fields [sic:
> columns are not fields] a variable. <<
>
> *NO! *A table be definition has a known fixed number of columns.
> Columns have a known data type and constraints, which are two of the
> many ways they are different from fields. *There is no such thing a
> generic, universal, "Automobiles, Squids and Britney Spears" table.
>
> >> For example in certain case, I will only have a 3 fields [sic], and others I will have 20. I need to create the table this way because it feeds a third-party graph control. The graph control requires this format (1 row for each department, which is a "field" in my above example). <<
>
> I would have to see the interface. *But why do you have a row in a
> column? *Why not put a row in a row? Why materialize it when you can
> use auery or even a constant table with your list of departments?

Sigh. I was wondering how long it would take someone to get upset
about my post (as is always the case in this forum, it seems).

Of course, I was simplifying my syntax for ease of reading. I know
that "Date" is a reserved word and I am not using it in my "real"
code.

As I mentioned, the layout of the table is dictated by the graph
control. Each row represents a set of bar graphs that are grouped
together. I would post a screenshot somewhere, but I am way too busy
now researching the "Automobiles, Squids & Britney Spears" table for
possbile patents I intend to file.

Thanks for help!

Wow - that worked really well. Just so I learn a little more...

Why won't a regular (non-global) temp table work? I have never used
global temp tables before - are they visible in Enterprise Manager? I
noticed that if you don't DROP the global table before creating it, it
give you a "already exists" error.

Thank you very much for your help!


The local temp table will not work as the EXECUTE (varchar) command will
execute (for lack of a better definition) in its own "connection" As soon
as the command ends, the connection is dropped along with the local temp
table.

The global temp table will stay on as long as there is a connection to it.
I'm unsure of how that all works behind the scenes, but the global temp
table will do.


Rick

>> Each row represents a set of bar graphs that are grouped together. <<

No, a row models a member of a set, not a whole set. A normalized,
relational design would look more like this skeleton:

CREATE TABLE Bargraphs
(bargraph_collection_id INTEGER NOT NULL,
bargraph_id INTEGER NOT NULL,
PRIMARY KEY (graph_collection_id, bargraph_id), -- graph within
collection
bargraph_stuff...);

Then each bar graph would get its parameters as needed from the
table.

>> I would post a screen shot somewhere, but I am way too busy now researching the "Automobiles, Squids & Britney Spears" table for possible patents I intend to file. <<

LOL! Too late! I actually found a website with a photo of a giant
squid next to a half-bald Britney Spears with the caption "Which is
prettier, Britney Spears or a squid?" It is already in public domain
and being used by popular media!