SQL:如何将数据插入到具有列名的表中。

When inserting data into a SQL Server table, is it possible to specify which column you want to insert data to?

当向SQL Server表插入数据时，是否可以指定要插入数据的列?

For a table with

为一个表

I know you can have syntax like this:

我知道你可以有这样的句法

INSERT INTO MyTable (Name, col4_on, col8_on, col9_on)
VALUES ('myName', 0, 1, 0)

But the above syntax becomes unwieldy when you have lots of columns, especially if they have binary data. It becomes hard to match up which 1 and 0 go with which column. I'm hoping there's a named-parameter like syntax (similar to what C# has) which looks like the following:

但是当您有很多列时，上面的语法就变得难以处理了，尤其是当它们有二进制数据时。很难把1和0和哪一列匹配起来。我希望有一个类似于命名参数的语法(类似于c#)，它看起来如下所示:

INSERT INTO MyTable 
VALUES (Name: 'myName', col4_on: 0, col8_on: 1, col9_on: 0)

Thanks

谢谢

5 个解决方案

#1

You must specify the column names. However, there is one exception. If you INSERTing exactly the same number of columns as the target table has in the same order as they are in the table, use this syntax:

必须指定列名。然而，有一个例外。如果您插入的列数目与目标表中的列数完全相同，且顺序与表中的列数相同，请使用以下语法:

INSERT INTO MyTable
VALUES ('val1A', 'val4A', 'val8A')

Note that this is a fragile way of performing an INSERT, because if that table changes, or if the columns are ordered differently on a different system, the INSERT may fail, or worse-- it may put the wrong data in each column.

注意，这是执行插入的脆弱方式，因为如果该表发生更改，或者如果在不同的系统上对列的顺序不同，那么插入可能会失败，甚至更糟——它可能在每个列中放置错误的数据。

I've found that when I INSERT a lot of columns, I find the queries easier to read if I can group them somehow. If column names are long, I may put them on separate lines like so:

我发现，当我插入大量列时，如果我能以某种方式对它们进行分组，查询会更容易阅读。如果列名很长，我可以把它们放在不同的行上，比如:

INSERT INTO MyTable
(
    MyTable_VeryLongName_Col1,
    MyTable_VeryLongName_Col4,
    MyTable_VeryLongName_Col8,
    -- etc.
)
SELECT
    Very_Long_Value_1,
    Very_Long_Value_4,
    Very_Long_Value_8,
    -- etc.

Or you can group 2 columns on a line, or put spaces on every 5, or comment every 10th line, etc. Whatever makes it easier to read.

或者你可以把一行分成两列，或者每5列加空格，或者每10行加注释等等。

If you find including column names onerous when INSERTing a lot of rows, then try chaining the data together:

如果在插入大量行时发现包含列名称很麻烦，那么尝试将数据链接在一起:

INSERT INTO MyTable (col1, col4, col8)
VALUES ('val1A', 'val4A', 'val8A'),
    ('val1B', 'val4B', 'val8B'),
    -- etc.

Or UNION them together:

或结合在一起:

INSERT INTO MyTable (col1, col4, col8)
SELECT 'val1A', 'val4A', 'val8A'
UNION ALL 'val1B', 'val4B', 'val8B'
UNION ALL ... -- etc.

Or, SELECT them from another table:

或者，从另一个表中选择它们:

INSERT INTO MyTable (col1, col4, col8)
SELECT val1, va4, val8
FROM MyOtherTable
WHERE -- some condition is met

#2

No, there is no way to do specifically what you want. The closest thing you can do is to use the column creation order to avoid use the columns names on the insert command. As this:

不，没有办法具体做你想做的事。您可以做的最接近的事情是使用列创建命令，以避免在insert命令中使用列名称。是这样的:

If you have a table like

如果你有一张像这样的桌子

tableA ( id, name, phone )

You can insert values on it using

您可以使用它来插入值

insert into tableA values ( 1, 'Name', '555-9999' );

But be carefull, you have to follow the exact order on the fields of your table, otherwise you can have an error and worst, put wrong data in wrong fields.

但是要小心，必须按照表字段的确切顺序，否则可能会出现错误，最糟糕的是，将错误的数据放在错误的字段中。

#3

Nope you cannot do it, the only other alternative for you would be insert from select

不，你不能这么做，你唯一的选择就是从select中插入

insert into MyTable select 'val1' as col1, 'val4' as col4, 'val8' as col8 --if any extra columns then just do "null as col10"

插入到MyTable中选择'val1'作为col1， 'val4'作为col4， 'val8'作为col8——如果有任何额外的列，那么只需执行"null as col10"

assuming the order is same in table

假设表中顺序相同

#4

INSERT INTO MyTable (col1, col4, col8)
VALUES ('val1', 'val4', 'val8')

This statement will add values to the columns mentioned in your INSERT INTO statement, you can write the above query in the following formats it will not make any difference .

此语句将向INSERT INTO语句中提到的列添加值，您可以按照以下格式编写上述查询，它不会产生任何差异。

INSERT INTO MyTable (col8, col1, col4)
VALUES ('val8', 'val1', 'val4')

或

INSERT INTO MyTable (col4, col8, col1)
VALUES ('val4', 'val8', 'val1')

to Add multiple rows at a time you can pass multiple rows at a time in you values clause something like this

要在一个时间内添加多个行，您可以在一个时间内通过多个行，在您的values子句中，类似这样。

INSERT INTO MyTable (col4, col8, col1)
VALUES ('val4', 'val8', 'val1'),
       ('val4', 'val8', 'val1'),
       ('val4', 'val8', 'val1'),
       ('val4', 'val8', 'val1')

The order of the values should match the order of the columns mentioned in your INSERT INTO statement.

值的顺序应该与INSERT INTO语句中提到的列顺序匹配。

All above statement will have the same result.

以上所有语句都将得到相同的结果。

keeping one thing in mind once you have mentioned a column you must provide a value for it

当你提到一篇专栏文章时，记住一件事，你必须为它提供一个价值。

like this

像这样

INSERT INTO MyTable (col1, col4, col8)
VALUES ('val1', null, 'val8')

but you cannot do something like this

但你不能做这样的事

INSERT INTO MyTable (col1, col4, col8)
VALUES ('val1', 'val8')

#5

I figured out a way around this but it's rather hacky and only works for tables which has columns with unique values:

我想出了一个解决这个问题的方法，但它相当陈腐，只适用于具有唯一值的列的表:

INSERT INTO MyTable (Name)
VALUES ('myName')
UPDATE MyTable
SET col4_on=0, col8_on=1, col9_on=0
WHERE Name = 'myName'

This could be expanded into a multiple row insert as follows:

这可以扩展为如下所示的多行插入:

INSERT INTO MyTable (Name)
VALUES ('row1'), ('row2'), ('row3')
UPDATE MyTable SET col4_on=0, col8_on=1, col9_on=0 WHERE Name = 'row1'
UPDATE MyTable SET col4_on=1, col8_on=0, col9_on=0 WHERE Name = 'row2'
UPDATE MyTable SET col4_on=1, col8_on=1, col9_on=1 WHERE Name = 'row3'

#1