Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

I need a stored procedure to copy a table with indexes

I would like 2 session variable to be used if possible:
@TableName
@DBname

There are tables in DB1 that I need to copy to @DBname.
I want a procedure where I just have to modify @TableName and @DBname at the top of the procedure, and then Execute.
I then want the procedure to copy DB1.@TableName to @DBname.@TableName.
The only way I know to do this would be to create @DBname.@TableName using the CREATE STATEMENT from DB1.@TableName to preserve indexes/comments/other characteristics of the table.  Then use an 'insert' to copy the records.

In any event - can someone write me the procedure?

With Regards-
Sam
0
SAbboushi
Asked:
SAbboushi
1 Solution
 
UmeshMySQL Principle Technical Support EngineerCommented:
Try this...
DELIMITER $$

DROP PROCEDURE IF EXISTS `uCopyTable`$$

CREATE PROCEDURE uCopyTable(IN NewSchema VARCHAR(50),IN OldSchema VARCHAR(50),IN OldTable VARCHAR(50))
BEGIN 
  
        SET @tmp_sql= CONCAT("CREATE TABLE ",NewSchema,'.',OldTable,' LIKE ',OldSchema,'.',OldTable); 
        PREPARE s1 FROM @tmp_sql; 
        EXECUTE s1; 
        DEALLOCATE PREPARE s1; 
  
        SET @tmp_sql= CONCAT("INSERT INTO ",NewSchema,'.',OldTable,' SELECT * FROM ',OldSchema,'.',OldTable); 
        PREPARE s1 FROM @tmp_sql; 
        EXECUTE s1; 
        DEALLOCATE PREPARE s1; 

END$$

DELIMITER ;

### Usage...

call uCopyTable('DBname','Db1','tableName')

Open in new window

0
 
SAbboushiAuthor Commented:
Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now