Tabel kopiëren (MySQL)

Regelmatig wil ik tabellen kopiëren van de ene tabel naar de andere. Bv. als ik met een datawarehouse werk, dat uit drie losse databases bestaat (org, op & export). Hoe doe je dat?

Het probleem

Kopiëren van de inhoud is triviaal. Het probleem betreft de overige kenmerken van zo'n tabel:

• PK
• Indexes
• Foreign keys? - Hier heb ik nog geen ervaring mee.

Command line

Via de command line kan dit met één commando:

mysqldump bron_db_naam tabel_naam | mysql doel_db_naam

Voordelen:

• Kenmerken zoals pk en indexes worden meegenomen
• Kort statement.

Nadelen:

• Meestal zoek ik een oplossing in SQL. Hetzelfde geldt voor oplossingen mbv. PHPMyAdmin: Die schijnt dit truukje heel goed te kunnen, maar dat is niet wat ik zoek.

SQL - Create + select

Wat ik tot voor kort gebruikte (sep. 2019):

drop table if exists abc;
create table abc
select * from other_db.abc;

Maar dan komen PK's en indexes niet mee. NN (velden die niet nul mogen zijn), komen wel mee (getest: okt. 2019).

SQL - create like + select

Vermoedelijk de oplossing:

• Dankzij deze post (okt. 2019)
• Alleen worden foreign constrains niet meegenomen - heb ik tot op heden nog niet mee te maken gehad):

CREATE DEFINER=`root`@`localhost` PROCEDURE `fetch_table_from_import`
(
   in   source_table_in		tinytext,
   in	destination_table_in	tinytext
)
BEGIN
# 
# Fetch table (structure + content) from database "bal_dwh_import" to current database
################################################################################################################################################
#
# * NOTE: Current destination table is being dropped (without backup)
# * The name of the source-database is hardcoded: "BAL_DWH_IMPORT"
# * Current database is used as destination-database


################################################################################################################################################
# Drop existing destination table
################################################################################################################################################
#
set @ddl=concat("drop table if exists ",destination_table_in);

prepare stmt from @ddl;
execute stmt;
deallocate prepare stmt;


################################################################################################################################################
# Instantiate new table (structure)
################################################################################################################################################
#
set @ddl=concat
(
   "create table ", destination_table_in, " ",
   "like bal_dwh_import.",source_table_in
);

prepare stmt from @ddl;
execute stmt;
deallocate prepare stmt;


################################################################################################################################################
# Insert content
################################################################################################################################################
#
set @ddl=concat
(
   "insert into ", destination_table_in, " ",
   "select * from bal_dwh_import.", source_table_in
);

prepare stmt from @ddl;
execute stmt;
deallocate prepare stmt;

END

SQL - Geavanceerd

Breid bovenstaande sproc uit met code, die de brontabel analyseert tav. PK's en indexes. Pas dat vervolgens toe op de brontabel. Dat moet niet al te moeilijk zijn

Zie ook

• Database klonen (MySQL)

Bronnen

• https://stackoverflow.com/questions/2748008/copying-a-table-in-mysql-and-including-the-primary-key