Data Migration

NAV to NAV SQL Fast Data Migration (with dynamic “INSERT INTO” STATEMENT)


new

SCENARIO

If you have some old NAV releases and you want to copy data fast from db\company to another db\company (and you don’t want to use Dataport\XMLPort to map each fields ore use Rapid Start Services), you can use SQL “INSERT INTO” statements to transfer data.

 TIMESTAMP FIELD ISSUE

You can’t use a “FULL SQL INSERT INTO * from table to table” statement because we have TIMESTAMP field ISSUE (look at Dynamics Community…more posts about this….) that can’t be copied to Destination Table

 MANUALLY SCRIPTING OBJECTS

If you can do manually exporting all (or single table) with SQL Script Object generator but I think that my solution can be useful and FAST.

HOW SYSTEM WORKS

System extract and pass fields like dynamics parameters to INSERT INTO Statement (es: company, db, fields); system build final INSERT INTO STATEMENT that will be execute and store script that will be execute on custom Table.

ASSUMPTIONS

  • NB1: TIMESTAMP field in NOT passed, is written automatically by Stored Procedure execution
  • NB2: “NO data validation and NAV logic is executing”, only SQL datatype fields mapping is checked
  • NB3: if you have more fields on Target Database than in Source Database (with “NOT NULL” restrictions on fields), will be necessary to MAP adjuntives fields on final SQL INSERT STATAMENTS passing 0 or ‘’ (BLANK) values for new fields.

OPERATIONAL STEPS

1) Create Stored Procedure “CopyTable

2) Create e new table “Tables Migration Config

3) Execute Stored Procedure “CopyTable” that write in field “Exec Script” > After copy and past on Excel on direct SQLQuery window to execute text existing on Exec Script field

1 ) Create Stored Procedure “CopyTable”

 copytable

CopyTable” Stored Procedure

USE [DB_SQL]

GO

