April 5, 2015 at 3:20 am
Say I have the following SQL Statement
IF(@Activities IS NOT NULL)
Begin
EXEC('INSERT INTO [User].[User_Activities]
(ActivitieId, UserId,DateCreated, DateDeleted)
VALUES' + @Activities)
END
@Activities looks like the following, its built dynamically from the application and passed in via Dapper.Net:
(1, 1, SYSDATETIME(), null),
(4, 1, SYSDATETIME(), null),
(6, 1, SYSDATETIME(), null),
(8, 1, SYSDATETIME(), null)
When this is passed in for the first time I do an insert everything is fine and executes successfully, If i navigate back to the page and remove an Activity and then Add a new one, and save again, the insert statement is called within the same proc so it will insert duplicate records except for the new one, the problem I'm having is trying to find a way/syntax to figure out what's new and what needs to be inserted which would result it not having duplicate records, I hope I've explained that properly and easy to the understand would any DBA Expert now how I could go about doing this?
April 5, 2015 at 5:19 am
Assuming you have a primary key to identify each row, I'd suggest setting things up through the MERGE command instead of just insert. That way you can do what's appropriate, INSERT/UPDATE/DELETE. Also, instead of a text listing, if you used a table you could just JOIN to do your updates.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 5, 2015 at 5:30 am
Ok, would you be able to show me a small example I've googled this but my knowledge on Sql and it's syntax is very limited
April 5, 2015 at 7:05 am
Ok I think im nearly there, this is my code
MERGE [User].[User_Activities] AS ua
USING (SELECT Id FROM [Fitness].[Activities]) AS f
ON ua.Id = f.Id
WHEN MATCHED THEN UPDATE SET ua.DateDeleted = sysdatetime()
WHEN NOT MATCHED THEN
EXEC('INSERT INTO [User].[User_Activities]
(UserId, ActivitieId, DateCreated, DateDeleted)
VALUES' + @Activities);
GO
I keep getting this error:
Incorrect syntax near the keyword 'EXEC'.
Incorrect syntax near 'GO'.
My two tables are as follows:
My fitness tables which has a primary key Id, its the table that holds all the activities
CREATE TABLE [Fitness].[Activities](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ActivityDesc] [varchar](25) NOT NULL,
CONSTRAINT [PK_Fitness.Activities] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This is my User.User_Activities table
CREATE TABLE [User].[User_Activities](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[ActivitieId] [int] NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateDeleted] [datetime] NULL,
CONSTRAINT [PK_User.User_Activities] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The ActivitieId on [User].[User_Activities] will be linked to the Id on the fitness table
April 5, 2015 at 4:14 pm
Can someone help me with this please
April 6, 2015 at 12:32 am
.Netter (4/5/2015)
Can someone help me with this please
Quick thought, you cannot mix dynamic and static sql in this way, better do a full dynamic statement. Also you should work through the logic in you code, looks like something is missing there.
😎
NB: Make certain that the content of the @Activities variable is both sanitized and tamper proof!
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.User_Activities') IS NOT NULL DROP TABLE dbo.User_Activities;
CREATE TABLE dbo.User_Activities(
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[ActivitieId] [int] NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateDeleted] [datetime] NULL,
CONSTRAINT [PK_User.User_Activities] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] ;
DECLARE @Activities NVARCHAR(MAX) = N'(1, 1, SYSDATETIME(), null),
(4, 1, SYSDATETIME(), null),
(6, 1, SYSDATETIME(), null),
(8, 1, SYSDATETIME(), null)';
DECLARE @SQL_STR NVARCHAR(MAX) = N'
;WITH INCOMING_ACTIVITIES(UserId, ActivitieId, DateCreated, DateDeleted) AS
(
SELECT * FROM (VALUES
' + @Activities + N'
) AS X(UserId, ActivitieId, DateCreated, DateDeleted)
)
MERGE dbo.User_Activities AS DEST
USING INCOMING_ACTIVITIES AS SRC
ON DEST.UserId = SRC.UserId
AND DEST.ActivitieId = SRC.ActivitieId
AND DEST.DateDeleted IS NULL
WHEN MATCHED THEN
UPDATE
SET DateDeleted = sysdatetime()
WHEN NOT MATCHED THEN
INSERT
(
UserId
,ActivitieId
,DateCreated
,DateDeleted
)
VALUES
(
SRC.UserId
,SRC.ActivitieId
,SRC.DateCreated
,SRC.DateDeleted
);
';
EXEC (@SQL_STR);
SELECT * FROM dbo.User_Activities;
[/code]
First execution Results
Id UserId ActivitieId DateCreated DateDeleted
-------------------- -------------------- ----------- ----------------------- -----------------------
1 1 1 2015-04-06 07:33:50.860 NULL
2 4 1 2015-04-06 07:33:50.860 NULL
3 6 1 2015-04-06 07:33:50.860 NULL
4 8 1 2015-04-06 07:33:50.860 NULL
Second execution Results
Id UserId ActivitieId DateCreated DateDeleted
-------------------- -------------------- ----------- ----------------------- -----------------------
1 1 1 2015-04-06 07:33:50.860 2015-04-06 07:34:33.723
2 4 1 2015-04-06 07:33:50.860 2015-04-06 07:34:33.723
3 6 1 2015-04-06 07:33:50.860 2015-04-06 07:34:33.723
4 8 1 2015-04-06 07:33:50.860 2015-04-06 07:34:33.723
Third execution Results
Id UserId ActivitieId DateCreated DateDeleted
-------------------- -------------------- ----------- ----------------------- -----------------------
1 1 1 2015-04-06 07:33:50.860 2015-04-06 07:34:33.723
2 4 1 2015-04-06 07:33:50.860 2015-04-06 07:34:33.723
3 6 1 2015-04-06 07:33:50.860 2015-04-06 07:34:33.723
4 8 1 2015-04-06 07:33:50.860 2015-04-06 07:34:33.723
5 1 1 2015-04-06 07:35:03.607 NULL
6 4 1 2015-04-06 07:35:03.607 NULL
7 6 1 2015-04-06 07:35:03.607 NULL
8 8 1 2015-04-06 07:35:03.607 NULL
April 6, 2015 at 7:26 am
Why do you need to use dynamic code here? I see that you have a list of values in your variable but if those were broken out you wouldn't need to worry about dynamic sql here in the first place.
As a side note, I would possibly rethink using MERGE here. I have found enough issues with that statement that I tend to avoid it for the more traditional methods. In an upsert it can cause deadlocks. According to MS it is "by design".
https://connect.microsoft.com/SQLServer/feedback/details/723696/basic-merge-upsert-causing-deadlocks
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 6, 2015 at 5:29 pm
Hi Sean,
Thanks for your response, we have changed the way we insert/update the activities now, instead from the UI when the user deletes one we call a ajax method which passes in the ID of the activity they want to remove, instead of submitting the hole page again.
Now going back to what you have mentioned regarding the deadlock, these two table in question i.e Fitness_Activities and user.User_Details will be the main tables used when signing up, so judging on what you linked it could be worth reverting back to what we had originally.
Thanks for the information
The dynamic code was built in the UI, when the user selects the activities he/she do we loop through it all build the insert statement and pass it in, we are currently using Dapper.Net which is a Micro ORM. Normally when doing such thing I would use a defined table type and pass in a data table and pull the values from that.
April 7, 2015 at 7:38 am
Sean Lange (4/6/2015)
Why do you need to use dynamic code here? I see that you have a list of values in your variable but if those were broken out you wouldn't need to worry about dynamic sql here in the first place.As a side note, I would possibly rethink using MERGE here. I have found enough issues with that statement that I tend to avoid it for the more traditional methods. In an upsert it can cause deadlocks. According to MS it is "by design".
https://connect.microsoft.com/SQLServer/feedback/details/723696/basic-merge-upsert-causing-deadlocks
Thanks for the info Sean, I've seen others in the corp using this statement more and more.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 7, 2015 at 7:52 am
below86 (4/7/2015)
Sean Lange (4/6/2015)
Why do you need to use dynamic code here? I see that you have a list of values in your variable but if those were broken out you wouldn't need to worry about dynamic sql here in the first place.As a side note, I would possibly rethink using MERGE here. I have found enough issues with that statement that I tend to avoid it for the more traditional methods. In an upsert it can cause deadlocks. According to MS it is "by design".
https://connect.microsoft.com/SQLServer/feedback/details/723696/basic-merge-upsert-causing-deadlocks
Thanks for the info Sean, I've seen others in the corp using this statement more and more.
Check out Aaron Bertrand's log post. It discusses a host of other issues with MERGE. http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 7, 2015 at 8:18 am
Sean Lange (4/7/2015)
below86 (4/7/2015)
Sean Lange (4/6/2015)
Why do you need to use dynamic code here? I see that you have a list of values in your variable but if those were broken out you wouldn't need to worry about dynamic sql here in the first place.As a side note, I would possibly rethink using MERGE here. I have found enough issues with that statement that I tend to avoid it for the more traditional methods. In an upsert it can cause deadlocks. According to MS it is "by design".
https://connect.microsoft.com/SQLServer/feedback/details/723696/basic-merge-upsert-causing-deadlocks
Thanks for the info Sean, I've seen others in the corp using this statement more and more.
Check out Aaron Bertrand's log post. It discusses a host of other issues with MERGE. http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/[/url]
I'm seeing this used moe in our corp, I haven't used it and was wondering if I should. But now, thanks to this info I don't think I will and I'll pass this along to others. Thanks again Sean, you da' man. 🙂
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply