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×Ô¶¯×÷Òµ±¸·Ý
1¡¢´ò¿ªSQL Server Management Studio
2¡¢Æô¶¯SQL Server´úÀí
3¡¢µã»÷×÷Òµ->н¨×÷Òµ
4¡¢"³£¹æ"ÖÐÊäÈë×÷ÒµµÄÃû³Æ
5¡¢Ð½¨²½Ö裬ÀàÐÍÑ¡T-SQL£¬ÔÚÏÂÃæµÄÃüÁîÖÐÊäÈëÏÂÃæÓï¾ä
DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR(19),getdate(),120)
set @strPath=replace(@ ......
1.Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)¡¡¡¡
¡¡¡¡ SQLSERVERµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬Òò´Ëfrom×Ó¾äÖÐдÔÚ×îºóµÄ±í£¨»ù´¡±ídriving table£©½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏ£¬±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í£¬µ±SQLSERVER´¦Àí¶à¸ö±íʱ£¬»áÔËÓÃÅÅÐò¼°ºÏ²¢µÄ·½Ê½Á ......
Èç¹ûÄã¾³£Óöµ½ÏÂÃæµÄÎÊÌ⣬Äã¾ÍÒª¿¼ÂÇʹÓÃSQL ServerµÄÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾äÁË£º
SQL³õѧÕß¡£
¾³£Íü¼Ç³£ÓõÄDML»òÊÇDDL SQL Óï¾ä¡£
ÔÚ¶àÈË¿ª·¢Î¬»¤µÄSQLÖУ¬Ã¿¸öÈ˶¼ÓÐ×Ô¼ºµÄSQLÏ°¹ß£¬Ã»ÓÐÒ»Ì×ͳһµÄ¹æ·¶¡£
ÔÚSQL Server Management StudioÖУ¬ÒѾ¸ø´ó¼ÒÌṩÁ˺ܶೣÓõÄÏÖ³ÉSQL¹æ·¶Ä£°å¡£
SQL Server Management ......
--TOP n ʵÏÖµÄͨÓ÷ÖÒ³´æ´¢¹ý³Ì(ת×Ô×Þ½¨)
CREATE PROC sp_PageView
@tbname sysname, --Òª·ÖÒ³ÏÔʾµÄ±íÃû
@FieldKey nvarchar(1000), --ÓÃÓÚ¶¨Î»¼Ç¼µÄÖ÷¼ü(Ωһ¼ü)×Ö¶Î,¿ÉÒÔÊǶººÅ·Ö¸ôµÄ¶à¸ö×Ö¶Î
@PageCurrent int=1, --ÒªÏÔʾµÄÒ³Âë
@PageSize int=10, - ......