------------- 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
		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
----------------------------------------------------------------------------------------------------------------------------------