标签云

微信群

扫码加入我们

WeChat QR Code

This question already has an answer here:How do I insert multiple rows WITHOUT repeating the “INSERT INTO dbo.Blah” part of the statement?13 answersI have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office.INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office");INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office");INSERT INTO MyTable VALUES ("Billy", 125, "London Office");INSERT INTO MyTable VALUES ("Miranda", 126, "Bristol Office");Can I insert all 4 rows in a single SQL statement?


Moderator Note: Please take all discussion about the merits of this question to this meta post.

2019年05月24日44分03秒

For oracle sql see stackoverflow.com/a/93724/1452172

2019年05月23日44分03秒

Chinggis6 Yes, trivially possible, just use a select for the column values:

2019年05月24日44分03秒

Chinggis6 insert into profiles (name, description) select first, 'Auto-generated' from usersYou seem to be confusing insert and update statement, which are different beasts.

2019年05月24日44分03秒

Chinggis6 Ah I see.Well, I just recommend using standard insert ... select syntax, it'll get you everything you need and is as flexible as can be wished for. dev.mysql.com/doc/refman/5.5/en/insert.html

2019年05月24日44分03秒

And please note that the maximum number of rows in one insert statement is 1000.

2019年05月23日44分03秒

That should be phrased "the maximum number of rows in one VALUES clause is 1000".It's not the INSERT statement that is limited to 1000 rows.

2019年05月24日44分03秒

I know this question and answer are old, but I like to mention that there is a functional difference between using the method mentioned in the question and in this answer. The first executes triggers X amount of times with 1 record in the inserted table. The second executes triggers 1 time with x amount of records in the inserted table. Making the second method the best choice for performance assuming you made your triggers work correctly with batch inserts.

2019年05月24日44分03秒

This doesn't work with SQL Server 2005, see stackoverflow.com/questions/2624713/…

2019年05月24日44分03秒

ahnbizcad Semicolons in T-sql are USUALLY optional, but they are reported to be required in the future. You should get yourself in the habit of using them to terminate each statement--your code will look nicer too IMO.

2019年05月24日44分03秒

As of SQL Server 2008, this will work if you replace the double quotes with single ones.

2019年05月24日44分03秒

Also works with postgres v9.0

2019年05月24日44分03秒

And with SQLite

2019年05月23日44分03秒

Only SQLite 3.7.11 onwards. If you cannot guarantee that, use the UNION method shown here: stackoverflow.com/a/5009740/841830

2019年05月24日44分03秒

Muneem, the limit is 1,000 VALUE lines per INSERT statement.

2019年05月24日44分03秒

A better question is why would you want to?Why not just run the cleaner 4 commands all together in one batch.If one row fails your batch fails.if you do them individually grouped together 3 of 4 succeed.

2019年05月24日44分03秒

m-t-head I have an example of why I want to, and will give you 2 reasons. I am inserting into a table which has a data integrity checking trigger. Reason 1 - inserting values separately would violate the integrity check thus rolling back the transaction and returning an error. I am using SQL Server which does not support deferred constraints, so even if instead of a trigger it was a regular constraint, it would still not work. Reason 2 - integrity check is an expensive procedure and I'd rather have it executed once instead of 1000's of times per transaction. I'm still looking for solutions.

2019年05月23日44分03秒

you can create one CTE and use insert into YourTable (ID,Name) Fromselect ID,Name From CTE

2019年05月23日44分03秒

What about this question? stackoverflow.com/questions/51763204/… Basically same concern but has the function of updating in case of duplicate record

2019年05月24日44分03秒

m-t-e-head The main reason is performance, actually. Inserting thousands or millions of rows would be much faster when you bundle them together into reasonably sized batches and using a single INSERT per batch.

2019年05月23日44分03秒