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

Nog twee aanvullende oplossingen, die beiden aanvullende SQL-code in de sproc vereisen:

• Analyseer de brontabel tav. PK's en indexes. Pas dat vervolgens toe op de brontabel
• Gebruik hiervoor maatwerk-code per tabel. Niet zo ver gezocht in mijn situatie, want de bron-database is de SSoT voor een dwh.

Zie ook

• Database klonen (MySQL)

Bronnen

• https://stackoverflow.com/questions/2748008/copying-a-table-in-mysql-and-including-the-primary-key
• https://tableplus.com/blog/2018/11/how-to-duplicate-a-table-in-mysql.html