MS SQL Server, multiple insertion

Question

Say I'm writing a 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 my hard drive and errors.

Why can't SQL server process my query?

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

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

UPDATE: I also want to mention that my source table occupies about 1 GB of memory when I look at it in the management studio. Still, is my 25 GB of free disk space somehow filling up? I also use two different databases Source.mdf -> Destination.mdf, I don't know if this matters.

Answer 1

...

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)

, , txn ..

Answer 2

( Csv native) .

BCP.

Answer 3

"" "" ( ). "" Management Studio. . , , Full.

Answer 4

post SQL Server.

, , , .

, .

Answer 5

BULK_LOGGED .

- SIMPLE FULL.

, , .

- , , ( . , msdn.microsoft.com/en-us/library/ms191244.aspx).

BULK_LOGGED

,

    --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;   

* . , * [: P]

. , .

. MSDN. Full Bulk-Logged msdn.microsoft.com/en-us/library/ms190203.aspx

Answer 6

, . , , . :

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

:

DBOPTION 'SELECT INTO' true

http://www.mssqlcity.com/FAQ/Devel/select_into.htm

Answer 7

INSERT INTO... SELECT (22 ) , . , , , , .

- , .

BCP, - BULK INSERT, , . BCP.

T-SQL, . INSERT... SELECT TOP (n)... WHERE NOT EXISTS , WHERE . , pk n , ROW_NUMBER() OVER (ORDER BY pk) WHERE rn% (n) = 0. INSERT INTO... SELECT... WHERE pk > @a AND pk <= @b, temp. , .

, Integration Services, . Microsoft - 30 . (BCP?) , . , . . , , .

30 : https://technet.microsoft.com/en-us/library/dd537533(v=sql.100).aspx