Quick Tip: Copying small amounts of data in SQL Server quickly
Last week, I was working on a Microsoft Dynamics GP update case where we had to re-upgrade one company from the previous service pack to the current service pack.
To allow Dynamics GP Utilities to re-upgrade one company there are 3 things we need to do.
- Restore the previous version of the Company database.
- Restore the rows from the pre update data into DYNAMICS.dbo.DB_Upgrade table for the company.
- Restore the rows from the pre update data into DYNAMICS.dbo.DU000020 table for the company.
Well, the first step of restoring a database is fairly easy and won't be covered here.
The quick tip I will be covering is how to use the clipboard and cut & paste to copy data inside Microsoft SQL Server Management Studio.
This example is assuming that you have access to both the pre update DYNAMICS and the current DYNAMICS databases from Microsoft SQL Server Management Studio. This can achieved either by connecting to another SQL instance or by restoring the pre update DYNAMICS database backup into another database name.
Note: Before any direct editing of SQL data, it is recommended that you have backups of the database in case anything goes wrong.
The method is the same regardless of table, I will use the DU000020 table just because it has less columns and will be easier to create screenshots for.
In the source database (pre update DYNAMICS), right click on the dbo.DU000020 table and select Edit Top 200 Rows.
Then click the Show SQL Pane button to allow us to view and edit the SQL statement.
Change the SQL statement to add a where clause for the specific data we want to copy and then click the red exclamation mark to execute.
Now that the source data is displayed, click on the first row of the column to the left of the data (where the black right arrow is on the image above) to select the entire first row. The shift click the same button on the last row of the data (above the NULL row) to select the entire data set. Finally right click on the selected area and select Copy.
Now on the target database, locate the DYNAMICS.dbo.DU000020 table and select Edit Top 200 Rows and use the Show SQL Pane to add the where clause to the SQL statement and Execute the update code. Then select the data that you will be replacing using the same method discussed above (click and shift click on the left hand buttons).
This time right click and select Delete. It is important to remove the old data to avoid duplicate key errors when we paste in the new data.
Once the old data has been removed, click on the button to the left of the NULL line where the asterisk (*) is to select the entire line. Right click on the highlighted line and select Paste.
Voila, the data is pasted into the table. The trick that makes this work is selecting the entire NULL row before pasting.
Note: if you need more than 200 records, you can remove the TOP (200) restriction from the SQL select statement. However, be mindful that the clipboard is not the appropriate method to transfer gigabytes of data.
Hope you find this tip useful. It is definitely much simpler than using DTS (Data Transformation Services), SSIS (SQL Server Integration Services) or T-SQL to create an INSERT statement.
[Edit] For larger data sets have a look at this post: Quick Tip: Copying larger amounts of data in SQL Server quickly.
David
14-Feb-2012: Added mention of SSIS. Happy Valentines Day.
20-Feb-2012: Added link to follow up article.