将数据插入带有标识列的表中 [英] insert data into table with identity column
本文介绍了将数据插入带有标识列的表中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我有以下列的表: -
userid(int,pk,identity),用户名(nvarchar(50))
以下程序代码:
ALTER PROCEDURE dbo.ManageUsers
(
@username nvarchar(50)
)
AS
INSERT INTO用户(用户名)值(@username)
结束
返回
我不能使用标识列将数据插入此表
但如果isidentity选项设置为false并尝试手动插入userid,则表示运行良好
有什么问题?
解决方案
忽略来自asp.net前端的任何可能的连接问题;存储过程中END之后的RETURN将给出语法错误。所以不会跑。
如果您正在使用查询分析器/ SQL Server Management Studio,则突出显示一个SQL块并点击< ctrl-f5>将为您提供该块的语法检查。可以节省很多时间。
试过这个,它在SQL2005上运行正常....
创建 表用户
(
ID int identity
约束 PK_users primary key (ID),
[name] nvarchar ( 50 )不 null
约束 DF_username 默认( ' ')
)
insert < span class =code-keyword> into [users]([name]) values (' sheila')
选择 * 来自用户
创建 procedure adduser(< span class =code-sdkkeyword> @ name nvarchar ( 50 ))
as
开始
将 插入 [用户]([名称])值( @ name )
return
end
exec adduser ' jim'
选择 * 来自用户
使用< a href =http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx> SET IDENTITY_INSERT [ ^ ] OFF / ON以在标识列中插入值。
试试这个:
ALTER 程序 dbo.ManageUsers
(
@ username nvarchar ( 50 ),
@ userid int
)
AS
SET IDENTITY_INSERT 用户 ON
INSERT INTO 用户(userid,username)值( @ userid , @ username )
SET IDENTITY_INSERT 用户 OFF
END
- 返回
你可以参考
- Amit
为此,当您创建用户表时,只需指定主键id和set identity seed的增量为是,之后创建过程如下:
创建过程InsertUSerDetails
(
@username nvarchar(50)
)
as
begin
插入用户值(@username);
结束
---如果你想尝试
hi all,
I have table with following columns :-
userid (int , pk, identity) , username (nvarchar(50))
the following code of procedure :
ALTER PROCEDURE dbo.ManageUsers ( @username nvarchar(50) ) AS INSERT INTO users (username) Values (@username) END RETURN
I can't insert data to this table with the identity column
but if isidentity option set to false and try to insert userid manually , it worked well
what's problem ??
解决方案
Ignoring any possible problems with the connection from the asp.net front-end; the RETURN following the END in the stored procedure will give you a syntax error. So won't run.
If you're using Query Analyser/SQL Server Management Studio then highlighting a block of SQL and hitting <ctrl-f5> will give you a syntax check on that block. Can save a lot of time.
Just tried this and it works fine on SQL2005....
create table users ( ID int identity constraint PK_users primary key (ID), [name] nvarchar(50) not null constraint DF_username default('') ) insert into [users] ([name]) values('sheila') select * from users create procedure adduser( @name nvarchar(50) ) as begin insert into [users] ([name]) values(@name) return end exec adduser 'jim' select * from users
Use SET IDENTITY_INSERT[^] OFF/ON to insert values in identity column.
Try this:
ALTER PROCEDURE dbo.ManageUsers ( @username nvarchar(50), @userid int ) AS SET IDENTITY_INSERT users ON INSERT INTO users (userid, username) Values (@userid, @username) SET IDENTITY_INSERT users OFF END --RETURN
You can refer
--Amit
For that when you create user table just assign the primary key of id and set identity seed and increment as a "Yes" and after that create procedure as like:
Create procedure InsertUSerDetails
(
@username nvarchar(50)
)
as
begin
Insert into users values(@username);
end
---Its work fine if you want to try
这篇关于将数据插入带有标识列的表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文