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

No, you can't select columns based on their ordinal position, as far as I know.

When looking at the transact SQL reference, there is nothing to suggest you can (http://msdn.microsoft.com/en-us/library/ms176104(SQL.90).aspx).

Answer 2

If you are using MS SQL 2005 you can use the ROW_NUMBER function.

SELECT Col1, Col2, ROW_NUMBER() OVER(ORDER BY Col1) FROM Test WHERE ROW_NUMBER() Over(Order BY Col1) Between @Position AND @Position

That should get you the desired results if I am reading the question correctly.

Answer 3

You can use this query

select * from information_schema.columns

to get the ordinal positions of the columns. Like Michael Haren wrote, you'll have to build a dynamic query using this, either in code or in a sproc that you pass the column positions to.

FWIW, this is pure evil.

Also, deathofrats is right, you can't really do this, since you'll be building a regular query w/ column names based on position.

Answer 4

I don't think you can. As @Michael Haren showed, you can use ordinal positions in ORDER BY clauses but I've never seen them used elsewhere in SQL Server.

I'm not sure what problem you are havng with your one-off data import that this would help with - presumably some unfortunate column name? Can you explain a little more?