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