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.

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)