SQL添加到数据库后,如何返回该行自动生成的字节信息
SQL添加到数据库后,如何返回该行自动生成的字节信息
例如:添加的内容是姓名hhb,性别男到数据库中后该行中编号自动生成是23
那么如何在添加后返回编号23呢?(因为添加前不知道自动生成编号23啊)
问题点数：100、回复次数：7Top
1 楼bitsbird（一瓢 在路上...）回复于 2004-05-01 22:23:00 得分 50
根据你添加的条件再select一次,比如说select top 1 Usernumber from table1 order by
Usernumber descTop
2 楼CMIC（大象）回复于 2004-05-01 22:26:28 得分 50
经典问题，用IDENT_CURRENT，在你插入记录后使用下面的语句就可以得到id号
select IDENT_CURRENT('你的表名')Top
3 楼citylamp（Johnson）回复于 2004-05-01 22:58:07 得分 0
我以前是重查数据库的Top
4 楼wangsaokui（无间道ＩＩＩ(终极无间)C#MVP）回复于 2004-05-01 23:10:23 得分 0
@@IDENTITY
Returns the last-inserted identity value.
Syntax
@@IDENTITY
Return Types
numeric
Examples
This example inserts a row into a table with an identity column and uses @@IDENTITY to display the identity value used in the new row.
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'
混点分了
大象的说明如下
IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.
Syntax
IDENT_CURRENT('table_name')
Arguments
table_name
Is the name of the table whose identity value will be returned. table_name is varchar, with no default.
Return Types
sql_variant
Remarks
IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
Examples
This example illustrates the different identity values returned by IDENT_CURRENT, @@IDENTITY, and SCOPE_IDENTITY.
USE pubs
DROP TABLE t6
DROP TABLE t7
GO
CREATE TABLE t6(id int IDENTITY)
CREATE TABLE t7(id int IDENTITY(100,1))
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT
AS
BEGIN
INSERT t7 DEFAULT VALUES
END
GO
--end of trigger definition
SELECT * FROM t6
--id is empty.
SELECT * FROM t7
--id is empty.
--Do the following in Session 1
INSERT t6 DEFAULT VALUES
SELECT @@IDENTITY
/*Returns the value 100, which was inserted by the trigger.*/
SELECT SCOPE_IDENTITY()
/* Returns the value 1, which was inserted by the
INSERT stmt 2 statements before this query.*/
SELECT IDENT_CURRENT('t7')
/* Returns value inserted into t7, i.e. in the trigger.*/
SELECT IDENT_CURRENT('t6')
/* Returns value inserted into t6, which was the INSERT statement 4 stmts before this query.*/
-- Do the following in Session 2
SELECT @@IDENTITY
/* Returns NULL since there has been no INSERT action
so far in this session.*/
SELECT SCOPE_IDENTITY()
/* Returns NULL since there has been no INSERT action
so far in this scope in this session.*/
SELECT IDENT_CURRENT('t7')
/* Returns the last value inserted into t7.*/Top
5 楼bitsbird（一瓢 在路上...）回复于 2004-05-01 23:13:52 得分 0
无间道兄,有时间打电话陪陪嫂子啊,今天过节呐:)Top
6 楼CMIC（大象）回复于 2004-05-01 23:25:07 得分 0
同意楼上的Top
7 楼bitsbird（一瓢 在路上...）回复于 2004-05-01 23:26:06 得分 0
谢谢大象兄同意
^_^Top
