select a record, then either update or insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select a record, then either update or insert

Author

Topic

jpegbert
Starting Member

2 Posts

Posted - 2009-06-15 : 13:15:44

Is there a way using a single stored procedure to select a record with a given key, then - if that record exists - update it, or to insert a new record if it does not exist? Or will this require multiple procedures? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-15 : 13:20:57

yup..its possible. the code will be something like


CREATE PROC DataEntry
@Key int,

... other fields
AS

IF NOT EXISTS (SELECT 1 FROM YourTable WHERE PK=@Key)
BEGIN
 INSERT INTO YourTable (PK,...) VALUES (@Key,...)
END
ELSE
BEGIN
 UPDATE YourTable
 SET Field1=@Val1,
 ....
 WHERE PK=@Key
END
GO

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-15 : 13:23:13

single procedure is enough

syntax is some thing like this


create proc usp_test
(
	@key int,
	@a   varchar(30),
	@b   int
)
as
set nocount on
begin

	if exists ( select 1 from tablename where [key] = @key )
	begin
			
			update tablename
			set a = @a,
				b= @b
			where 
				[key] = @key
	end
	else 
	begin
	
			insert into tablename
				(
					a, b
				)
			values
				( 
					@a , @b
				)
	end

end
set nocount off

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-15 : 13:24:09

You can also flip it around and try to update first:

CREATE PROC DataEntry
@Key int,

... other fields
AS

UPDATE YourTable
SET Field1=@Val1,
....
WHERE PK=@Key

IF @@ROWCOUNT = 0
BEGIN
 INSERT INTO YourTable (PK,...) VALUES (@Key,...)
END
GO

jpegbert
Starting Member

2 Posts

Posted - 2009-06-15 : 14:01:24

Thanks for the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-15 : 14:04:07

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources