Rajneesh Noonia

————- Create helper functions —————————–

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N‘[dbo].[Mig_ImportTable]‘) AND OBJECTPROPERTY(id, N‘IsProcedure’) = 1)
DROP PROCEDURE [dbo].[Mig_ImportTable]
GO

CREATE PROC dbo.Mig_ImportTable @DATABASE SYSNAME, @TABLE SYSNAME AS
        SET NOCOUNT ON

        DECLARE @COLUMN SYSNAME
        DECLARE @SQL NVARCHAR(4000)
        DECLARE @colSQL NVARCHAR(4000)
        DECLARE @IsIdentity BIT
        DECLARE @IsTableIdentity BIT

        SET @colSQL = ”
        SET @SQL=”
        SET @IsTableIdentity = 0 –false
        SET @IsIdentity = 0 –false

        PRINT ‘Table Migration Started for :’ + @TABLE + ‘ in ‘ + @DATABASE

        DECLARE curMoveDown CURSOR
        LOCAL FORWARD_ONLY
        OPTIMISTIC FOR
        SELECT column_name,COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, ‘IsIdentity’)AS IsIdentity FROM information_schema.COLUMNS
        WHERE UPPER(table_name ) = UPPER(@TABLE)

        OPEN curMoveDown FETCH NEXT FROM curMoveDown INTO @COLUMN,@IsIdentity
        WHILE @@FETCH_STATUS = 0
        BEGIN
                SET @colSQL = @colSQL + ‘[' + @COLUMN + '],’
                SET @IsTableIdentity = @IsTableIdentity | @IsIdentity
                IF (@IsTableIdentity = 1) BREAK
                FETCH NEXT FROM curMoveDown INTO @COLUMN,@IsIdentity
        END
        CLOSE curMoveDown
        DEALLOCATE curMoveDown

        IF(LEN(@colSQL)>1)
        BEGIN
                SET @colSQL = LEFT(@colSQL,LEN(@colSQL) – 1)

                IF(@IsTableIdentity = 1) SET @SQL = @SQL + ‘SET IDENTITY_INSERT ‘ + @TABLE + ‘ ON ‘
                SET @SQL = @SQL + ‘     ALTER TABLE ‘ + @TABLE + ‘ DISABLE TRIGGER ALL ‘
                SET @SQL = @SQL + ‘     ALTER TABLE ‘ + @TABLE + ‘ NOCHECK CONSTRAINT ALL ‘
                SET @SQL = @SQL + ‘     TRUNCATE TABLE ‘ + @TABLE + ‘  ’
                IF(@IsTableIdentity = 1)
                        SET @SQL = @SQL + ‘     INSERT INTO ‘ + @TABLE + ‘ (‘ +  @colSQL + ‘) SELECT ‘+  @colSQL + ‘ FROM ‘ + ‘[' + @DATABASE + '].[dbo].[' + @TABLE + '] ‘
                ELSE
                        SET @SQL = @SQL + ‘     INSERT INTO ‘ + @TABLE + ‘ SELECT * FROM ‘ + ‘[' + @DATABASE + '].[dbo].[' + @TABLE + '] ‘
                IF(@IsTableIdentity = 1) SET @SQL = @SQL + ‘    SET IDENTITY_INSERT ‘ + @TABLE + ‘ OFF ‘
                SET @SQL = @SQL + ‘     ALTER TABLE ‘ + @TABLE + ‘ ENABLE TRIGGER ALL ‘
                SET @SQL = @SQL + ‘     ALTER TABLE ‘ + @TABLE + ‘ CHECK CONSTRAINT ALL ‘

                EXEC sp_executesql @SQL
                PRINT ‘Table migrated :’ + @TABLE
        END
        ELSE
                PRINT ‘No Column found :’ + @TABLE + ‘ (SQL = ‘ + @colSQL + ‘)’

        PRINT ‘Table Migration finished for :’ + @TABLE
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N‘[dbo].[Mig_ImportDatabase]‘) AND OBJECTPROPERTY(id, N‘IsProcedure’) = 1)
DROP PROCEDURE [dbo].[Mig_ImportDatabase]
GO

CREATE PROC dbo.Mig_ImportDatabase @Source SYSNAME AS   

        DECLARE @SERVER SYSNAME
        DECLARE @INSTANCE SYSNAME
        DECLARE @FULLNAME SYSNAME
        DECLARE @DBNAME SYSNAME
        DECLARE @cmd varchar(1000)
        DECLARE @SQL Nvarchar(1000)
        DECLARE @TABLE Nvarchar(100)
        DECLARE @Result INT

        SET NOCOUNT ON

        CREATE TABLE [#Mig_FailedTables] ( [name] SYSNAME NOT NULL ) ON [PRIMARY]

        SELECT @SERVER = CONVERT(SYSNAME, SERVERPROPERTY(‘servername’))
        SELECT @INSTANCE = IsNull(”,CONVERT(SYSNAME, SERVERPROPERTY(‘InstanceName’)))
        SELECT @DBNAME = DB_NAME()

        IF(Len(@INSTANCE)>0) SET @FULLNAME = @SERVER + ‘\’ + @INSTANCE
        ELSE SET @FULLNAME = @SERVER

        Print ‘Migration started FROM DATABASE [‘ + @SOURCE + ‘] TO DATABASE [‘ + @DBNAME + ‘] ON server  ‘ + @FULLNAME

        –Cursor to loop throw all tables of Target database (current database)
        DECLARE CurTables CURSOR LOCAL FORWARD_ONLY
        OPTIMISTIC FOR
        SELECT NAME from dbo.sysobjects where OBJECTPROPERTY(id, N’IsUserTable‘) = 1 order by Name

        OPEN CurTables FETCH NEXT FROM CurTables INTO @Table
        WHILE @@FETCH_STATUS = 0
        BEGIN
                Print ‘Migrating TABLE ‘ + @Table
                SET @cmd = ‘ECHO Exec [Mig_ImportTable] ”‘ + @Source + ‘”,”‘ + @Table + ‘”  > DbMig.sql‘
                EXEC @Result = master..xp_cmdshell @cmd, no_output

                SET @cmd = ‘ECHO GO  >> DbMig.sql‘
                EXEC @Result =  master..xp_cmdshell @cmd, no_output

                SET @cmd = ‘ECHO @ECHO OFF  > DbMig.cmd‘
                EXEC @Result =  master..xp_cmdshell @cmd, no_output

                SET @cmd = ‘ECHO osql -E -b -S "’ + @FULLNAME +’" -d "’ + @DBNAME +’" -i "DbMig.sql" >> DbMig.cmd‘
                EXEC @Result =  master..xp_cmdshell @cmd, no_output

                SET @cmd = ‘ECHO EXIT ERRORLEVEL >> DbMig.cmd‘
                EXEC @Result =  master..xp_cmdshell @cmd, no_output

                SET @cmd = ‘CMD /c "DbMig.cmd>>DbMig.Log"‘
                EXEC @Result = master..xp_cmdshell @cmd, no_output
                IF (@Result <> 0)
                        Insert into #Mig_FailedTables (Name) values (@Table)
                FETCH NEXT FROM CurTables INTO @Table

        END
        CLOSE CurTables
        DEALLOCATE CurTables
        Print ‘Migration Finished..‘
        SELECT Name as [Failed Tables] FROM #Mig_FailedTables
        DROP TABLE #Mig_FailedTables

GO

————- Migration of data starts from here ——————

———————————————————————————————————————————-

USE TargetDatabase                                              — Target Database      ** Change This
Exec Mig_ImportDatabase ‘SourceDatabase‘                        — Source Database      ** Change This – Import Complete database
EXEC Mig_ImportTable ‘SourceDatabase‘,’SpecificTableName‘       – SpecificTableName    ** Change This – Import Single Table
———————————————————————————————————————————-