SQL ServerÈçºÎ¿çʵÀý·ÃÎÊÊý¾Ý¿â
ÔÚÎÒÃÇÈÕ³£Ê¹ÓÃSQL ServerÊý¾Ý¿âʱ£¬¾³£Óöµ½ÐèÒªÔÚʵÀýInstance01ÖпçʵÀý·ÃÎÊInstance02ÖеÄÊý¾Ý¡£ÀýÈçÔÚ×öÊý¾ÝÇ¨ÒÆÊ±£¬ÈçÏÂÓï¾ä:
insert into Instance01.DB01.dbo.Table01
select * from Instance02.DB01.dbo.Table01
ÆÕͨÇé¿öÏ£¬ÕâÑù×öÊDz»ÔÊÐíµÄ£¬ÒòΪSQL ServerĬÈϲ»¿ÉÒÔ¿çʵÀý·ÃÎÊÊý¾Ý¡£½â¾ö·½°¸ÊÇʹÓô洢¹ý³Ìsp_addlinkedserver½øÐÐʵÀý×¢²á¡£
sp_addlinkedserverÔÚMSDNÖе͍ÒåΪ:
sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
ÀýÈ磺ÔÚInstance01ʵÀýÖУ¬Ö´ÐÐÈçÏÂSQLÓï¾ä
EXEC sp_addlinkedserver ‘Instance02’ //ֻдµÚÒ»¸ö²ÎÊý¼´¿É£¬Ä¬ÈÏÇé¿öÏ£¬×¢²áµÄÊÇSQL ServerÊý¾Ý¿â£¬ÆäËû²ÎÊýÓ÷¨Ïê¼ûMSDN¡£
Èç¹ûÄãµÄÁ½¸öʵÀýÔÚͬһ¸öÓòÖУ¬ÇÒInstance01ÓëInstance02Óй²Í¬µÄÓòµÇ½Õʺţ¬ÄÇô¾¹ýÉÏÃæµÄ×¢²áºó£¬Ç°ÃæµÄinsertÓï¾ä¾Í¿ÉÒÔÖ´ÐÐÁË¡£·ñÔò£¬»¹ÐèÒª¶Ô×¢²áµÄÔ¶³ÌʵÀý½øÐеǽÕʺÅ×¢²á£¬ÔÚInstance01ʵÀýÖУ¬Ö´ÐÐÈçÏÂSQLÓï¾ä
EXEC sp_addlinkedsrvlogin 'InstanceName','true' //ʹÓü¯³ÉÈÏÖ¤·ÃÎÊÔ¶³ÌʵÀý
»òÕß EXEC sp_addlinkedsrvlogin 'InstanceName','false','TJVictor,'sa','Password1' //ʹÓÃWindowsÈÏÖ¤·ÃÎÊÔ¶³ÌʵÀý£¬µ±Óû§ÒÔTJVictorÓû§µÇ½Instance01ʵÀý·ÃÎÊInstance02ʱ£¬Ä¬ÈϰÑTJVictorÓ³Éä³Ésa£¬ÇÒÃÜÂëΪPassword1
¾¹ý sp_addlinkedserverʵÀý×¢²áºÍsp_addlinkedsrvloginµÇ½ÕÊ»§×¢²áºó£¬¾Í¿ÉÒÔÔÚInstance01ÖÐÖ±½Ó·ÃÎÊInstance02ÖеÄÊý¾Ý¿âÊý¾ÝÁË¡£
Èç¹û»¹ÎÞ·¨·ÃÎÊ£¬Çë¼ì²é±¾»úDNSÊÇ·ñ¿ÉÒÔ½âÎöÔ¶³ÌÊý¾Ý¿âµÄʵÀýÃû¡£Èç¹ûÎÞ·¨½âÎö£¬¿ÉÒÔÔÚEXEC sp_addlinkedserver ‘Instance02’ÖаÑInstance02»»ÎªIP£¬»òÕßÔÚhostsÎļþÖУ¬×Ô¼º½¨Á¢ÏàÓ¦DNSÓ³Éä¡£
ÏÂÃæÁоټ¸¸ö¿çʵÀýÊý¾Ý¿â·ÃÎʵĴ洢¹ý³ÌºÍÊÓͼ¡£
´æ´¢¹ý³ÌÃû/ÊÓͼÃû
×÷ÓÃ
¾ÙÀý
sp_addlinkedserver
Ïà¹ØÎĵµ£º
create table tb (ptoid int,proclassid int,proname varchar(10))
insert tb
select 1,1,'Ò·þ1'
union all
select 2,2,'Ò·þ2'
union all
select 3,3,'Ò·þ3'
union all
select 4,3,'Ò·þ4'
union all
select 5,2,'Ò·þ5'
union all
select 6,2,'Ò·þ6'
union all
select 7,2,'Ò·þ7'
union all
select 8 ......
µÚÒ»Ìõ£º
select * from where field in (1,2,3) ÕâÖÖд·¨Ó¦¸ÃÉáÆúÁË£¬¶øÓ¦¸ÃÕâÑùд£º
select * from table where field = 1 or field = 2 or field = 3
ÎªÊ²Ã´ÄØ£¿
·²ÊÂÖ»ÓÐ×ö¹ý²ÅÖªµÀ£¬×Ô¼º½¨¸ö±í£¬²åÈë10WÌõ²âÊÔÊý¾Ý£¬ÓÃsql²âÊÔһϱãÖª£¬ÒòΪ¹¤×÷±ãÀû£¬ÎÒÔÚ²úÆ·»úºÍÍâÍø²âÊÔ»ú¶¼ÒѾ²âÊÔ¹ý£ ......
ÎÒÃǶ¼ÖªµÀÔÚOracleÖÐÿÌõSQLÓï¾äÔÚÖ´ÐÐ֮ǰ¶¼ÐèÒª¾¹ý½âÎö£¬ÕâÀïÃæÓÖ·ÖΪÈí½âÎöºÍÓ²½âÎö¡£ÔÚOracleÖдæÔÚÁ½ÖÖÀàÐ͵ÄSQLÓï¾ä£¬Ò»ÀàΪ
DDLÓï¾ä£¨Êý¾Ý¶¨ÒåÓïÑÔ£©£¬ËûÃÇÊÇ´ÓÀ´²»»á¹²ÏíʹÓõģ¬Ò²¾ÍÊÇÿ´ÎÖ´Ðж¼ÐèÒª½øÐÐÓ²½âÎö¡£»¹ÓÐÒ»Àà¾ÍÊÇDMLÓï¾ä£¨Êý¾Ý²Ù×ÝÓïÑÔ£©£¬ËûÃÇ»á¸ù¾ ......
-- create by zh
-- n ÊÇ×÷ÎïµÄʱ¼ä,x ÊÇÏ£ÍûÔÚ¼¸µã³ÉÊì,·µ»Ø²¥ÖÖµÄʱ¼ä
with t as
(
select 64 n,9 x from dual union all
select 64 n,13 x from dual union all
select 64 n,17 x from dual union all
select 64 n,20 x from dual
)
select '³ÉÊìʱ¼ä:' || lpad(to_char(n),4,' ' ......
1 ·ÀÖ¹sql×¢Èëʽ¹¥»÷(¿ÉÓÃÓÚUI²ã¿ØÖÆ£© #region ·ÀÖ¹sql×¢Èëʽ¹¥»÷(¿ÉÓÃÓÚUI²ã¿ØÖÆ£©
2
3 /**/ ///
4 /// ÅжÏ×Ö·û´®ÖÐÊÇ·ñÓÐSQL¹¥»÷´úÂë
5 ///
6 /// ´«ÈëÓû§Ìá½»Êý¾Ý
7 /// ......