Scribblings of a Microsoft Dynamics GP MVP
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.
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
– Make backup of table
select * into GL70500BAK from GL70500select count(*) from GL70500BAK
– Test code to remove data
select count(*) from GL70500delete from GL70500
select count(*) from GL70500
– Recreate table at this stage
– 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
– Remove Backup Table
drop table GL70500BAK/*
// 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.)
*/
Note: The declaration of the varchar for Microsoft SQL Server cannot use the “max” syntax.
To use this script, 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
Original post:
Backing up and Restoring data when recreating SQL Tables