MS SQL Server, multiple insert

Question

Say I write the query:

INSERT INTO DestinationTable
(ColumnA, ColumnB, ColumnC, etc.)
SELECT FROM SourceTable
(ColumnA, ColumnB, ColumnC, etc.)

And my source table has 22 million rows.

SQL server fills up my hard drive, and errors out.

Why can't SQL server handle my query?

Should I use a cursor and insert a row at a time?

PS - it is SQL Express 2005, but I could try on the full version.

UPDATE: I also want to mention that my source table only takes up around 1GB of storage when I look at it in the management studio. And yet my 25GB of free disk space somehow gets filled up? I am also using 2 different databases Source.mdf -> Destination.mdf, I don't know if this makes any difference.

Answer 1

Batch update...

INSERT INTO DestinationTable
    (ColumnA, ColumnB, ColumnC, etc.)
SELECT TOP 100000 ColumnA, ColumnB, ColumnC, etc.
FROM SourceTable
WHERE NOT EXISTS (SELECT *
    FROM DestinationTable
    WHERE DestinationTable.KeyCols = SourceTable.KeyCols)

WHILE @@ROWCOUNT <> 0
    INSERT INTO DestinationTable
        (ColumnA, ColumnB, ColumnC, etc.)
    SELECT TOP 100000 ColumnA, ColumnB, ColumnC, etc.
    FROM SourceTable
    WHERE NOT EXISTS (SELECT *
        FROM DestinationTable
        WHERE DestinationTable.KeyCols = SourceTable.KeyCols)

There are variations to deal with checkpointing, log file management, if you need it in one txn etc

Answer 2

This blog post has info about importing data into SQL Server.

As for the reason you table is filling up, I would look at the schema of the table, and make sure there are the column sizes are as small as they can possibly be.

I would really analyze if all the data is necessary.

Answer 3

You can bulk copy the data to a CSV file and import it in.

Read up the BCP utility here.

Answer 4

You are inserting data in a way that supports a transaction. There is no way to disable this through the method you're using, however you could do this outside of the scope of a transaction through other methods. Read below:

http://support.microsoft.com/kb/59462

The key approach is this:

DBOPTION 'SELECT INTO' to true

http://www.mssqlcity.com/FAQ/Devel/select%5Finto.htm

Answer 5

You could try setting the database recovery model to "Simple" instead of "Full" (the default). This is done on the Options page of the database properties in Management Studio. That should keep your transaction log size down. After you're done the insert you can always set the recovery model back to Full.

Answer 6

I would highly recommend you to set the database recovery model to BULK_LOGGED while carrying out such heavy bulk data operations.

By default - database is set to SIMPLE or FULL recovery model.

The full recovery model, which fully logs all transactions, is intended for normal use.

The bulk-logged recovery model is intended to be used temporarily during a large bulk operation— assuming that it is among the bulk operations that are affected by the bulk-logged recovery model (for more information, see Operations That Can Be Minimally Logged at msdn.microsoft.com/en-us/library/ms191244.aspx).

BULK_LOGGED recovery model minimally logs the transactions

you can do it by using below snippet

    --Determine the recovery model currently used for the database

    SELECT name AS [Database Name],
    recovery_model_desc AS [Recovery Model]
    FROM sys.databases 
    WHERE name=<database_name> ;

    --Remember this recovery model so that you can switch back to the same later

    --set the database recovery model to BULK_LOGGED

    ALTER DATABASE <database_name>  SET RECOVERY BULK_LOGGED;

    --Run your heavy data insert tasks
    INSERT INTO DestinationTable
    (ColumnA, ColumnB, ColumnC, etc.)
    SELECT FROM SourceTable
    (ColumnA, ColumnB, ColumnC, etc.)

    /*Again set the database recovery model to FULL or SIMPLE 
    (the result which we had got from first query)*/

    ALTER DATABASE <database_name>  SET RECOVERY FULL;   
    --OR 
    ALTER DATABASE <database_name>  SET RECOVERY SIMPLE;

*Note - Please do keep patience during the bulk operation is being processed * [:P]

I have done this many times before. Do let me know whether this helped you.

You can refer below MSDN article for details of switching between recovery models - Considerations for Switching from the Full or Bulk-Logged Recovery Model at msdn.microsoft.com/en-us/library/ms190203.aspx