Solved

Check if exists in stored procedure with table type

Posted on 2011-03-11
2
679 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

626 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