您可以在insert语句中以不同的方式为您的列排序值吗？

Is there a way to name or guide your values in an insert statement? eg.

有没有办法在insert语句中命名或引导您的值?例如。

declare @temp TABLE
(
    column1 int,
    column2 int,
    column3 int
)

insert @temp(column1, column2, column3)
Select 1 as column1,3 as column3, 2 as column2

select * from @temp

will return a 3 in column2 when I would like the 2 in column2 and 3 in column3.

当我想要第2列中的2和第3列中的3时,将返回第2列中的3。

EDIT

I am trying to write some dynamic sql mapping code. Different columns map to our data from each vendor. So my plan was to write the insert statement to go and grab all the vendor columns from a mapping table. However, I cannot be sure the columns will come out of the mapping table in the correct order.

我正在尝试编写一些动态的sql映射代码。不同的列映射到每个供应商的数据。所以我的计划是编写insert语句,从映射表中获取所有供应商列。但是,我不能确定列将以正确的顺序从映射表中出来。

3 个解决方案

#1

insert into mytable(col1, col3, col2) values (:val1, :val3, :val2)

插入mytable(col1,col3,col2)值(:val1,:val3,:val2)

#2

Different columns map to our data from each vendor.

不同的列映射到每个供应商的数据。

Write a view per vendor that maps each vendor's tables to your common format.

为每个供应商编写一个视图,将每个供应商的表映射到您的通用格式。

Do your inserts from each view, or a union of the views.

从每个视图或视图的并集中进行插入。

#3

Why not just rename the columns? Maybe I am missing something?

为什么不重命名列?也许我错过了什么?

INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);

Allows you to specify the column name and matching values in that same order.

允许您以相同的顺序指定列名称和匹配值。

Edit The order of the columns coming out is precisely in the order that you specify in the select clause. Again I am missing something very big.

编辑出来的列的顺序恰好按照您在select子句中指定的顺序排列。我又错过了一些非常大的东西。

#1