Is it possible to select sql server data using col

Question

Is it possible to select column data using the ordinal_position for a table column? I know using ordinal positions is a bad practice but for a one-off data import process I need to be able to use the ordinal position to get the column data.

So for example

create table Test(
    Col1 int,
    Col2 nvarchar(10)

)

instead of using

select Col2 from Test

can I write

select "2" from Test -- for illustration purposes only

Answer 1

Yes, you could do this with some really ugly hits into the system tables. You'd probably need to fall into the world of dynamic sql.

I really, really do not recommend this approach.

If that didn't deter you, then this might get you started (ref):

select table_name, column_name, ordinal_position, data_type
from information_schema.columns
order by 1,3

Answer 2

You'd have to do something like

declare @col1 as varchar(128)
declare @col2 as varchar(128)
declare @sq1 as varchar(8000) 

select @col1 = column_name from information_schema.columns where table_name = 'tablename'
and ordinal_position = @position

select @col2 = column_name from information_schema.columns where table_name = 'tablename'
and ordinal_position = @position2

set @sql = 'select ' + col1 ',' + col2 'from tablename' 

exec(@sql)

Answer 3

If you know quantity of columns, but don't know its names and types, you can use following trick:

select NULL as C1, NULL as C2 where 1 = 0 
-- Returns empty table with predefined column names
union all
select * from Test 
-- There should be exactly 2 columns, but names and data type doesn't matter

As a result, you will have a table with 2 columns [C1] and [C2]. This method is not very usefull if you have 100 columns in your table, but it works well for tables with small predefined number of columns.

Answer 4

I don't know of any way to do this short of using dynamic SQL. Maybe if you include a bit more information about why you feel you have to use the ordinal values someone on here can give you advice on how to get around that problem.

EDIT: I see that you answered this to some degree in another comment. Can you provide more specifics? The import proc and/or table definitions?

Answer 5

If you know the number of columns, one way might be to transfer the data into a tmp table with that number of columns and select from the temp table...

declare @tmp table(field1 sql_variant, field2 int, field3 sql_variant)

insert into @tmp
select * from Test

select field2 from @tmp

Answer 6

An option you have is using conditions: In your example:

    SELECT 
     CASE YourColumnNumber 
      WHEN "1" THEN Col1
      WHEN "2" THEN Col2
      ELSE "?"
     END AS Result
    FROM Test

Going to schema will slow query I am afraid...