Table of Contents

{$page}

Explanation of SQL

This SQL query creates a stored procedure that can be used to copy values from one table into another even if the two databases are not identical. This is helpful is the field layout of the source and destination table are different, for example. Or if not all of the fields in one table are found in the other.

Notes:

Sample Usage

code_formatsql

exec Insert_Into_Table 'BackupData', 'TempPC', 'StoreData', 'PostalCodeTaxClass'

exec Insert_Into_Table 'BackupData', 'PostalCodeTaxClass', 'StoreData', 'PostalCodeTaxClass', 'ModifiedDate > 12/1/2009 '

code

Risk of Data Corruption if Run Improperly

High. You are updating the data in the Destination Database. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

SQL

code_formatsql

– Section: Insert_Into_Table Stored Procedure

–

– This section defines a stored procedure that inserts the NEW data in working table

– into the destination database.

–

– Other Notes:

– * Only new data is inserted.

– * A custom field list is generated for the working and destination table so that

– the order of the fields does NOT need to match.

CREATE PROCEDURE Insert_Into_Table 

      @SourceDatabase varchar(255),
      @SourceTableName varchar(50),
      @DestDatabase varchar(255),
      @DestTableName varchar(50),
      @CustomWhereClause varchar(max) = ''

AS

BEGIN 

  SET @DestDatabase = @DestDatabase+'.dbo.';
  SET @SourceDatabase = @SourceDatabase+'.dbo.';
  DECLARE @S VarChar(max);
  1. - Create a list of columns common in both the source and destination tables
  1. - Sorted in the order found in the destination table
  Declare @ColumnNames varchar(max);
  SET @ColumnNames = NULL
  BEGIN TRY
    CREATE TABLE #ColumnNames (ColumnName Varchar(255), ColumnIndex int);
    SET @S =
      'INSERT INTO #ColumnNames '+
      'Select * FROM '+
      '( '+
      '  SELECT A.[name] AS ColumnName, ' +
      '         A.[colid] as ColumnIndex ' +
      '  FROM '+@DestDatabase+'syscolumns A ' +
      '  join '+@SourceDatabase+'syscolumns B ' +
      '  ON A.[name] = B.[name] ' +
      '  WHERE  ' +
      '   A.id in  ' +
      '    (SELECT id FROM '+@DestDatabase+'sysobjects ' +
      '     WHERE type = ''U'' ' +
      '     AND [NAME] = '''+@DestTableName+''') ' +
      '   and B.id in  ' +
      '    (SELECT id FROM '+@SourceDatabase+'sysobjects ' +
      '     WHERE type = ''U'' ' +
      '     AND [NAME] = '''+@SourceTableName+''') ' +
      ') Temp ' +
      'ORDER BY ColumnIndex ';
    Execute(@S);
    SELECT @ColumnNames = COALESCE(@ColumnNames + ',', '') + '[' + ColumnName + '] '
    FROM #ColumnNames
    ORDER BY ColumnIndex
  1. - Insert the Table Contents
    SET @S=
            'INSERT INTO ' + @DestDatabase+@DestTableName + ' ('+@ColumnNames+' ) ' +
                'SELECT '+@ColumnNames+' FROM '+ @SourceDatabase+@SourceTableName + ' ' +
                'WHERE ID > 0 AND ID NOT IN (SELECT ID FROM '+@DestDatabase+@DestTableName+') ';
    IF (Coalesce(@CustomWhereClause, '')  '')
      SET @S = @S + ' AND '+@CustomWhereClause
  1. - When there is text larger than 8K, SQL will terminate with an error
  1. - This disabled that (not desired, but the alternate is less desireable)
  1. -
    SET ANSI_WARNINGS OFF
  1. - Print @S
  1. - Print ''
    Print 'Inserting Records into '+@DestDatabase+@DestTableName
    EXECUTE(@S);
    SET ANSI_WARNINGS ON
  1. - Clean up after ourselves
    DROP TABLE #ColumnNames;
  END TRY
  BEGIN CATCH
    PRINT '-------------------------';
    PRINT 'Oops!  An Error Occurred.';
    PRINT
      '  Message: '+ Coalesce(ERROR_MESSAGE(),'NULL');
    PRINT
      '  Error Number: '+ Coalesce(Cast(ERROR_NUMBER() AS VarChar(20)),'NULL') +
      '  Severity: '+ Coalesce(Cast(ERROR_SEVERITY() AS VarChar(20)),'NULL') +
      '  State: '+ Coalesce(Cast(ERROR_STATE() AS VarChar(20)),'NULL') +
      '  Procedure: '+ Coalesce(Cast(ERROR_PROCEDURE() AS VarChar(50)),'NULL') +
      '  Line: '+ Coalesce(Cast(ERROR_LINE() AS VarChar(20)),'NULL');
    PRINT '  Column Names: '+coalesce(@ColumnNames, 'NULL');
    PRINT '  Last SQL Run: '+coalesce(@S, 'NULL');
    PRINT '-------------------------';
  END CATCH

END;

code

Version Information