sqlscope

❶ 如何在sql中插入記錄時返回id(id為自動增長)

insert into [table] values(null) select SCOPE_IDENTITY()
重點在插入後面的select

❷ SQL Server 鏁版嵁搴撳備綍鏌ュ嚭鏈鍚庝竴嬈℃彃鍏ョ殑涓鏉

SCOPE_IDENTITY銆両DENT_CURRENT 鍜 @@IDENTITY
鏄鐩鎬技鐨勫嚱鏁幫紝鍥犱負瀹冧滑閮借繑鍥炴彃鍏ュ埌鏍囪瘑鍒椾腑鐨勫箋
IDENT_CURRENT 涓嶅彈浣滅敤鍩熷拰浼氳瘽鐨勯檺鍒訛紝鑰屽彈闄愪簬鎸囧畾鐨勮〃銆侷DENT_CURRENT
榪斿洖涓轟換浣曚細璇濆拰浣滅敤鍩熶腑鐨勭壒瀹氳〃鎵鐢熸垚鐨勫箋傛湁鍏寵︾粏淇℃伅錛岃峰弬闃匢DENT_CURRENT
(Transact-SQL)銆
SCOPE_IDENTITY 鍜 @@IDENTITY
榪斿洖鍦ㄥ綋鍓嶄細璇濅腑鐨勪換浣曡〃鍐呮墍鐢熸垚鐨勬渶鍚庝竴涓鏍囪瘑鍊箋備絾鏄錛孲COPE_IDENTITY 鍙榪斿洖鎻掑叆鍒板綋鍓嶄綔鐢ㄥ煙涓鐨勫礆紱@@IDENTITY
涓嶅彈闄愪簬鐗瑰畾鐨勪綔鐢ㄥ煙銆
渚嬪傦紝鏈変袱涓琛 T1 鍜 T2錛屽苟涓斿湪 T1 涓婂畾涔変簡 INSERT 瑙﹀彂鍣ㄣ傚綋灝嗘煇琛屾彃鍏 T1 鏃訛紝瑙﹀彂鍣ㄨ嬋鍙戱紝騫跺湪 T2
涓鎻掑叆涓琛屻傝ユ柟妗堟紨紺轟簡涓や釜浣滅敤鍩燂細鍦 T1 涓婄殑鎻掑叆錛屼互鍙婂湪 T2 閫氳繃瑙﹀彂鍣ㄧ殑鎻掑叆銆
鍋囪 T1 鍜 T2 閮芥湁鏍囪瘑鍒楋紝@@IDENTITY 鍜 SCOPE_IDENTITY 灝嗗湪 T1 涓婄殑 INSERT
璇鍙ョ殑鏈鍚庤繑鍥炰笉鍚岀殑鍊箋侤@IDENTITY 灝嗚繑鍥炲湪褰撳墠浼氳瘽涓鐨勪換浣曚綔鐢ㄥ煙鍐呮彃鍏ョ殑鏈鍚庝竴涓鏍囪瘑鍒楃殑鍊箋傝繖鏄鍦 T2
涓鎻掑叆鐨勫箋係COPE_IDENTITY() 灝嗚繑鍥炲湪 T1 涓鎻掑叆鐨 IDENTITY 鍊箋傝繖鏄鍦ㄥ悓涓涓浣滅敤鍩熷唴鍙戠敓鐨勬渶鍚庣殑鎻掑叆銆傚傛灉鍦ㄤ換浣 INSERT
璇鍙ヤ綔鐢ㄤ簬浣滅敤鍩熶腑鐨勬爣璇嗗垪涔嬪墠璋冪敤 SCOPE_IDENTITY() 鍑芥暟錛屽垯璇ュ嚱鏁板皢榪斿洖 Null銆
濡傛灉璇鍙ュ拰浜嬪姟澶辮觸錛屽畠浠浼氭洿鏀硅〃鐨勫綋鍓嶆爣璇嗭紝浠庤屼嬌鏍囪瘑鍒椾腑鐨勫煎嚭鐜頒笉榪炶瘡鐜拌薄銆傚嵆浣挎湭鎻愪氦璇曞浘鍚戣〃涓鎻掑叆鍊肩殑浜嬪姟錛屼篃姘歌繙鏃犳硶鍥炴粴鏍囪瘑鍊箋備緥濡傦紝濡傛灉鍥
IGNORE_DUP_KEY 鍐茬獊鑰屽艱嚧 INSERT 璇鍙ュけ璐ワ紝琛ㄧ殑褰撳墠鏍囪瘑鍊間粛鐒朵細澧炲姞銆

鑺傞夎嚜sqlserver 鏁欑▼ SCOPE_IDENTITY (Transact-SQL)