SQLServer µ¼Èëµ¼³ö
· ±¾ÎÄÌÖÂÛÁËÈçºÎͨ¹ýTransact-SQLÒÔ¼°ÏµÍ³º¯ÊýOPENDATASOURCEºÍOPENROWSETÔÚͬ¹¹ºÍÒì¹¹Êý¾Ý¿âÖ®¼ä½øÐÐÊý¾ÝµÄµ¼Èëµ¼³ö£¬²¢¸ø³öÁËÏêϸµÄÀý×ÓÒÔ¹©²Î¿¼¡£
1. ÔÚSQL ServerÊý¾Ý¿âÖ®¼ä½øÐÐÊý¾Ýµ¼Èëµ¼³ö
(1).ʹÓÃSELECT INTOµ¼³öÊý¾Ý
ÔÚSQL ServerÖÐʹÓÃ×î¹ã·ºµÄ¾ÍÊÇͨ¹ýSELECT INTOÓï¾äµ¼³öÊý¾Ý£¬SELECT INTOÓï¾äͬʱ¾ß±¸Á½¸ö¹¦ÄÜ£º¸ù¾ÝSELECTºó¸úµÄ×Ö¶ÎÒÔ¼°INTOºóÃæ¸úµÄ±íÃû½¨Á¢¿Õ±í£¨Èç¹ûSELECTºóÊÇ*, ¿Õ±íµÄ½á¹¹ºÍfromËùÖ¸µÄ±íµÄ½á¹¹Ïàͬ£©£»½«SELECT²é³öµÄÊý¾Ý²åÈëµ½Õâ¸ö¿Õ±íÖС£ÔÚʹÓÃSELECT INTOÓï¾äʱ£¬INTOºó¸úµÄ±í±ØÐëÔÚÊý¾Ý¿â²»´æÔÚ£¬·ñÔò³ö´í£¬ÏÂÃæÊÇÒ»¸öʹÓÃSELECT INTOµÄÀý×Ó¡£
¼ÙÉèÓÐÒ»¸ö±ítable1£¬×Ö¶ÎΪf1(int)¡¢f2(varchar(50))¡£
SELECT * INTO table2 from table1
ÕâÌõSQLÓïµÄÔÚ½¨Á¢table2±íºó£¬½«table1µÄÊý¾ÝÈ«²¿²åÈëµ½table1Öеģ¬»¹¿ÉÒÔ½«*¸ÄΪf1»òf2ÒÔ±ãÏòÊʵ±µÄ×Ö¶ÎÖвåÈëÊý¾Ý¡£
SELECT INTO²»½ö¿ÉÒÔÔÚͬһ¸öÊý¾ÝÖн¨Á¢±í£¬Ò²¿ÉÒÔÔÚ²»Í¬µÄSQL ServerÊý¾Ý¿âÖн¨Á¢±í¡£
USE db1
SELECT * INTO db2.dbo.table2 from table1
ÒÔÉÏÓï¾äÔÚÊý¾Ý¿âdb2Öн¨Á¢ÁËÒ»¸öËùÓÐÕßÊÇdboµÄ±ítable2£¬ÔÚÏòdb2½¨±íʱµ±Ç°µÇ¼µÄÓû§±ØÐëÓÐÔÚdb2½¨±íµÄȨÏÞ²ÅÄܽ¨Á¢table2¡£ ʹÓÃSELECT INTOҪעÒâµÄÒ»µãÊÇSELECT INTO²»¿ÉÒÔºÍCOMPUTEÒ»ÆðʹÓã¬ÒòΪCOMPUTE·µ»ØµÄÊÇÒ»×é¼Ç¼¼¯£¬Õ⽫»áÒýÆð¶þÒâÐÔ£¨¼´²»ÖªµÀ¸ù¾ÝÄĸö±í½¨Á¢¿Õ±í£©¡£
(2).ʹÓÃINSERT INTO ºÍ UPDATE²åÈëºÍ¸üÐÂÊý¾Ý
SELECT INTOÖ»Äܽ«Êý¾Ý¸´ÖƵ½Ò»¸ö¿Õ±íÖУ¬¶øINSERT INTO¿ÉÒÔ½«Ò»¸ö±í»òÊÓͼÖеÄÊý¾Ý²åÈëµ½ÁíÍâÒ»¸ö±íÖС£
INSERT INTO table1 SELECT * from table2
»ò
INSERT INTO db2.dbo.table1 SELECT * from table2
µ«ÒÔÉϵÄINSERT INTOÓï¾ä¿ÉÄÜ»á²úÉúÒ»¸öÖ÷¼ü³åÍ»´íÎó£¨Èç¹ûtable1ÖеÄij¸ö×Ö¶ÎÊÇÖ÷¼ü£¬Ç¡ÇÉtable2ÖеÄÕâ¸ö×Ö¶ÎÓеÄÖµºÍtable1µÄÕâ¸ö×ֶεÄÖµÏàͬ£©¡£Òò´Ë£¬ÉÏÃæµÄÓï¾ä¿ÉÒÔÐÞ¸ÄΪ
INSERT INTO table1 -- ¼ÙÉè×Ö¶Îf1ΪÖ÷¼ü
SELECT * from table2 WHERE
NOT
Ïà¹ØÎĵµ£º
SqlserverÖÐCompute By×Ó¾äÓ÷¨·ÖÎö
2007Äê04ÔÂ20ÈÕ ÐÇÆÚÎå 08:05
ʹÓÃCompute by×Ó¾äºÍÐÐͳ¼Æº¯Êý£¨count,sum,max,avg,minµÈ£©£¬¿ÉÒÔͳ¼ÆÅÅÐòÖнá¹ûÍêÈ«ÏàͬµÄÁУ¬Í³¼ÆÖµ×÷Ϊ²éѯ½á¹ûÒÔ¸½¼ÓÐеÄÐÎʽÏÔʾ£¬
Óï·¨£ºCompute avg|count|max|min|sum by ±í´ïʽ
1¡¢¾ÙÒ»¸öÀý×Ó
±ÈÈçÓÐÒ»¸öÊý¾Ý±í£º±íÃûΪperson,Èý¸ö×ֶηֱð ......
SQLServer ģʽ¾ÍÊÇ£¬°ÑSession ´æ·ÅÔÚ SQL Server Êý¾Ý¿âÀעÒâ²»ÊÇ Oracle £¬¶¯¶¯½ÅÖº¶¼Äܲµ½ÔÒòÀ²£©£¬ÏÂÃ濪ʼ˵Ã÷Ò»ÏÂÉèÖõľßÌå²½Ö裺
1¡¢ Æô¶¯Ïà¹ØµÄÊý¾Ý¿â·þÎñ£¨Èçͼ£©
ÔËÐÐSQL Server ·þÎñ¹ÜÀíÆ÷ → Æô¶¯ SQL Server £¨×îºÃÉèΪ¿ª»ú×Ô¶¯ÔËÐУ© ......
Èç¹ûÄã¾³£Óöµ½ÏÂÃæµÄÎÊÌ⣬Äã¾ÍÒª¿¼ÂÇʹÓÃSQL ServerµÄÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾äÁË£º
SQL³õѧÕß¡£
¾³£Íü¼Ç³£ÓõÄDML»òÊÇDDL SQL Óï¾ä¡£
ÔÚ¶àÈË¿ª·¢Î¬»¤µÄSQLÖУ¬Ã¿¸öÈ˶¼ÓÐ×Ô¼ºµÄSQLÏ°¹ß£¬Ã»ÓÐÒ»Ì×ͳһµÄ¹æ·¶¡£
ÔÚSQL Server Management StudioÖУ¬ÒѾ¸ø´ó¼ÒÌṩÁ˺ܶೣÓõÄÏÖ³ÉSQL¹æ·¶Ä£°å¡£
SQL Server Management ......
SQLSERVER ´óÊý¾ÝÁ¿²åÈëÃüÁ
BULK INSERTÊÇSQLSERVERÖÐÌṩµÄÒ»Ìõ´óÊý¾ÝÁ¿µ¼ÈëµÄÃüÁËüÔËÓÃDTS(SSIS)µ¼ÈëÔÀí£¬¿ÉÒÔ´Ó±¾µØ»òÔ¶³Ì·þÎñÆ÷ÉÏÅúÁ¿µ¼ÈëÊý¾Ý¿â»òÎļþÊý¾Ý¡£ÅúÁ¿²åÈëÊÇÒ»¸ö¶ÀÁ¢µÄ²Ù×÷£¬ÓŵãÊÇЧÂʷdz£¸ß¡£È±µãÊdzöÏÖÎÊÌâºó²»¿ÉÒԻعö¡£
¡¡¡¡BULK INSERTÊÇÓÃÀ´½«ÍⲿÎļþÒÔÒ»ÖÖÌض¨µÄ¸ñʽ¼ÓÔ ......