如何在不重复“插入到dbo”的情况下插入多行。说什么?

I know I've done this before years ago, but I can't remember the syntax, and I can't find it anywhere due to pulling up tons of help docs and articles about "bulk imports".

我知道我在几年前就已经这么做了，但是我不记得语法了，而且我在任何地方都找不到它，因为它需要大量的帮助文档和关于“大量导入”的文章。

Here's what I want to do, but the syntax is not exactly right... please, someone who has done this before, help me out :)

这是我想做的，但是语法不太正确……拜托，以前做过这个的人，帮帮我吧

INSERT INTO dbo.MyTable (ID, Name)
VALUES (123, 'Timmy'),
    (124, 'Jonny'),
    (125, 'Sally')

I know that this is close to the right syntax. I might need the word "BULK" in there, or something, I can't remember. Any idea?

我知道这接近于正确的语法。我可能需要“散装”这个词，或者别的什么，我记不清了。任何想法?

I need this for a SQL Server 2005 database. I've tried this code, to no avail:

我需要一个SQL Server 2005数据库。我试过这个密码，但没有效果:

DECLARE @blah TABLE
(
    ID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
)

INSERT INTO @blah (ID, Name)
    VALUES (123, 'Timmy')
    VALUES (124, 'Jonny')
    VALUES (125, 'Sally')

SELECT * FROM @blah

I'm getting Incorrect syntax near the keyword 'VALUES'.

我在关键字“值”附近得到了不正确的语法。

12 个解决方案

#1

INSERT INTO dbo.MyTable (ID, Name)
SELECT 123, 'Timmy'
UNION ALL
SELECT 124, 'Jonny'
UNION ALL
SELECT 125, 'Sally'

For SQL Server 2008, can do it in one VALUES clause exactly as per the statement in your question (you just need to add a comma to separate each values statement)...

对于SQL Server 2008，可以按照您的问题中的语句在一个VALUES子句中完成它(您只需添加一个逗号来分隔每个VALUES语句)……

#2

Your syntax almost works in SQL Server 2008 (but not in SQL Server 2005¹):

您的语法在SQL Server 2008(但在SQL Server 20051中不适用):

CREATE TABLE MyTable (id int, name char(10));

INSERT INTO MyTable (id, name) VALUES (1, 'Bob'), (2, 'Peter'), (3, 'Joe');

SELECT * FROM MyTable;

id |  name
---+---------
1  |  Bob       
2  |  Peter     
3  |  Joe       

---

¹ When the question was answered, it was not made evident that the question was referring to SQL Server 2005. I am leaving this answer here, since I believe it is still relevant.

当回答这个问题时，并没有明显地说明这个问题指的是SQL Server 2005。我把这个答案留在这里，因为我相信它仍然是相关的。

#3

If your data is already in your database you can do:

如果你的数据已经在你的数据库中，你可以这样做:

INSERT INTO MyTable(ID, Name)
SELECT ID, NAME FROM OtherTable

If you need to hard code the data then SQL 2008 and later versions let you do the following...

如果您需要对数据进行硬编码，那么SQL 2008和后续版本将允许您执行以下操作……

INSERT INTO MyTable (Name, ID)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)

#4

You could do this (ugly but it works):

你可以这么做(虽然很丑，但很管用):

INSERT INTO dbo.MyTable (ID, Name) 
select * from
(
 select 123, 'Timmy'
  union all
 select 124, 'Jonny' 
  union all
 select 125, 'Sally'
 ...
) x

#5

Using INSERT INTO ... VALUES syntax like in Daniel Vassallo's answer there is one annoying limitation:

使用插入……就像Daniel Vassallo的回答一样，有一个令人讨厌的限制:

From MSDN

从MSDN

The maximum number of rows that can be constructed by inserting rows directly in the VALUES list is 1000

通过直接插入值列表中的行可以构造的最大行数为1000

The easiest way to omit this limitation is to use derived table like:

省略此限制的最简单方法是使用派生表，如:

INSERT INTO dbo.Mytable(ID, Name)
SELECT ID, Name 
FROM (
   VALUES (1, 'a'),
          (2, 'b'),
          --...
          -- more than 1000 rows
)sub (ID, Name);

LiveDemo

LiveDemo

---

This will work starting from SQL Server 2008+

#6

You can use a union:

你可以使用联盟:

INSERT INTO dbo.MyTable (ID, Name) 
SELECT ID, Name FROM (
    SELECT 123, 'Timmy'
    UNION ALL
    SELECT 124, 'Jonny'
    UNION ALL
    SELECT 125, 'Sally'
) AS X (ID, Name)

#7

This looks OK for SQL Server 2008. For SS2005 & earlier, you need to repeat the VALUES statement.

这对于SQL Server 2008来说是没问题的。对于SS2005或更早的版本，您需要重复下面的值语句。

