Sage Evolution
Space shortcuts
Page tree
Skip to end of metadata
Go to start of metadata

This article will show you how you can run the stored procedure (Generate SQL inserts) for multiple tables. Please check the article below to find the store procedure to generate  SQL to insert:

Note: This stored procedure uses the tables from the MPBM application to copy over the tables.

Step-by-step guide

Please follow the steps below to run the sql.

  1. Copy the SQL from section "Store procedure" and execute the SQL in the database you want copy into.

  2. To run the stored procedure, execute the following code: 

    Sql to execute the _as_sp_GenerateInsertSQL
    exec _as_sp_CopyMPBMDatabse @FromDatabase ='<<Insert database name>>'

    note: replace the <<Insert database name>> with your own values.

--Drop code is include, so that the create sql can be used.
IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_as_sp_CopyMPBMDatabse') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _as_sp_CopyMPBMDatabse

GO

-- =============================================
-- Author:		Jan van der Hilst
-- Company:		Asamco B.V.
-- Date:		21/03/2018
-- Contact:		info@asamco.com
--				jan@asamco.com
-- Description:	This stored procedure will loop over multiple tables and executing the insert sql.
-- How to run:	exec _as_sp_CopyMPBMDatabse @FromDatabase ='<<Insert database name>>'
-- =============================================

-- =============================================
------------------------------------------------
-----© 2018 Asamco B.V. ALL RIGHTS RESERVED-----
---------------www.asamco.com-------------------
---------------Info@asamco.com------------------
-- =============================================

CREATE PROC _as_sp_CopyMPBMDatabse
@FromDatabase as Varchar(300)
AS
SET NOCOUNT ON
BEGIN



CREATE TABLE #TEMPInserts(
	sqlStatement  varchar(max)
)

DECLARE @TableName varchar(200)
DECLARE CursorGetSQL CURSOR 
 	FORWARD_ONLY
 	FOR
		--CHANGE THIS SELECT TO ALTER THE TABLES
		SELECT Name FROM sys.tables 
		where name like '_as_MPBM_%'
		AND Name not like '%_Audit%'
 	OPEN CursorGetSQL
 
 	FETCH NEXT FROM CursorGetSQL INTO @TableName
 				
 	WHILE @@FETCH_STATUS = 0 
 	BEGIN		
		
		--Get the SQL
		INSERT INTO #TEMPInserts
		EXEC _as_sp_GenerateInsertSQL @TableName, @FromDatabase	

 	FETCH NEXT FROM CursorGetSQL INTO @TableName
 	END 
 	CLOSE CursorGetSQL
 	DEALLOCATE CursorGetSQL



DECLARE @SqlToRun nvarchar(max)
DECLARE CursorGetSQL CURSOR 
 	FORWARD_ONLY
 	FOR
		SELECT  sqlStatement from #TEMPInserts
 	OPEN CursorGetSQL
 
 	FETCH NEXT FROM CursorGetSQL INTO @SqlToRun
 				
 	WHILE @@FETCH_STATUS = 0 
 	BEGIN		
		
		--Run the SQL
		--PRINT @SqlToRun
		EXEC( @SqlToRun);
		--Clear the SQL
		Select @SqlToRun = ''

 	FETCH NEXT FROM CursorGetSQL INTO @SqlToRun
 	END 
 	CLOSE CursorGetSQL
 	DEALLOCATE CursorGetSQL

	
END

GO