SQLServer ÖбíµÄ×ÔÔö³¤ÁÐ
1.½¨±í(Create table)ʱʹÓà IDENTITY (SEED,INCREMENT)
ÆäÖÐSEEDÊÇÆðʼֵ£¬INCREMENTÊÇÔöÁ¿¡£
Àý£º
CREATE TABLE mytable1
(
[user_id] BIGINT NOT NULL
IDENTITY(1, 1) ,
[user_mail] NVARCHAR(255)
)
ÃüÁîÒѳɹ¦Íê³É¡£
2.Ð޸ıí(Alter table)ÁеÄ×ÔÔö³¤
SQLServer²»ÔÊÐíÐÞ¸ÄÒѾ´æ´¢µÄ×ÔÔö³¤ÁУ¬µ«ÊÇ¿ÉÒÔΪһ¸öûÓÐ×ÔÔö³¤±êʶÁеıíÌí¼Ó×ÔÔö³¤ÁÐ
Àý£º
DROP TABLE mytable1 -- ɾ³ý¸Õ²Å´´½¨µÄ±í
CREATE TABLE mytable1
(
[user_mail] NVARCHAR(255)
)
ALTER TABLE mytable1 ADD [user_id] BIGINT IDENTITY(1,1)
ÃüÁîÒѳɹ¦Íê³É¡£
3.ÊÖ¶¯²åÈë×ÔÔö³¤×ֶεÄÖµ
Àý£º
SET IDENTITY_INSERT mytable1 ON -- ¿ªÆô ÊÖ¶¯
INSERT INTO mytable1
( [user_id], [user_mail] )
VALUES ( 10, N'oyi319@email.com' )
SET IDENTITY_INSERT mytable1 OFF -- ¹Ø±Õ ÊÖ¶¯
(1 ÐÐÊÜÓ°Ïì)
4.¸ü¸Äµ±Ç°SEEDÖµ
a.ÓÃDBCC CHECKIDENTÖØÐÂÉèÖñíµÄ±êʶÁеÄÖµ
Àý£º
DBCC CHECKIDENT(mytable1, RESEED, 4)
¼ì²é±êʶÐÅÏ¢: µ±Ç°±êʶֵ '10'£¬µ±Ç°ÁÐÖµ '4'¡£
DBCC Ö´ÐÐÍê±Ï¡£Èç¹û DBCC Êä³öÁË´íÎóÐÅÏ¢£¬ÇëÓëϵͳ¹ÜÀíÔ±ÁªÏµ¡£
b.²»ÖØÉ赱ǰֵ£¬Ö»ÊDz鿴µ±Ç°ÖµµÄ±¨¸æ
Àý£º
DBCC CHECKIDENT(mytable1, NORESEED)
¼ì²é±êʶÐÅÏ¢: µ±Ç°±êʶֵ '4'£¬µ±Ç°ÁÐÖµ '10'¡£
DBCC Ö´ÐÐÍê±Ï¡£Èç¹û DBCC Êä³öÁË´íÎóÐÅÏ¢£¬ÇëÓëϵͳ¹ÜÀíÔ±ÁªÏµ¡£
Ïà¹ØÎĵµ£º
ÒÉÎÊ£º
1£¬ sqlserverÀïÃæÖ´ÐÐ xp_cmdshell
exec @error=xp_cmdshell 'cmdstr......'
·µ»ØÖµÊÇʲôֵ£¿dosÃüÁîµÄ´íÎó£¿Ê²Ã´ÑùµÄ´íÎó¿ÉÒÔÆË×½µ½£¿
±ÈÈç:
DECLARE @error int
EXEC @error=master.dbo.xp_cmdshell 'bcp CM_DWHSend.dbo.WK_CM_DWHSend_Master in "D:\Sales\DWH_Onl\2010042_SENDMEMM.csv" -n -t, - ......
·ÖÁ½ÖÖÇé¿ö£º
1¡¢Í¨¹ý½Å±¾·½Ê½£¬2¡¢µ¼Èëµ¼³ö·½Ê½
µ«²»Í¬°æ±¾µÄSqlServer,ÔÚÕâ·½Ãæ²¢²»Ïàͬ¡£
sql2000¶ÔÊý¾Ý¿â¶ÔÏóÖ´ÐÐÉú³É½Å±¾Ê±£¬¾¡¹Ü¶¼Ñ¡Éϸ÷¸öÌõ¼þ£¬µ«¶ÔÓÚ±í¶ÔÏó²¢²»ÄÜ´´½¨Ö÷¼ü
¿ÉÒÔͨ¹ýµ¼Èëµ¼³ö·½Ê½£¬ÔÚµ¼ÈëÄ¿±êÑ¡ÔñÄ¿±êsqlserverºó£¬Ñ¡ÔñÖ»µ¼³ö¶ÔÏó½á¹¹¾ÍºÃÁË¡£
sql2005¶ÔÊý¾Ý¿â¶ÔÏóÖ´ÐÐÉú³É½Å±¾Ê±£¬¿ÉÒÔ´´½¨Ö÷¼ ......
http://www.tongyi.net/article/20031101/200311013786.shtml
ËùνµÄÊý¾Ý´«Ê䣬ÆäʵÊÇÖ¸SQLServer·ÃÎÊAccess¡¢Excel¼äµÄÊý¾Ý¡£
ΪʲôҪ¿¼Âǵ½Õâ¸öÎÊÌâÄØ£¿
ÓÉÓÚÀúÊ·µÄÔÒò£¬¿Í»§ÒÔÇ°µÄÊý¾ÝºÜ¶à¶¼ÊÇÔÚ´æÈëÔÚÎı¾Êý¾Ý¿âÖУ¬ÈçAcess¡¢Excel¡¢Foxpro¡£ÏÖÔÚϵͳÉý¼¶¼°Êý¾Ý¿â·þÎñÆ÷ÈçSQLServer¡¢ORACLEºó£¬¾³£ÐèÒª·ÃÎÊÎı¾Êý ......