.::: Insert data table to another table with different Database in SQL Server :::.

 

  A. Without Date

1. Check

select * from teguhth..pembelian
 

2. create table & insert firstime

-- pertama kali insert table
select * into dwh..pembelian_clone from teguhth..pembelian
 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. table after create
USE [dwh]
GO

CREATE TABLE [dbo].[pembelian_clone](
    [KODE_PEMBELIAN] [char](10) NOT NULL,
    [KODE_BARANG] [char](6) NOT NULL,
    [KODE_CUSTOMER] [char](6) NOT NULL,
    [TANGGAL_PEMBELIAN] [date] NULL,
    [JUMLAH_PEMBELIAN] [decimal](4, 0) NULL
) ON [PRIMARY]
GO

B. with Date

1. Check

select *,GETDATE() AS [Date Cron] from teguhth..pembelian;

select *,CAST(GETDATE() AS date) AS [Date Cron]
from teguhth..pembelian

 

2. create table & insert firstime

-- pertama kali insert table
select *,GETDATE() AS [Date Cron] into dwh..pembelian_clone from teguhth..pembelian

3. insert data after create table
-- insert data after create table
insert into dwh..pembelian_clone select *, GETDATE() AS [Date Cron] from teguhth..pembelian;

4. check
select *,GETDATE() AS [Date Cron] from teguhth..pembelian
select * from dwh..pembelian_clone;
 

5. table after create

USE [dwh]
GO

CREATE TABLE [dbo].[pembelian_clone](
    [KODE_PEMBELIAN] [char](10) NOT NULL,
    [KODE_BARANG] [char](6) NOT NULL,
    [KODE_CUSTOMER] [char](6) NOT NULL,
    [TANGGAL_PEMBELIAN] [date] NULL,
    [JUMLAH_PEMBELIAN] [decimal](4, 0) NULL,
    [Date Cron] [datetime] NOT NULL
) ON [PRIMARY]
GO

 

C. using table n join

1. sample query

USE teguhth;
GO

SELECT db_name() as DBName,
    b.NAMA_BARANG,
    s.NAMA_SUPLIER,
    p.TANGGAL_PASOK,
    p.JUMLAH_PASOK
FROM
    barang AS b
JOIN
    pasok AS p ON b.KODE_BARANG = p.KODE_BARANG
JOIN
    suplier AS s ON s.KODE_SUPLIER = p.KODE_SUPLIER;
 

2. Sample create new table

SELECT
    barang.NAMA_BARANG,
    suplier.NAMA_SUPLIER,
    pasok.TANGGAL_PASOK,
    pasok.JUMLAH_PASOK
INTO dwh.dbo.pembelian_joint
FROM barang
JOIN pasok ON barang.KODE_BARANG = pasok.KODE_BARANG
JOIN suplier ON suplier.KODE_SUPLIER = pasok.KODE_SUPLIER;

3. show create table

USE [dwh]
GO

/****** Object:  Table [dbo].[pembelian_joint]    Script Date: 5/5/2025 1:46:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[pembelian_joint](
    [NAMA_BARANG] [varchar](25) NULL,
    [NAMA_SUPLIER] [varchar](30) NULL,
    [TANGGAL_PASOK] [date] NULL,
    [JUMLAH_PASOK] [decimal](4, 0) NULL
) ON [PRIMARY]
GO

4. select table

select db_name() as DBName,* from dwh..pembelian_joint;