.::: Create Linked Server MSSQL to use Open Query (to another MSSQL) insert, update, delete :::.

A. Preconfigurasi
1. Konfiguration ODBC Data Source (64)
No Need Configuration ODBC

2. create Linked Server
Server Objects -> Linked Servers -> New Linked Server
General
Linked Server: MSSQL
Server Type: SQL Server
or 
Linked Server: MSSQL\instance
Linked Server: IP_SQLServer\instance

Security (user MSSQL) 
Be made using the seciroty context

Local login : sa
remote login: sa
With password: []

or

Server Options
RPC & RPC Out: True

B. Select Query
1. Normal Query - insert
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-99','Barang - After Migration','BUAH',20);
insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');
insert into customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');
insert into pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','2002-01-01',8);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','2002-05-20',3);
select * from barang where KODE_BARANG ='ELK-99';
select * from suplier where KODE_SUPLIER ='EJ-99';
select * from customer where KODE_CUSTOMER ='J-0099';
select * from pasok where KODE_PASOK ='PAS-E099';
select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

2. Open Query - insert
insert into openquery (MSSQL, 'select KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG from teguhth.dbo.barang') VALUES  ('ELK-99','Barang - After Migration','BUAH',20); 
insert into openquery (MSSQL, 'select KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER from teguhth.dbo.suplier') values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');
insert into openquery (MSSQL, 'select KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER from teguhth.dbo.customer') values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');
insert into openquery (MSSQL, 'select KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK from teguhth.dbo.pasok') values('PAS-E099','ELK-99','EJ-99','2002-01-01',8);
insert into openquery (MSSQL, 'select KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN from teguhth.dbo.pembelian') values('BEL-E099','ELK-99','J-0099','2002-05-20',3);
--or 
insert into openquery (MSSQL, 'select * from teguhth.dbo.barang') VALUES  ('ELK-99','Barang - After Migration','BUAH',20); 
insert into openquery (MSSQL, 'select * from teguhth.dbo.suplier') values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');
insert into openquery (MSSQL, 'select * from teguhth.dbo.customer') values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');
insert into openquery (MSSQL, 'select * from teguhth.dbo.pasok') values('PAS-E099','ELK-99','EJ-99','2002-01-01',8);
insert into openquery (MSSQL, 'select * from teguhth.dbo.pembelian') values('BEL-E099','ELK-99','J-0099','2002-05-20',3);

-- or 
select * from openquery(MSSQL,'select * from teguhth.dbo.barang') where KODE_BARANG ='ELK-99';
select * from openquery(MSSQL,'select * from teguhth.dbo.suplier') where KODE_SUPLIER ='EJ-99';
select * from openquery(MSSQL,'select * from teguhth.dbo.customer') where KODE_CUSTOMER ='J-0099';
select * from openquery(MSSQL,'select * from teguhth.dbo.pasok') where KODE_PASOK ='PAS-E099';
select * from openquery(MSSQL,'select * from teguhth.dbo.pembelian') where KODE_PEMBELIAN ='BEL-E099';
-- or
select * from openquery(MSSQL,'select * from teguhth..barang where KODE_BARANG =''ELK-99''');
select * from openquery(MSSQL,'select * from teguhth..suplier where KODE_SUPLIER =''EJ-99''');
select * from openquery(MSSQL,'select * from teguhth..customer where KODE_CUSTOMER =''J-0099''');
select * from openquery(MSSQL,'select * from teguhth..pasok where KODE_PASOK =''PAS-E099''');
select * from openquery(MSSQL,'select * from teguhth..pembelian where KODE_PEMBELIAN =''BEL-E099''');

C. Update Query
1. Normal Query - update
update barang set nama_barang='Barang OpenQuery' where KODE_BARANG ='ELK-99';
select * from barang

2. Open Query - update
update openquery(MSSQL,'select * from teguhth..barang where KODE_BARANG =''ELK-99''') set nama_barang='Barang OpenQuery'  ;
select * from openquery(MSSQL,'select * from teguhth..barang where KODE_BARANG =''ELK-99''');

D. Delete Query 
1. Normal Query - delete
delete from pembelian where KODE_BARANG ='ELK-99';
delete from pasok where KODE_PASOK ='PAS-E099';
delete from barang where KODE_BARANG ='ELK-99';

2. Open Query - delete
delete from openquery (MSSQL,'select * from teguhth..barang') ;
delete from openquery (MSSQL,'select * from teguhth..barang ') where NAMA_BARANG='KOMPUTER' ;
--foreign key must be delete first before delete primary key
-- table pembelian
delete from openquery (MSSQL,'select * from teguhth..pembelian ') where KODE_BARANG ='ELK-99' ;
-- or
delete from openquery (MSSQL,'select * from teguhth..pembelian where KODE_BARANG =''ELK-99''')  ;
-- table pasok
delete from openquery (MSSQL,'select * from teguhth..pasok ') where KODE_PASOK ='PAS-E099' ;
delete from openquery (MSSQL,'select * from teguhth..pasok where KODE_PASOK =''PAS-E099''') ;
--foreign key must be delete first before delete primary key
delete from openquery (MSSQL,'select * from teguhth..barang ') where KODE_BARANG ='ELK-99' ;
select * from openquery(MSSQL,'select * from teguhth..barang where KODE_BARANG =''ELK-99''');