Tag: Application

This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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 of Winthrop Development Consultants

-- Last Modified: 12-Nov-2025

/* 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 TEMP_GL00105 from GL00105 
select count(*) from TEMP_GL00105 
/* 1 - End of Section ................................ */


/* 2 - Optional for testing: Remove data from original table */
-- Test code to remove data
select count(*) from GL00105
delete from GL00105
select count(*) from GL00105
/* 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 @srcfieldlst varchar(max)
declare @dstfieldlst varchar(max)
declare @dstvalues varchar(max)
-- Declare variable for SQL 2000
-- declare @fieldlst varchar(8000)
-- declare @newfieldlst varchar(8000)
-- declare @dstvalues varchar(8000)

-- Insert data from backup table
set @srcfieldlst = ''
select @srcfieldlst = @srcfieldlst + case when len(@srcfieldlst) > 0 then ', ' else '' end + '[' + dst.cn + ']' 
from ( 
	select o.name tn , c.name cn , c.colid from sysobjects o join syscolumns c on o.id = c.id 
	where o.name = 'TEMP_GL00105'  
	and c.name != 'DEX_ROW_ID'  
--	and c.name != 'DEX_ROW_TS'   
) src
join ( 
	select o.name tn, c.name cn , c.colid from sysobjects o join syscolumns c on o.id = c.id 
	where o.name = 'GL00105'  
	and c.name != 'DEX_ROW_ID'  
--	and c.name != 'DEX_ROW_TS'   
) dst
on src.cn = dst.cn
order by src.colid 

set @dstfieldlst = @srcfieldlst
set @dstvalues = ''
select @dstfieldlst = @dstfieldlst + case when len(@dstfieldlst) > 0 then ', ' else '' end + '[' + dst.cn + ']' 
	 , @dstvalues = @dstvalues + ', ' + dst.emptyvalue
from ( 
	select o.name tn , c.name cn , c.colid from sysobjects o join syscolumns c on o.id = c.id 
	where o.name = 'TEMP_GL00105'  
	and c.name != 'DEX_ROW_ID'  
--	and c.name != 'DEX_ROW_TS'   
) src
right outer join ( 
	select o.name tn, c.name cn , c.colid, c.xtype, t.name,
	case 
		when t.name in ('tinyint','smallint', 'int', 'bigint','bit','uniqueidentifier') then '''' + '0' + '''' 
		when t.name in ('numeric', 'real','decimal','float','money') then '''' + '0.0' + '''' 
		when t.name in ('char', 'varchar','nchar','nvarchar','text','ntext','sysname') then '''' + '' + '''' 
		when t.name in ('binary','varbinary','image') then 'cast(' + '0x0' + ' as ' + t.name + ')'
		when t.name in ('datetime','smalldatetime','datetime2','timestamp') then '''' + '1900-01-01 00:00:00.000' + '''' 
		when t.name in ('date') then '''' + '1900-01-01' + '''' 
		when t.name in ('time') then '''' + '00:00:00.000' + '''' 
		else '''' + '' + '''' 
	end emptyvalue
	from sysobjects o join syscolumns c on o.id = c.id join systypes t on c.xtype = t.xtype and c.xusertype = t.xusertype
	where o.name = 'GL00105'  
	and c.name != 'DEX_ROW_ID'  
--	and c.name != 'DEX_ROW_TS'   
) dst
on src.cn = dst.cn
where src.tn is null
order by src.colid 
 
--print @dstfieldlst
--print @srcfieldlst + @dstvalues

exec ( 'insert GL00105 ( ' + @dstfieldlst + ' ) ' + 'select ' + @srcfieldlst + @dstvalues + ' from TEMP_GL00105 ' ) 
select count(*) from GL00105
/* 4 - End of Section ............................................... */


/* 5 - After verifying that the recreated table has the correct data */
-- Remove Backup Table
drop table TEMP_GL00105 
/* 5 - End of Section .............................................. */



select * from GL00105

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.

Here is the script:

• SQL Backup & Restore Table Contents.zip

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.
12-Mar-2019: Updated with latest code.
15-Apr-2025: Updated to fix varchar conversion error for added binary columns.
12-Nov-2025: Updated to fix issue with duplicate column error when custom column types are used. Thanks to Matt Connor for finding this issue and providing the solution.