SQL Select or Insert return ID
Alright so a quick SQL question here(using sql-server-2008).
I have a mapping table names
with the following columns
ID
DisplayName
What I want to do is first SELECT [ID] FROM [names] WHERE [DisplayName] = 'chuck';
BUT, if the name 'chuck' doesn't exist in the database, I would like to create it, and return the auto incremented ID
.
I was wondering if SQL had some built in way of doing this in an easy way, or if I have to go the long route?
long route being something like this
SELECT COUNT(ID) AS count, ID FROM names WHERE DisplayName='chuck'
IF(count > 0)
SELECT ID as ReturnID;
ELSE
BEGIN
INSERT INTO names(DisplayName) values('chuck');
SELECT scope_identity() as ReturnID;
END
I didn't test that last statement, but I assume the long way would be something like that. If there is no built in way, I'd appreciate it if someone could simply correct that statement(as I'm sure it isn't completely correct).
You should take care about transactions as well:
set XACT_ABORT on
begin tran
declare @ID int
select @ID = ID from names with (holdlock, updlock) WHERE DisplayName='chuck'
if @@rowcount = 0
begin
INSERT INTO names(DisplayName) values('chuck');
set @ID = scope_identity();
end
select @ID as ReturnID;
commit tran
Note the usage of table hints - holdlock and updlock . They prevent another thread from executing exactly the same query and creating the row a second time. For more information look for isolation, synchronization, deadlocks, concurrent updates.
I would do:
IF (NOT EXISTS(SELECT null from names where DisplayName='Chuck'))
INSERT INTO Names (DisplayName) Values ('Chuck')
SELECT ID as ReturnID FROM Names where DisplayName='Chuck'
Doesn't save much though
链接地址: http://www.djcxy.com/p/57784.html上一篇: 按周分组,并填补'缺失'周
下一篇: SQL选择或插入返回ID