SQL Server – Copy Huge Table with Transformations to Another Table

bulk-insertsql serversql-server-2016

I have a large table that was imported from a CSV using the import wizard. I have a query that selects from this table and replaces all of the string "code" columns with integer ids from other dimension tables and performs some transformations on some other columns. This all works well.

I need to insert the results of this query into a table in the database that has the required foreign key links and indices. This is also the table that the SQL objects (sprocs, functions, views) use to access the data.

My issue is that the insert takes forever (1.2 hrs) before it eventually dies by complaining about the size of the transaction log. I don't have access to log onto the server. I only have access through SSMS with db_owner rights on the database in question.

My questions are:

How can I perform the insert without logging it in the transaction log? I know there is BULK INSERT and OPENROWSET(BULK ...) but these require a file.
Would the insert go faster if I drop the indices? I think this would break the execution plan of the existing SQL objects that use the destination table. Is this advisable if there is a clustered index on the destination table?
Is not logging the insert the correct way to go or should I use some other method?

Best Answer

If this is a one-time job, you might just use the BCP Utility to do this task.

It seems that your hanging point is doing a massive import that exceeds the resources of your disk space. The thing you need to do is to break the import into batches, which BCP supports, so as to avoid a transaction that cannot fit into your server.

When I have had to make a single massive data transfer, I have had good success using the BCP utility to extract the data to a file. This does, of course, require a file location to temporarily hold the extracted data.

Use The BCP Utility examples from MSDN as samples. (Scroll to see entire commands.)

Copy Data to File Storage:

 bcp WorlWideImporters.Warehouse.StockItemTransactions OUT D:\BCP\StockItemTransactions_native.bcp -m 1 -n -e D:\BCP\Error_out.log -o D:\BCP\Output_out.log -S ServerA -T

To import the data back into a database using BCP you should define a batch size, such as -b 5000 (commit transactions ever 5000 rows) so as to avoid filling up the transaction log.

You may also need to schedule the log backups more frequently during the massive import that you describe. Or you can switch to SIMPLE mode during the insert to the database.

Copy from File Storage to Database

bcp WorlWideImporters.Warehouse.StockItemTransactions_bcp IN D:\BCP\StockItemTransactions_native.bcp -b 5000 -h "TABLOCK" -m 1 -n -e D:\BCP\Error_in.log -o D:\BCP\Output_in.log -S ServerA -T

These are reliable tools, but not the fanciest. For importing data using BULK INSERT which is, in my experience, runs faster that BCP and it also has a parameter for controlling batch size.

You should reference The BCP Utility and BULK INSERT if your needs are more complex than the simple examples I have used.

However, please notice this post with several answers: Freeing Unused Space SQL Server Table

Note that Kin included the options mentioned is BCP and BULK INSERT, but there are other good options is the posts noted above.

Related Solutions

Sql-server – Slow Performance Inserting Few Rows Into Huge Table

Looks like your clustered index page splits are going to be painful because the clustered index holds the actual data and this will need new pages to be allocated and the data moved to these. This is likely to cause page locking and thus blocking.

Remember also that your clustered index key is 21 bytes and this will need to be stored in all of your secondary indexes as a bookmark.

Have you considered making your primary key identity column your clustered index, not only will this reduce the size of your other indexes, it will also mean that you will reduce the number of page splits in your clustered index. It's worth a try if you can stomach rebuilding your indexes.

SQL Server 2005 – Generate and Bulk Insert 3 Million Rows Across 2 Tables

Your table structure looks like this?:

Table1
id (identity)
data1
data2
data3

Table 2
id (identity)
t1id (fk to table1)
data1
data2
...

-- Main Table insert
INSERT INTO table1 (data1,data2,data3)
VALUES('dummy1','dummy2','dummy3');

--get last inserted id
var=SELECT SCOPE_IDENTITY()

-- dependent insert into table2
INSERT INTO table2 (t1id,data1,data2,...)
VALUES(var,'dummy1','dummy2','...');

Look at this answer.
If you return the id from the newly inserted row of Table1 you could use that to insert the dependent row into Table2 without querying Table1 for every row.
If the select for the newly generated id is your bottleneck this should help. If you're full scanning your main table for every row, indexing the id column, if not already done, could also decrease the time needed for the lookup.

Best Answer

Related Solutions

Sql-server – Slow Performance Inserting Few Rows Into Huge Table

SQL Server 2005 – Generate and Bulk Insert 3 Million Rows Across 2 Tables

Related Question