是否可以使用列序号位置选择sql server数据 [英] Is it possible to select sql server data using column ordinal position
问题描述
是否可以使用表列的 ordinal_position 来选择列数据?我知道使用序数位置是一种不好的做法,但对于一次性数据导入过程,我需要能够使用序数位置来获取列数据.
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.
例如
create table Test(
Col1 int,
Col2 nvarchar(10)
)
而不是使用
select Col2 from Test
我可以写吗
select "2" from Test -- for illustration purposes only
推荐答案
如果你知道列的数量,但不知道它的名称和类型,你可以使用以下技巧:
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
因此,您将拥有一个包含 2 列 [C1] 和 [C2] 的表格.如果您的表中有 100 列,此方法不是很有用,但它适用于预定义列数较少的表.
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.
这篇关于是否可以使用列序号位置选择sql server数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!