Monday, October 3, 2022

.::: Archive table 6 months and 24 months SQL Server Using Copy import export write a query to specify the data to transfer :::.



A. Import using ssms
1. create table source

create table pembelian_source(
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN decimal(4),
primary key(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER),
foreign key(KODE_BARANG)references barang(KODE_BARANG),
foreign key(KODE_CUSTOMER)references customer(KODE_CUSTOMER));

2. create table destination
create table pembelian_dest(
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN decimal(4),
primary key(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER),
foreign key(KODE_BARANG)references barang(KODE_BARANG),
foreign key(KODE_CUSTOMER)references customer(KODE_CUSTOMER));
 3. insert data to table source 

insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E001','ELK-01','J-0001','2020-04-15',4);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E002','ELK-01','J-0002','2020-05-15',2);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E003','ELK-01','B-0001','2020-06-15',2);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E004','ELK-01','B-0002','2020-07-15',3);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E005','ELK-02','J-0001','2020-08-15',1);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E006','ELK-02','J-0002','2020-09-15',1);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E007','ELK-02','B-0001','2020-10-15',2);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E008','ELK-02','B-0002','2020-11-15',2);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E009','ELK-03','J-0001','2020-12-15',5);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E010','ELK-03','J-0002','2021-01-15',4);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E011','ELK-03','B-0001','2021-02-15',6);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E012','ELK-03','J-0001','2021-03-15',5);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E013','ELK-04','J-0002','2021-04-15',12);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E014','ELK-04','B-0002','2021-05-15',15);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E015','ELK-01','J-0001','2021-06-15',3);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E016','ELK-01','J-0001','2021-07-15',4);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E017','ELK-01','J-0002','2021-08-15',2);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E018','ELK-01','B-0001','2021-09-15',2);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E019','ELK-01','B-0002','2021-10-15',3);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E020','ELK-02','J-0001','2021-11-15',1);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E021','ELK-02','J-0002','2021-12-15',1);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E022','ELK-02','B-0001','2022-01-15',2);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E023','ELK-02','B-0002','2022-02-15',2);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E024','ELK-03','J-0001','2022-03-15',5);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E025','ELK-03','J-0002','2022-04-15',4);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E026','ELK-03','B-0001','2022-05-15',6);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E027','ELK-03','J-0001','2022-06-15',5);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E028','ELK-04','J-0002','2022-07-15',12);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E029','ELK-04','B-0002','2022-08-15',15);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E030','ELK-04','B-0002','2022-09-15',15);

4. check pembelian_source
select @@SERVERNAME as [ServerName], db_name() as [DBName], * from pembelian_source;


5.  check query 6 months ago from source / to insert
select @@SERVERNAME as [ServerName], db_name() as [DBName],* from pembelian_source
where TANGGAL_PEMBELIAN >= DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;
 


6. check query range 24 months < x < 6 months ago from source / to insert

select @@SERVERNAME as [ServerName], db_name() as [DBName], * from pembelian_source
where TANGGAL_PEMBELIAN BETWEEN  DATEADD(MONTH , -24, GETDATE()) AND  DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;


select @@SERVERNAME as [ServerName], db_name() as [DBName], * from pembelian_source
where TANGGAL_PEMBELIAN >= DATEADD(MONTH , -24, GETDATE()) AND  TANGGAL_PEMBELIAN < DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;


7. import using ssms with query (6 months )
  
 
 
 
 




8. check query 6 months ago from Source & destination
select @@SERVERNAME as [ServerName], db_name() as [DBName],* from pembelian_source
where TANGGAL_PEMBELIAN >= DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;

select @@SERVERNAME as [ServerName], db_name() as [DBName], * from pembelian_dest
where TANGGAL_PEMBELIAN >= DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;
 


9. import using ssms with query (24 months < x < 6 months)
 

 
10. check query range 24 months < x < 6 months ago from source & destination

select @@SERVERNAME as [ServerName], db_name() as [DBName], * from pembelian_source
where TANGGAL_PEMBELIAN BETWEEN  DATEADD(MONTH , -24, GETDATE()) AND  DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;

select @@SERVERNAME as [ServerName], db_name() as [DBName], * from pembelian_dest
where TANGGAL_PEMBELIAN BETWEEN  DATEADD(MONTH , -24, GETDATE()) AND  DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;
 



select @@SERVERNAME as [ServerName], db_name() as [DBName], * from pembelian_source
where TANGGAL_PEMBELIAN >= DATEADD(MONTH , -24, GETDATE()) AND  TANGGAL_PEMBELIAN < DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;


select @@SERVERNAME as [ServerName], db_name() as [DBName], * from pembelian_dest
where TANGGAL_PEMBELIAN >= DATEADD(MONTH , -24, GETDATE()) AND  TANGGAL_PEMBELIAN < DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;


B.
1. insert data to table source
-- tambahan mungkin
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E031','ELK-01','J-0001','2022-09-01',3);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E032','ELK-01','J-0001','2022-08-01',3);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E033','ELK-01','J-0001','2022-07-01',4);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E034','ELK-01','J-0002','2022-06-01',2);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E035','ELK-01','B-0001','2022-05-01',2);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E036','ELK-01','B-0002','2022-04-01',3);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E037','ELK-02','J-0001','2022-03-01',1);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E038','ELK-02','J-0002','2022-02-01',1);    
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E039','ELK-02','B-0001','2022-01-01',2);
insert into pembelian_source(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E040','ELK-02','B-0002','2021-12-01',2);

2. import using ssms with query
select * from pembelian_source
where TANGGAL_PEMBELIAN >= DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;
 
select * from pembelian_dest
where TANGGAL_PEMBELIAN BETWEEN  DATEADD(MONTH , -24, GETDATE()) AND  DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;

3. check query 6 months ago from destination  
select @@SERVERNAME as [ServerName], db_name() as [DBName],* from pembelian_source
where TANGGAL_PEMBELIAN >= DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;

select @@SERVERNAME as [ServerName], db_name() as [DBName], * from pembelian_dest
where TANGGAL_PEMBELIAN >= DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;
 



4. check query range 24 months < x < 6 months ago from source

select @@SERVERNAME as [ServerName], db_name() as [DBName],* from pembelian_source
where TANGGAL_PEMBELIAN BETWEEN  DATEADD(MONTH , -24, GETDATE()) AND  DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;

select @@SERVERNAME as [ServerName], db_name() as [DBName],* from pembelian_dest
where TANGGAL_PEMBELIAN >= DATEADD(MONTH , -24, GETDATE()) AND  TANGGAL_PEMBELIAN < DATEADD(MONTH , -6, GETDATE())
order by TANGGAL_PEMBELIAN desc;
 


5. delete

delete from pembelian_source where KODE_pembelian in('BEL-E031','BEL-E032','BEL-E033','BEL-E034','BEL-E035','BEL-E036','BEL-E037','BEL-E038','BEL-E039','BEL-E040');






Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts