Backing up and Restoring data when recreating SQL Tables
Added to the Useful SQL Scripts Series.
There are times when you need to recreate a table to change its structure but don't want to lose the data stored in the table. I have had this situation a number of times when working on upgrade support cases where (for some unknown reason) a table does not have the correct table structure to allow the Dexterity Utilities to upgrade it.
Disclaimer: I know that later versions of the Professional Services Tools Library (PSTL) does support recreating a table while maintaining the data. However, you might not have PSTL installed and the script in this post is simple to use.
The script makes a backup of the current table to a new table of the same name with the suffix BAK. Once the backup is created, you can use the SQL Maintenance (File >> Maintenance >> SQL) window in Microsoft Dynamics GP to drop and create the table and its Auto Stored Procedures.
Note: Please see the post Granting Access and Binding Defaults when recreating SQL Tables for more information on creating tables (especially when related to upgrades).
Once the table has been recreated, you can run the rest of the script to copy the contents of the backup table into the newly recreated table. Then the final step is to remove the backup table.
Note: Please make sure you have a current backup of the database before using the code in this article.
T-SQL Script Code
-- Written by David Musgrave, Last Modified: 11-Feb-2011
/* This Script is designed to be executed in sections */
/* Please read the instructions included as comments */
/* Use Find and Replace to change the table name */
/* Highlight each Section and click Execute or F5 */
/* 1 - Make a backup of the table so the data is saved */
-- Make backup of table
select * into GL70500BAK from GL70500
select count(*) from GL70500BAK
/* 1 - End of Section ................................ */
/* 2 - Optional for testing: Remove data from original table */
-- Test code to remove data
select count(*) from GL70500
delete from GL70500
select count(*) from GL70500
/* 2 - End of Section ...................................... */
/* 3 - Drop and recreate original table using correct structure */
-- Recreate table at this stage using SQL Maintenance or T-SQL
-- This will leave a blank table of the correct structure
/* 3 - End of Section ......................................... */
/* 4 - Re-populate original table from the previously backed up table */
-- Declare variable for SQL 2005/2008
declare @fieldlst varchar(max)
-- Declare variable for SQL 2000
--declare @fieldlst varchar(8000)
-- Insert data from backup table
set @fieldlst = ''
select @fieldlst = @fieldlst
+ case when len(@fieldlst) > 0 then ', ' else '' end
+ '[' + c.name + ']'
from sysobjects o join syscolumns c on o.id = c.id
where o.name = 'GL70500BAK'
and c.name != 'DEX_ROW_ID'
and c.name != 'DEX_ROW_TS'
order by colid
--print @fieldlst
exec ( 'insert GL70500 ( ' + @fieldlst + ' ) ' +
'select ' + @fieldlst + ' from GL70500BAK ' )
select count(*) from GL70500
/* 4 - End of Section ............................................... */
/* 5 - After verifying that the recreated table has the correct data */
-- Remove Backup Table
drop table GL70500BAK
/* 5 - End of Section .............................................. */
/*
// 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.)
*/
Note: The declaration of the varchar for Microsoft SQL Server 2000 cannot use the "max" syntax.
To use this script (also attached at the bottom of this article), please highlight the portions you need to execute and press F5. Only run the next section of code once you are satisfied that the previous code has executed successfully.
Hope you find this script useful.
David
11-Feb-2011: Added comments to better explain how script works.
06-Dec-2011: Added link to Granting Access and Binding Defaults when recreating SQL Tables post.