Is it possible to select sql server data using column ordinal position
ordinals, sql, sql-server, sql-server-2005, t-sql
Solution
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.
Problem
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 ```