Fast SQL Copy for Employee Portal

Recently I wanted to copy the contents of my Employee Portal setup from one company to another and then eventually from my test system to my live system. Now I know I could probably do this with a dataport and, to be honest, I did try that but something didn’t quite work. I probably made a silly mistake, but it did inspire me to create a SQL stored procedure to do the job for me.

This stored procedure uses the INFORMATION_SCHEMA in SQL to find the field names of any matchining tables and creates some SQL for me to execute. For safety, I only generate the SQL and do not execute it, giving you a change to change your mind if you don’t like what it is about to do.

The really neat trick is the fact that it will handle fields with an AutoIncrement property set in NAV.

I adapted this from a similar stored procedure I created a while ago that would copy all tables from one company to another which is considerably faster than the NAV backup/restore. For Employee Portal tables I am just assuming they start with ‘EP’ – another good reason to inspect the generated SQL before you execute it.

If you use this code and destroy anything, you only have yourself to blame and I will take no responsibility. This code is posted here “as is” for the sake of sharing knowledge and I will not be held responsible for any loss of data or hair that may result from running it.

Here’s the code...

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[NAV_EP_Copy]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
 DROP PROCEDURE dbo.NAV_EP_Copy
END
GO

CREATE PROCEDURE [dbo].[NAV_EP_Copy]
  @SourceCompany varchar(31), 
  @TargetDatabase varchar(31),
  @TargetCompany varchar(31) 
AS

--Version 2 - added target database and row count checks

DECLARE @SourceTableName varchar(100), @TargetTableName varchar(100)
DECLARE @TmpColumnName varchar(30), @TmpColumnType varchar(100)
DECLARE @SQLStr1 varchar(8000), @SQLStr2 varchar(8000), @SQLStr3 varchar(8000), @Values varchar(8000)
DECLARE @Identity int
DECLARE @ErrorCount int, @Debug int

SET @Debug = 1
SET @ErrorCount = 0

IF @Debug = 0 BEGIN
  PRINT 'Copying NAV Employee Portal'
  PRINT ''
  PRINT ' From ' + @SourceCompany
  PRINT ' To   ' + @TargetDatabase + ' ' + @TargetCompany
  PRINT ''
  PRINT ''
END

IF SUBSTRING(@SourceCompany,LEN(@SourceCompany),1) <> '$' BEGIN
  RAISERROR('Source Company Name must use SQL name (replace chars with underscore) and end in $.',1,1)
  SET @ErrorCount = @ErrorCount + 1
END


IF SUBSTRING(@TargetCompany,LEN(@TargetCompany),1) <> '$' BEGIN
  RAISERROR('Target Company Name must use SQL name (replace chars with underscore) and end in $.',1,1)
  SET @ErrorCount = @ErrorCount + 1
END

-- Debug mode does not do anything other than generate messages so who cares if there is data there alredy.
EXEC('set nocount on select top 1 1 from [' + @TargetDatabase + '].[dbo].[' + @TargetCompany + 'EP WP Request Table Tab'+']')

IF (@@rowcount > 0) BEGIN
 IF @Debug <> 1 BEGIN
  RAISERROR('Target EP WP Request Table Tab table has data.',1,1)
  SET @ErrorCount = @ErrorCount + 1
 END ELSE BEGIN
  PRINT '***** WARNING *****'
  PRINT 'Target EP WP Request Table Tab table has data.'
  PRINT 'Executing these commands will delete that data.'
  PRINT ' '
  PRINT 'Data Loss May Occurr.'
  PRINT '***** WARNING *****'
  PRINT ' '
 END
END

IF @ErrorCount > 0 GOTO ENDHERE

DECLARE srcTable CURSOR FOR
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME LIKE(@SourceCompany+'EP%')

OPEN srcTable

FETCH NEXT FROM srcTable INTO @SourceTableName
WHILE @@FETCH_STATUS = 0 BEGIN
  
  SET @TargetTableName = @TargetCompany+SUBSTRING(@SourceTableName,LEN(@SourceCompany)+1,100)
  SET @SQLStr1 = ''
  SET @SQLStr2 = ''
  SET @SQLStr3 = ''
  SET @Values = ''

  IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity')=1 AND TABLE_NAME = @SourceTableName) > 0 BEGIN
    SET @SQLStr1 = 'SET IDENTITY_INSERT [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] ON '
    SET @Identity = 1
  END

  SET @SQLStr1 = @SQLStr1 + 'TRUNCATE TABLE [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] '
  
  DECLARE srcColumn CURSOR FOR
    SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @SourceTableName
  
  OPEN srcColumn

  SET @SQLStr2 = @SQLStr2 + 'INSERT INTO [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] ('

  FETCH NEXT FROM srcColumn INTO @TmpColumnName, @TmpColumnType
  
  WHILE @@FETCH_STATUS = 0 BEGIN
    
    SET @SQLStr2 = @SQLStr2 + ' ['+@TmpColumnName+']'
    
    IF @TmpColumnType = 'timestamp'
      SET @Values = @Values + ' NULL'
    ELSE 
      SET @Values = @Values + ' ['+@TmpColumnName+']'
  
    FETCH NEXT FROM srcColumn INTO @TmpColumnName, @TmpColumnType
  
    IF @@FETCH_STATUS = 0 BEGIN
      SET @SQLStr2 = @SQLStr2+',' 
      SET @Values = @Values+',' 
    END
  END

  SET @SQLStr2 = @SQLStr2 + ')'
  SET @SQLStr3 = 'SELECT ' + @Values + ' FROM ['+@SourceTableName+'] ' 

  CLOSE srcColumn
  DEALLOCATE srcColumn

  IF @Identity = 1 BEGIN
    SET @SQLStr3 = @SQLStr3 + ' SET IDENTITY_INSERT [' + @TargetDatabase + '].[dbo].[' + @TargetTableName+ '] OFF '
    SET @Identity = 0
  END

  IF @Debug = 1 BEGIN
    PRINT @SQLStr1
    PRINT @SQLStr2
    PRINT @SQLStr3
  END ELSE BEGIN
    PRINT ''
    PRINT '-------------------------------------------------------------'
    PRINT @TargetDatabase + ' ' + @TargetTableName
    EXECUTE ( @SQLStr1 + @SQLStr2 + @SQLStr3 )
  END

  FETCH NEXT FROM srcTable INTO @SourceTableName
END

CLOSE srcTable
DEALLOCATE srcTable

ENDHERE: