July 14, 2016 at 10:01 am
In a t-sql 2012 database table, I need to be able to determine if a row exists in a table, update, or delete the row from the table. This will be t-sql 2012 that runs behind a .net web application. Thus would you tell me how I would do the following:
1. If row does not exist, do an insert.
2. If a row exists, update all columns in the selected table.
3. If want to delete columns in a table, just want the data columns contain empty values.
July 14, 2016 at 10:17 am
What you're looking for is a Merge, for the insert/update. Here is an example that should help you:
Create table #Test (CustomerID int,
Name varchar(20),
City varchar(20));
Insert into #Test
Values (1, 'Steve','London');
Select *
from #Test;
MERGE #Test as Target
Using (Select 1, 'Steve', 'Colchester') as source (CustomerID, Name, City)
On (Target.CustomerID = source.CustomerID)
when matched then
update SET Name = source.name,
city = source.city
when not matched then
Insert ( CustomerID, Name, City)
Values (source.CustomerID, source.Name, Source.City);
Select *
from #test;
MERGE #Test as target
Using (Select 2, 'Donna', 'Leeds') as source (CustomerID, Name, City)
On (Target.CustomerID = source.CustomerID)
when matched then
update SET Name = source.name,
city = source.city
when not matched then
Insert ( CustomerID, Name, City)
Values (source.CustomerID, source.Name, Source.City);
Select *
from #Test;
Drop table #Test;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply