{$page}

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.

code_formatsql

exec Copy_Table 'StoreData', 'GL', 'NewBackupData', 'GL'

exec Copy_Table 'StoreData', 'GL', 'StoreData', 'GLBackup-2009-12-25'

code

code_formatsql

– 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);

1. - 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

code

• Entered : 12/2009
• Version : All

• Backlinks include_pagepage_componentbacklinks