Quick Tip: Copying larger amounts of data in SQL Server quickly

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

I recently posted the Quick Tip: Copying small amounts of data in SQL Server quickly. This quick tip used the clipboard to copy data between tables as an alternative to using DTS (Data Transformation Services), SSIS (SQL Server Integration Services) or Transact-SQL (T-SQL).

In this post, I thought I would show a fairly simple way to perform the same result using T-SQL commands. The benefit of this method is that it can work with larger data sets and will have better performance than the copy & paste method. We will use the same example scenario as the previous post.

The desired result is to re-upgrade a company from a previous service pack to the current service pack.

The example assumes that the pre-update backup of the company database has been restored and that the pre-update backup of the DYNAMICS database has been restored to DYNAMICSOLD.

To complete the process we need to restore the pre-update values for the company to be re-upgraded into the DU000020 and DB_Upgrade tables in the DYNAMICS database.

The code below copies the data from DU000020 table in the DYNAMICSOLD database into a temporary table and then inserts the data into the DU000020 table.

SQL Copy Data Example

-- Copy required data from old database into temporary table
SELECT *
INTO #DU000020
FROM DYNAMICSOLD.dbo.DU000020 -- Path to old copy of Dynamics Database
WHERE companyID = - 1

-- Display contents of temporary table
SELECT *
FROM #DU000020

-- Remove data from table to avoid duplicate errors
DELETE
FROM DYNAMICS.dbo.DU000020
WHERE companyID = - 1

-- Insert data from temporary table into current database
INSERT INTO DYNAMICS.dbo.DU000020
SELECT [companyID]
	,[PRODID]
	,[versionMajor]
	,[versionMinor]
	,[versionBuild]
FROM #DU000020

-- Drop temporary table
DROP TABLE #DU000020

-- Display final copied data
SELECT *
FROM DYNAMICS.dbo.DU000020
WHERE companyID = - 1

/*
// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
*/

To use this example for other tables you will need to do a Find & Replace on the table name (DU000020) and update the where clause as appropriate.

You will also need to change the columns listed for the insert statement. To get a list of columns nicely formatted that can be cut and pasted into your script, use the attached ListColumns.sql script (attached to the bottom of the post).

Also make sure you have a look at this post for a method of Backing up and Restoring data when recreating SQL Tables.

Hope you find this method useful.

David

SQL ListColumns.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

3 thoughts on “Quick Tip: Copying larger amounts of data in SQL Server quickly

  1. Another idea would be to script the outcome and import that e.g. with ssmstoolspack export resultset to a script and then insert into the database from that outputted script.

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.