/****** Object: StoredProcedure [dbo].[CopyTable]   Script Date: 10/09/2015 09:21:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[CopyTable]

— Input parameters

@inputSourceDatabase varchar(100),

@inputDestDatabase varchar(100),

@inputSourceTableName varchar(100),

@inputDestTableName varchar(100)

AS

BEGIN

— SET NOCOUNT ON added to prevent extra result sets from

— interfering with SELECT statements.

SET NOCOUNT ON;

— Declare the variables to store the values returned by FETCH.

DECLARE     @column_name varchar(100),

@table_catalog varchar(100),

@table_schema varchar(100),

@table_name varchar(100),

@column_name_2 varchar(100),

@table_catalog_2 varchar(100),

@table_schema_2 varchar(100),

@table_name_2 varchar(100),

@ResultScript varchar(max),

@tempTable_name varchar(100),

@EndResultScript varchar(max),

@StartResultScript varchar(max),

@TotalResultScript varchar(max)

DECLARE Table_cursor CURSOR FOR

Select [TABLE_CATALOG],[TABLE_SCHEMA],[TABLE_NAME],[COLUMN_NAME] from information_schema.columns

where

–table_name = ‘Planet s_r_l_$ZZTEST_TABLE’ AND

table_name = @inputSourceTableName AND

column_name <> ‘timestamp’ AND

TABLE_CATALOG = @inputSourceDatabase

OPEN Table_cursor;

SET @StartResultScript = ”

SET @EndResultScript = ”

FETCH NEXT FROM Table_cursor

INTO @table_catalog,@table_schema,@table_name,@column_name;

— Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @ResultScript = CONCAT(@ResultScript,'[‘,@column_name,’],’)

FETCH NEXT FROM Table_cursor

INTO @table_catalog,@table_schema,@table_name,@column_name;

END

–Creo Parte finale della stringa di esecuzione.

SET @ResultScript = LEFT(@ResultScript,LEN(@ResultScript)-1)

SET @EndResultScript =CONCAT(‘) SELECT ‘,@ResultScript, ‘ FROM [‘, @inputSourceTableName,’]’)

CLOSE Table_cursor;

DEALLOCATE Table_cursor;

–Final String building (CONCAT)

SET @StartResultScript = CONCAT(‘INSERT INTO [‘,@inputDestDatabase,’].[dbo].[‘,@inputDestTableName,’](‘)

SET @TotalResultScript = CONCAT(@StartResultScript,@ResultScript,@EndResultScript)

–Verbose Mode

PRINT @TotalResultScript

–Execution Statement

INSERT INTO [Tables Migration Config] ([Source Database],[Dest Database],[Source Table Name],[Dest Table Name], [Exec Script])

values (@inputSourceDatabase,

@inputDestDatabase ,

@inputSourceTableName ,

@inputDestTableName,

@TotalResultScript)

END

2) Create new table “Tables Migration Config”

tableconfig

/****** Object: Table [dbo].[Tables Migration Config]   Script Date: 10/09/2015 09:20:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Tables Migration Config](

[timestamp] [timestamp] NOT NULL,

[Source Database] [varchar](50) NOT NULL,

[Dest Database] [varchar](50) NOT NULL,

[Source Table Name] [varchar](100) NOT NULL,

[Dest Table Name] [varchar](100) NOT NULL,

[Exec Script] [varchar](max) NOT NULL,

CONSTRAINT [Table Fields Name$0] PRIMARY KEY CLUSTERED

(

[timestamp] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]

) ON [Data Filegroup 1] TEXTIMAGE_ON [Data Filegroup 1]

GO

SET ANSI_PADDING OFF

GO

3) Exec Stored Procedure “CopyTable” that recover all fields and write to table “Tables Migration Config

USE DB_SQL

Exec CopyTable ‘SOURCE DB’,’DEST DB’,’Source DB$Customer’,’Destionation DB$Customer’

Parameters

  • Source DB
  • Destination DB
  • Source Table
  • Destination Table

Output is written to video and in table “[Tables Migration Config]” > field “Exec Script

Example of utilization:

— Connect to DB

USE DB

GO

— Delete all data in table (first launch)

TRUNCATE TABLE dbo.[Tables Migration Config]

— Exec Stored Proc “CopyTable”

Exec CopyTable ‘SOURCE DB’,’DEST DB’,’Source Company$Customer’,’Destionation Company$Customer’

GO

–Result on Video (Verbose) and on”EXEC SCRIPT” Field

INSERT INTO [DEST DB].[dbo].[Destionation Company$Customer]([No_],[Name],[Search Name],[Name 2],[Address],[Address 2],[City],[Contact],

[Phone No_],[Telex No_],[Our Account No_],[Territory Code],[Global Dimension 1 Code],[Global Dimension 2 Code],[Chain Name],

[Budgeted Amount],[Credit Limit (LCY)],[Customer Posting Group],[Currency Code],[Customer Price Group],[Language Code],

[Statistics Group],[Payment Terms Code],[Fin_ Charge Terms Code],[Salesperson Code],[Shipment Method Code],[Shipping Agent Code],

[Place of Export],[Invoice Disc_ Code],[Customer Disc_ Group],[Country_Region Code],[Collection Method],[Amount],[Blocked],

[Invoice Copies],[Last Statement No_],[Print Statements],[Bill-to Customer No_],[Priority],[Payment Method Code],

[Last Date Modified],[Application Method],[Prices Including VAT],[Location Code],[Fax No_],[Telex Answer Back],

[VAT Registration No_],[Combine Shipments],[Gen_ Bus_ Posting Group],[Picture],[Post Code],[County],[E-Mail],

[Home Page],[Reminder Terms Code],[No_ Series],[Tax Area Code],[Tax Liable],[VAT Bus_ Posting Group],[Reserve],

[Block Payment Tolerance],[IC Partner Code],[Prepayment %],[Primary Contact No_],[Responsibility Center],

[Shipping Advice],[Shipping Time],[Shipping Agent Service Code],[Service Zone Code],[Allow Line Disc_],[Base Calendar Code],

[Copy Sell-to Addr_ to Qte From],[Int_ on Arrears Code],[Fiscal Code],[Cumulative Bank Receipts],[Banca di Sconto])

SELECT [No_],[Name],[Search Name],[Name 2],[Address],[Address 2],[City],[Contact],[Phone No_],[Telex No_],[Our Account No_],

[Territory Code],[Global Dimension 1 Code],[Global Dimension 2 Code],[Chain Name],[Budgeted Amount],[Credit Limit (LCY)],

[Customer Posting Group],[Currency Code],[Customer Price Group],[Language Code],[Statistics Group],[Payment Terms Code],

[Fin_ Charge Terms Code],[Salesperson Code],[Shipment Method Code],[Shipping Agent Code],[Place of Export],[Invoice Disc_ Code],

[Customer Disc_ Group],[Country_Region Code],[Collection Method],[Amount],[Blocked],[Invoice Copies],[Last Statement No_],

[Print Statements],[Bill-to Customer No_],[Priority],[Payment Method Code],[Last Date Modified],[Application Method],

[Prices Including VAT],[Location Code],[Fax No_],[Telex Answer Back],[VAT Registration No_],[Combine Shipments],

[Gen_ Bus_ Posting Group],[Picture],[Post Code],[County],[E-Mail],[Home Page],[Reminder Terms Code],

[No_ Series],[Tax Area Code],[Tax Liable],[VAT Bus_ Posting Group],[Reserve],[Block Payment Tolerance],

[IC Partner Code],[Prepayment %],[Primary Contact No_],[Responsibility Center],[Shipping Advice],[Shipping Time],

[Shipping Agent Service Code],[Service Zone Code],[Allow Line Disc_],[Base Calendar Code],[Copy Sell-to Addr_ to Qte From],

[Int_ on Arrears Code],[Fiscal Code],[Cumulative Bank Receipts],[Banca di Sconto] FROM [Source Company$Customer]

  • results are visible in Table with:

SELECT * FROM [dbo].[Tables Migration Config]

 

MSDN

  • Also published on Scripting GUY Gallery on MSDN

NAV to NAV SQL Fast Data Migration (with dynamic “INSERT INTO” STATEMENTS)

https://gallery.technet.microsoft.com/NAV-to-NAV-SQL-Fast-Data-4341b7e9#content

Bingo, You are Welcome !

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...