August 18, 2011 at 9:36 am
I am currently inserting records to database by passing them from c# side(by looping through C#list)
SqlCommand cmd = new SqlCommand("USP_WriteModifieddata", connection);
cmd.CommandType = CommandType.StoredProcedure;
foreach (PropertyInfo propertyInfo in bct.GetProperties())
{
HERe I am sending params for each record
}
In the Stored Proc shown below I am directly inserting to DB. But I want to check before inserting if that record already exists in DB. If exists I should update it and if not exist insert it. Can any one please advice me on this...
CREATE PROCEDURE [dbo].[USP_WriteModifieddata]
@IdVARCHAR(5000) ,
@NameNVARCHAR(4000), etc....
AS
BEGIN
INSERT INTO dbo.tbldata(Id,Name,.etc...)
VALUES(@Id,@Name,etc....)
August 18, 2011 at 9:40 am
You can use the merge statement if you are on SQL Server 2008 or
--Try to update the record
Update dbo.tblData
Set Name = @Name
WHERE ID = @id
--If the row wasn't updated, it dosn't exist so insert it.
If @@ROWCOUNT = 0
BEGIN
INSERT INTO dbo.tbldata(Id,Name,.etc...)
VALUES(@Id,@Name,etc....)
END
August 18, 2011 at 12:18 pm
I am using this way
IF EXISTS (SELECT * FROM tbldata WHERE Id='@Id')
BEGIN
UPDATE dbo.tbldata
SET Name=@Name,ShortDescription=@ShortDescription,etc...
WHERE Id='@Id'
END
ELSE
BEGIN
INSERT INTO dbo.tbldata(Id,
Name,ShortDescription, etc...)
VALUES(@Id,@Name, @ShortDescription, )
But its trying to insert even if the particular id exists and throwing primary key error.
It should only insert if that particular id doesnt exist and update if exist.
Any help is appreciated!!1
August 18, 2011 at 12:30 pm
The difference in your way and the way I stated above is .... yours will ALWAYS run two queries and mine will only run two queries if the item needs to be added. I don't know what the overhead is but that may or may not make a performance difference that matters.
Why is your @ID variable in single quotes?
Change Select * in the exist to select 1 or select Id (performance reasons)
IF EXISTS (SELECT 1 FROM tbldata WITH (NOLOCK) WHERE Id=@Id)
BEGIN
UPDATE dbo.tbldata
SET Name=@Name,ShortDescription=@ShortDescription
WHERE Id=@Id
END
ELSE
BEGIN
INSERT INTO dbo.tbldata(Id, Name, ShortDescription)
VALUES(@Id,@Name, @ShortDescription)
END
August 18, 2011 at 12:37 pm
Processing a record one by one is going to be slow.
The merge statement is your best option.
The following is an example.
http://www.mssqltips.com/tip.asp?tip=1704
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 18, 2011 at 1:09 pm
I removed single quotes for Id in where condition and its working !!:-)
August 18, 2011 at 1:20 pm
I too have found that the MERGE construct works very nicely. Sure beats having an INSERT / UPDATE which was done historically....
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 18, 2011 at 1:46 pm
Actually after updating I want to insert the Modifieddate..
How is it possible...
August 18, 2011 at 1:54 pm
Insert a modifieddate where? I usually have columns on stuff like this, DateCreated and DateModified and updated them when needed.
August 18, 2011 at 1:55 pm
Consider using an INSERT/UPDATE trigger on the table.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 18, 2011 at 2:41 pm
into a column ModifiedDate.
It should happen only when an update happen
August 18, 2011 at 3:05 pm
You could do the below, but I highly recommend using MERGE instead.
IF EXISTS (SELECT 1 FROM tbldata WITH (NOLOCK) WHERE Id=@Id)
BEGIN
UPDATE dbo.tbldata
SET Name=@Name,ShortDescription=@ShortDescription,ModifiedDate=CURRENT_TIMESTAMP
WHERE Id=@Id
END
ELSE
BEGIN
INSERT INTO dbo.tbldata(Id, Name, ShortDescription)
VALUES(@Id,@Name, @ShortDescription)
END
August 18, 2011 at 3:14 pm
Neal Sivley (8/18/2011)
Insert a modifieddate where? I usually have columns on stuff like this, DateCreated and DateModified and updated them when needed.
If you look at the MERGE example and and add code to update the DateCreated on an insert to the CurrentDate and the Modified Date to the CurrentDate on an Update you will be good to go.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply