.::: Insert data table to another table with different Database in MariaDB :::.

 

A. Without Date

1. Check

select * from teguhth.pembelian;
 
2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN CHAR(10) NOT NULL,
    KODE_BARANG CHAR(6) NOT NULL,
    KODE_CUSTOMER CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN DECIMAL(4,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
3. insert data after create table
-- insert data after create table
insert into dwh.pembelian_clone select * from teguhth.pembelian;

4. check
select * from teguhth.pembelian;
select * from dwh.pembelian_clone;
 

5. pertama kali insert table

CREATE TABLE dwh.pembelian_create as SELECT * FROM teguhth.pembelian;

B. with Date

1. Check

SELECT *, NOW() AS `Date Cron` FROM teguhth.pembelian;
SELECT *, CAST(NOW() AS DATE) AS `Date Cron`
FROM teguhth.pembelian;
 

2. table after create

CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN CHAR(10) NOT NULL,
    KODE_BARANG CHAR(6) NOT NULL,
    KODE_CUSTOMER CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN DECIMAL(4,0) DEFAULT NULL,
    `Date Cron` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. insert data after create table
-- insert data after create table
INSERT INTO dwh.pembelian_clone
SELECT *, NOW() AS `Date Cron` FROM teguhth.pembelian;

4. check
SELECT *, NOW() AS `Date Cron` FROM teguhth.pembelian;
select * from dwh.pembelian_clone;
 

5. pertama kali insert table

CREATE TABLE dwh.pembelian_create as SELECT *, NOW() AS `Date Cron` FROM teguhth.pembelian;

 

C. using table n join

1. sample query

use teguhth
select barang.NAMA_BARANG,suplier.NAMA_SUPLIER,pasok.TANGGAL_PASOK,pasok.JUMLAH_PASOK from barang,suplier,pasok where barang.KODE_BARANG=pasok.KODE_BARANG and suplier.KODE_SUPLIER=pasok.KODE_SUPLIER;
 

2. Sample create new table

create table dwh.pembelian_joint as select barang.NAMA_BARANG,suplier.NAMA_SUPLIER,pasok.TANGGAL_PASOK,pasok.JUMLAH_PASOK from barang,suplier,pasok where barang.KODE_BARANG=pasok.KODE_BARANG and suplier.KODE_SUPLIER=pasok.KODE_SUPLIER;

3. show create table

show create table dwh.pembelian_joint;

CREATE TABLE `pembelian_joint` (
  `NAMA_BARANG` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `NAMA_SUPLIER` varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `TANGGAL_PASOK` date DEFAULT NULL,
  `JUMLAH_PASOK` decimal(4,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

4. select table

select * from dwh.pembelian_joint;