What happens if you lose the connection during a huge insert?

Question

What happens when you lose your connection with the DB during a huge insert. Will part of the data be inserted? Will nothing be inserted? What is the reason for this?

The example statement I am picturing is:

INSERT INTO user 
  (variable) 
values 
  (?),
  (?),
  (?),
  ...,
  (?)

I am specifically interested in MySQL or SQL Server.

Answer 1

For SQL databases that are ACID - A is for Atomic meaning all or none of a transaction will occur. When partial data is loaded and the connection stops, there is no choice in a ACID constraint but to drop all of the data.

For MySQL Innodb (default) is ACID but there is a MyISAM engine which isn't.

Answer 2

For SQL Server, a DML command (like INSERT) is always transaction-protected. If something goes wrong while processing that command, then the whole transaction is rolled back. All or nothing.

Note that there is a difference here from the various bulk-loading options. Those includes BULK INSERT command, BCP.EXE, SqlBulkCopy class and SSIS. For such tools, you define how many rows per transaction. If you for instance specify 10,000 rows per transactions and something goes wrong after 54,321 rows, then you have 50,000 rows committed and 4,321 rows rolled back.