Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Check if exists in stored procedure with table type

Posted on 2011-03-11
2
Medium Priority
?
681 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 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 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