Good Afternoon Everyone
I am working with the scenario of transferring client data from one database to another, this is something that is done very rarely here.
So I've decided to create a stored proc that processes each table in turn, the table name is passed to a SPROC I have called "dbo.BMSClientDataTransferGetColumnNames" and it is supposed to return a SQL Command based on Identity insert, exclude computed columns.
The Sproc code is as follows: -
ALTER PROCEDURE [dbo].[BMSClientDataTransferGetColumnNames]
@Destination VarChar(500),
@Source VarChar(500),
@Table VarChar(500),
@WithWhere Char(1)='N',
@WhereClause VarChar(125)=NULL,
@SQLCommandOutput VarChar(max) OUTPUT
AS
BEGIN
DECLARE
@Check tinyint,
@IdentityCheck tinyint,
@ColumnList VarChar(3000),
@ColumnName VarChar(255),
@StringLen int,
@TableName VarChar(255)
SET @Check =(SELECT COUNT() FROM sys.tables WHERE [name]=@Table)
IF @Check=0
BEGIN
SET @SQLCommandOutput=(SELECT'SELECT ['+@Table+'] is not a table, check the name of the Table')
GOTO EndProcess
END
SELECT c.name,c.is_identity,c.is_computed INTO #Columns FROM sys.columns c INNER JOIN sys.tables t ON t.principal_id=c.object_id WHERE t.[name]=@Table
SELECT @IdentityCheck =(SELECT COUNT() from #Columns WHERE is_identity=1)
DECLARE Build_CURS CURSOR FOR SELECT name FROM #Columns WHERE is_computed = 0
OPEN Build_CURS
FETCH NEXT FROM Build_CURS INTO @ColumnName
WHILE @@FETCH_STATUS=0
BEGIN
SET @ColumnList=(SELECT @ColumnList+@ColumnName+',')
FETCH NEXT FROM Build_CURS INTO @ColumnName
END
CLOSE Build_CURS
DEALLOCATE Build_CURS
SET @StringLen =LEN(@ColumnList)-1
SET @ColumnList=LEFT(@ColumnList,@StringLen)
SET @TableName=(SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables WHERE name=@Table)
SET @SQLCommandOutput=(SELECT 'INSERT INTO '+@Destination+'.'+@TableName+'
(
'+@ColumnList+'
)
SELECT
'+@ColumnList+'
FROM '+@Source + '.'+@TableName)
IF @WithWhere='Y' SET @SQLCommandOutput=(SELECT @SQLCommandOutput + ' '+@WhereClause)
IF @IdentityCheck>0
BEGIN
SET @SQLCommandOutput =(SELECT 'SET IDENTITY_INSERT '+@TableName+' ON;
'+@SQLCommandOutput+'
SET IDENTITY_INSERT '+@TableName+' OFF;')
END
EndProcess:
SELECT @SQLCommandOutput RETURN
END
And I'm executing as follows
declare @SQLCommand nvarchar(max)
EXEC @SQLCommand=dbo.BMSClientDataTransferGetColumnNames
@Destination='DestinationDB',
@Source ='[ServerName].[SourceDB]',
@Table='PermissionsProfiles',
@WithWhere='Y',
@WhereClause='WHERE ID NOT IN (SELECT ID FROM dbo.[PermissionsProfiles])',
@SQLCommandOutput=@SQLCommand OUTPUT
SELECT @SQLCommand
However when executed only NULL values are being returned