Is it possible to select sql server data using column ordinal position

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

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.