INSERT INTO dbo.MyTable (ID, Name)  
VALUES (123, 'Timmy')  
VALUES (124, 'Jonny')   
VALUES (125, 'Sally')  

EDIT:: My bad. You have to repeat the 'INSERT INTO' for each row in SS2005.

编辑::我的坏。您必须为SS2005中的每一行重复“插入”。

INSERT INTO dbo.MyTable (ID, Name)  
VALUES (123, 'Timmy')  
INSERT INTO dbo.MyTable (ID, Name)  
VALUES (124, 'Jonny')   
INSERT INTO dbo.MyTable (ID, Name)  
VALUES (125, 'Sally')  

#8

It would be easier to use XML in SQL Server to insert multiple rows otherwise it becomes very tedious.

在SQL Server中使用XML插入多行将更容易，否则将变得非常繁琐。

View full article with code explanations here http://www.cyberminds.co.uk/blog/articles/how-to-insert-multiple-rows-in-sql-server.aspx

在http://www.cyberminds.co.uk/blog/articles/how- insert-multi -row -in-sql-server.aspx中查看全文，并提供代码说明

Copy the following code into sql server to view a sample.

将以下代码复制到sql server中以查看示例。

declare @test nvarchar(max)

set @test = '<topic><dialog id="1" answerId="41">
        <comment>comment 1</comment>
        </dialog>
    <dialog id="2" answerId="42" >
    <comment>comment 2</comment>
        </dialog>
    <dialog id="3" answerId="43" >
    <comment>comment 3</comment>
        </dialog>
    </topic>'

declare @testxml xml
set @testxml = cast(@test as xml)
declare @answerTemp Table(dialogid int, answerid int, comment varchar(1000))

insert @answerTemp
SELECT  ParamValues.ID.value('@id','int') ,
ParamValues.ID.value('@answerId','int') ,
ParamValues.ID.value('(comment)[1]','VARCHAR(1000)')
FROM @testxml.nodes('topic/dialog') as ParamValues(ID)

#9

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

OR YOU CAN USE ANOTHER WAY

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES 
('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)

#10

Corresponding to INSERT (Transact-SQL) (SQL Server 2005) you can't omit INSERT INTO dbo.Blah and have to specify it every time or use another syntax/approach,

对应于插入(Transact-SQL) (SQL Server 2005)，您不能省略插入到dbo。每次都要指定它或者使用另一种语法/方法，

#11

I've been using the following:

我一直在使用以下内容:

INSERT INTO [TableName] (ID, Name)
values (NEWID(), NEWID())
GO 10

It will add ten rows with unique GUIDs for ID and Name.

它将为ID和名称添加10行唯一的GUIDs。

Note: do not end the last line (GO 10) with ';' because it will throw error: A fatal scripting error occurred. Incorrect syntax was encountered while parsing GO.

注意:不要用';'结束最后一行(GO 10)，因为它会抛出错误:发生致命的脚本错误。解析GO时遇到不正确的语法。

#12

This is working very fast,and efficient in SQL. Suppose you have Table Sample with 4 column a,b,c,d where a,b,d are int and c column is Varchar(50).

这是一种非常快速、高效的SQL。假设有4列ab c d的表样本，其中ab d为int c列为Varchar(50)

CREATE TABLE [dbo].[Sample](
[a] [int] NULL,
[b] [int] NULL,
[c] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[D] [int] NULL
)

So you cant inset multiple records in this table using following query without repeating insert statement,

因此，如果不重复插入语句，就不能使用以下查询在该表中插入多个记录，

DECLARE @LIST VARCHAR(MAX)
SET @LIST='SELECT 1, 1, ''Charan Ghate'',11
     SELECT 2,2, ''Mahesh More'',12
     SELECT 3,3,''Mahesh Nikam'',13
     SELECT 4,4, ''Jay Kadam'',14'
INSERT SAMPLE (a, b, c,d) EXEC(@LIST)

Also With C# using SqlBulkCopy bulkcopy = new SqlBulkCopy(con)

同样使用c#使用SqlBulkCopy = new SqlBulkCopy(con)

You can insert 10 rows at a time

一次可以插入10行

   DataTable dt = new DataTable();
        dt.Columns.Add("a");
        dt.Columns.Add("b");
        dt.Columns.Add("c");
        dt.Columns.Add("d");
        for (int i = 0; i < 10; i++)
        {
            DataRow dr = dt.NewRow();
            dr["a"] = 1;
            dr["b"] = 2;
            dr["c"] = "Charan";
            dr["d"] = 4;
            dt.Rows.Add(dr);
        }
        SqlConnection con = new SqlConnection("Connection String");
        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(con))
        {
            con.Open();
            bulkcopy.DestinationTableName = "Sample";
            bulkcopy.WriteToServer(dt);
            con.Close();
        }