Solved

Check if exists in stored procedure with table type

Posted on 2011-03-11
2
678 Views
Last Modified: 2012-05-11
Hi,

I have a stored procedure that inserts from a table type like the code below:

How can I check if xxxx already exists and instead Update that row?

I use this for inserting messages through BizTalk.

Best Regards
ALTER PROCEDURE [dbo].[InsertStuff]
(
	@parStuff AS dbo.StuffTableType READONLY
)
AS
SET NOCOUNT ON
BEGIN
DECLARE 
@id int

INSERT INTO dbo.Stuff(
	[xxx],[xxxx],[xxxxx]   
SELECT [xxx],[xxxx],[xxxxx]
FROM @parStuff;

Open in new window

0
Comment
Question by:mattegol
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35108031
you have to do that based on the key field(s):
 ALTER PROCEDURE [dbo].[InsertStuff] ( @parStuff AS dbo.StuffTableType READONLY ) AS SET NOCOUNT ON BEGIN -- to update existing rows, xxx is the primary key field UPDATE s SET xxxx = p.xxxx , xxxxx = p.xxxxx FROM dbo.Stuff s JOIN @parStuff p ON p.xxx = s.xxx INSERT INTO dbo.Stuff ( [xxx],[xxxx],[xxxxx] ) SELECT [xxx],[xxxx],[xxxxx] FROM @parStuff p LEFT JOIN dbo.Stuff s --- LEFT join plus below on s.xxx = p.xxx WHERE s.xxx IS NULL -- left join plus this will make you will only insert "new" rows here 

Open in new window



0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35108343
If not exists (select 1 from dbo.Stuff    where field =   [xxx] )
INSERT INTO dbo.Stuff(
        [xxx],[xxxx],[xxxxx]  
SELECT [xxx],[xxxx],[xxxxx]
FROM @parStuff;
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 88
SQL Server maintenance plan 8 61
find SQL job run average duration 24 75
SQL parsing XML works but want to do it another way 4 47
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question