Fastest Way To Copy Records From One Table To Another Using MS SQL Server

It’s very common for a software developer to want to copy some records from one table to another. All it takes is a very simple SQL statement in SQL Server.

For example,

Insert into Customers
Select * from Customers;

Seems pretty straight forward. As long as the number of columns and column types are the same including size, it should work like a charm.

Why when you run the command and it seems to take forever to complete.

When you run the command and it seems to take forever to complete. There are a million or so records in the Customers table, but it doesn’t seem like it should be that slow. Maybe it’s the indexes that are on the current Customer table. You could drop or disable the indexes and see if that helps, but then you have to ultimately add the indexes back to the table so not very productive.

I would suggest trying a different approach that I have found works well, especially for large tables. Test the technique out and see if it helps. If it does, great. If it doesn’t, then continue the same way you’ve been doing it.

Okay, let’s give it a try.

Instead of doing this:

Insert into Customer:
Select * from Customers;

Do this:

Execute xp_cmdshell 'bcp [database].dbo.CustomersToAdd out p:\[directory]\Customers.txt -T -n';

This Does Requires xp_cmdshell To Be Enabled On The SQL Server:

This does require xp_cmdshell to be enabled on the SQL Server. Replace [database] with the database name where the Customers table is located Replace the P drive with whatever is appropriate, and pick a directory name that SQL Server can write to and read from.

The -n tells SQL Server to output to the SQL Server native format

Then do as follows:

BULK INSERT [database].dbo.Customer FROM 'p:\[directory]\Customer.txt' with (datafiletype = 'native');

Bulk Insert in SQL Server is very fast, and the bcp above is also quite quick.

Since we’re using native format, the column names must be exactly the same, along with datatype, and nullability, along with column size.

Seems strange but it works well. For smaller inserts it doesn’t seem much faster but for 100k adds it does seem to help.

You can also use a query in the first bcp, for example:

Execute xp_cmdshell 'bcp "SELECT * FROM [database].dbo.Customers WHERE balance > 500" queryout p:\[directory]\Customers.txt -T -n'

You can use this technique in stored procedure as well.

Here is another Technique of How To Copy Hundreds of Millions of Records in Less then an Hour:

For Processing hundreds of millions of records it requires a different strategy and the implementation should be different compared to smaller tables with only several millions of records. For this different technique check this article: Processing hundreds of millions records got much easier.

Some Of the Remote DBA Services We Provide:

• MySQL Consulting Services.
• Convert MS Access To Web-Based Application.
• Optimize Your Database.
• Maintain Visual FoxPro Applications.
• Performance tuning and sql specialist.
• Stored Procedures Query Tuning.
• SQL Performance Analysis and tuning.
• Convert Visual FoxPro into the cloud.
• MS Access Consultants.

For more info about Finding MySQL Consulting Experts at Your Convenience and MS SQL consulting services that we provide please call us: 732-536-4765 or send us an email via our Contact Us page.