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:

• The intersection of all fields found in the two tables are moved. (i.e., The field must be found in both tables.)
• It will NOT replace any records already found in the destination table (based on the ID)
• You can specify a custom WHERE clause that will be used to filter the source table and only move certain records.

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

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.

--------------------------------------------------------------------------------------
-- 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);
 
    -- Create a list of columns common in both the source and destination tables
    -- 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
 
      -- 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
 
      -- When there is text larger than 8K, SQL will terminate with an error
      -- This disabled that (not desired, but the alternate is less desireable)
      --
      SET ANSI_WARNINGS OFF
      -- Print @S
      -- Print ''
      Print 'Inserting Records into '+@DestDatabase+@DestTableName
      EXECUTE(@S);
 
      SET ANSI_WARNINGS ON
 
      -- 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;
--------------------------------------------------------------------------------------

• Entered : 12/2009
• Version : All