-- =============================================
--
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_MigrateTable'
AND type = 'P')
DROP PROCEDURE sp_MigrateTable
GO
CREATE PROCEDURE sp_MigrateTable
-- sp_MigrateTable '','aggrp', 'stone', 'rm00401', 'docnumbr,rmdtypal'
@DBFrom varchar(50),
@DBTo varchar(50),
@Tablename varchar(255),
@UniqueFields VARCHAR(255)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @firstUnique VARCHAR(255)
declare @fieldName1 varchar(500)
declare @fieldName2 varchar(500)
declare @paramName varchar(255)
declare @fieldType varchar(50)
declare @fieldTypeVB varchar(50)
declare @intTableNameLen int
Declare @vchrSelectList as varchar(7900)
Declare @ProcParamList as TABLE (Paramname varchar(255), RowID int identity)
declare @vchrInsertFieldList as varchar(7900),
@vchrJOIN varchar(7900),
@bIsIdentity bit,
@Tablename2 varchar(255),
@ORDINAL_POSITION int,
@Identity_Column varchar(255)
select
@intTableNameLen = len(@TableName),
@vchrInsertFieldList = '',
@vchrSelectList = '',
@Tablename2 = Replace(@Tablename,'[','')
select
@Tablename2 = Replace(@Tablename2,']','')
declare @ParamList table (ParamName varchar(100) )
insert into @ParamList (ParamName)
select vchr100 from fdd_CommaSepListToTable(@UniqueFields)
PRINT '-- ========================================================================='
print '-- Created ' + convert(varchar(10),getdate(),101) + ' by DynDeveloper.com'
print '-- ========================================================================='
declare curFields CURSOR for
select column_name, column_name2, fieldType, columnTypeVB, column_name3, IsIdentity
from ddv_DALTableColumnMetaData
where table_name = @TableName2
and IsComputed = 0
order by ORDINAL_POSITION
OPEN curFields
FETCH NEXT FROM curFields INTO @fieldname1, @fieldName2, @fieldType, @fieldTypeVB, @paramName, @bIsIdentity
WHILE @@FETCH_STATUS = 0
BEGIN
if @fieldName1 in (select ParamName from @ParamList) begin
set @vchrJOIN = coalesce(@vchrJOIN + ' and ','') + 't.' + @fieldName1 + ' = t2.' + @fieldName1
IF @firstUnique IS NULL BEGIN
SET @firstUnique = @fieldName1
END
end
--build the field lists
if @bIsIdentity = 0 begin
set @vchrInsertFieldList = @vchrInsertFieldList + @fieldName2 + ' ' + ', '
set @vchrSelectList = @vchrSelectList + 't2.' + @fieldName1 + ' , '
end
if @bIsIdentity = 1 begin
set @Identity_Column = @fieldName1
end
--build a sqlhelper param list
FETCH NEXT FROM curFields INTO @fieldname1, @fieldName2, @fieldType,@fieldTypeVB, @paramName, @bIsIdentity
END
CLOSE curFields
DEALLOCATE curFields
-- sp_MigrateTable 'aggrp','stone','RM00401','docnumbr, rmdtypal'
IF @Identity_Column is null begin
raiserror ('No identity column specified',16,2)
end
set @vchrInsertFieldList = left(@vchrInsertFieldList, len(@vchrInsertFieldList) - 1)
declare @intMaxRowID int
select @intMaxRowID = MAX(RowID)
from @ProcParamList
update @ProcParamList set Paramname = left(paramname,len(paramname)-1)
where RowID = @intMaxRowID
set @vchrSelectList = left (@vchrSelectList,len(@vchrSelectList)-1)
-- LEFT JOIN stone..rm00401 t ON t.docnumbr = t2.docnumbr
--WHERE t.docnumbr IS NULL
print 'INSERT INTO ' + @DBTo + '..' + @TableName + ' (' + @vchrInsertFieldList + ')'
print ' SELECT ' + space(len(@TableName)) + '' + @vchrSelectList
PRINT ' FROM ' + @dbfrom + '..' + @tablename + ' t2'
PRINT ' LEFT JOIN ' + @dbto + '..' + @tablename + ' t on ' + @vchrJOIN
PRINT ' WHERE t.' + @firstUnique + ' is null'
go