This stored procedure is used to create a copy of a table in another (or the same) database. It assumes the table is in the general Cyrious format and resets the modified dates. An index is also created on the ID field.

High. The target table is created and modified in this query. 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.

Sample Use
EXEC Copy_Table 'StoreData', 'GL', 'NewBackupData', 'GL'
EXEC Copy_Table 'StoreData', 'GL', 'StoreData', 'GLBackup-2009-12-25'
--------------------------------------------------------------------------------------
-- Section: Copy_Table Stored Procedure
--
-- This section defines a stored procedure that copies a table
-- from the Source database to the working table.
--
-- Other Notes:
--   * ModifiedBy fields are NULLed.
--   * ModifiedDate is set to the current date and time.
--------------------------------------------------------------------------------------
CREATE PROCEDURE Copy_Table
        @SourceDatabasePath VARCHAR(255),
        @SourceTableName VARCHAR(50),
        @DestDatabasePath VARCHAR(255),
        @DestTableName VARCHAR(50)
 AS
BEGIN
    SET @SourceDatabasePath = @SourceDatabasePath+'.dbo.';
    SET @DestDatabasePath = @DestDatabasePath+'.dbo.';
 
    IF OBJECT_ID(@DestDatabasePath+@DestTableName) IS NOT NULL
      EXECUTE('DROP TABLE '+@DestDatabasePath+@DestTableName);
 
    -- Copy the Table
    EXECUTE
    (
              'SELECT * INTO '+ @DestDatabasePath+@DestTableName +' FROM '+@SourceDatabasePath+@SourceTableName+' WHERE ID > 0 '+
              '; '+
              'UPDATE '+ @DestDatabasePath+@DestTableName+' '+
              'SET StoreID = -1, ModifiedByUser = NULL, ModifiedByComputer = NULL, ModifiedDate = GetDate(), SeqID = 0 ' +
              '; '+
              'CREATE INDEX My'+@DestTableName+'_IDIndex ON '+@DestDatabasePath+@DestTableName+' (ID ASC)  '+
              '; '
    )
END
  • Entered : 12/2009
  • Version : All
You could leave a comment if you were logged in.