Quick Tip: Copying larger amounts of data in SQL Server quickly
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, https://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