Insert by Column Number, Not Name

[MKVAB]

Is it possible to do an insert by column number???

Like... INSERT INTO tabname 3 VALUES('some insert')
--> 3 being the column number

Thing is, I've got a stored procedure that builds a table dynamically. I need to insert a value into the table but I can't do a simple insert because the number and names of the columns can change each time it's run. So, getting the column count is easy enough. I've got a little loop that goes through and does the insert for each column number. That's where it fails. I could get the column names and use them, it would just be a whole lot easier if I can insert by column number.

Ideas???

 

[philhege]

If you know the ordinal position of the column number, you can query the schema for the column name:

DECLARE @l_vcolumnname sysname
SELECT @l_vcolumnname = column_name FROM information_schema.columns
WHERE table_name = 'yourtablename' AND ordinal_position = 3

At this point, you'd have to construct a dynamic SQL statement for the insert.

SET @l_vSQL = 'INSERT INTO ' +yourtablename +
'('+@l_vcolumnname+') VALUES ('''+@yourvalue+''')

At this point, I would question the architecture of a solution that requires on-the-fly table creation. If you're creating a table with a unique name, it's better to create a column that holds that name as a key.

If the column names are different for each table, I'd once again question the architecture. How do you know the data type of the column? You'd have to know that before you can construct the INSERT statement. At some point, you'd have to know what name the column was given, so you should capture it at that point in the workflow.






Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[SQLSister]

As phil said building tables dynamically is usually a bad idea. And if it is your users building the table it is a worse idea (this is a security issue). Are you using real tables when maybe you need temp tables? Even there building them dynamically is usually a poor idea. Are you trying to make one proc do everything, rather than taking the time to write multiple procs? I can't express strongly enough how very poor an idea that is. It is highly likely to be slow, insecure and bug-ridden.

Any process where you do not know the column names of tables and their datatypes is one that needs to be rethought. This is a system doomed to poor performance and error. I would never consider performing an insert on a table where I did not know the columns and the datatypes in advance. How can you debug it? How can you know what type of information to put where? What do you do if something in the process changes like someone swaps two columsn and you think the data from field1 should go in col 1 but in reality it now is in field2?


"NOTHING is more important in a database than integrity." ESquared

 

[MKVAB]

Thanks for the reply Phil and SQLSister.

The table (temp table) is built dynmically to accomodate data from a pivot table whose columns are created dynamically. The data will always be varchar(25).

I extrapolate the columns from the query (date ranges with sums in the pivot) so the columns will always be named [MM-DD-YYYY], it's just the number, and date value that makes up the column names that will be determined at runtime.

So... I know at the end of the proc how many columns I have, and there's a check not to allow creation of a table with more than 31 columns. I want to insert by column number at the end of the proc. I can work around it though and use the column names that were created earlier in the procedure.

Thanks though.
-MKVAB