November 11, 2016 at 9:52 am
So I have a stored proc
INSERT INTO dbo.Table2 (Application, Timestamp, Performance, Availability)
SELECT
Application,
CAST(Timestamp AS DATE) as Date,
AVG(CAST(Performance AS decimal(6,2))) as Average_Performance,
AVG(Availability) as Average_Availability
FROM dbo.Table1
GROUP BY CAST(Timestamp AS DATE), Application
END
It works fine but if I get an existing Application and Timestamp, it creates a new row. I know I need an IF EXISTS UPDATE but I am not sure how to write it to only update if duplicate. I have seen some examples of this but my ON DUPLICATE doesn't seem to work in the above code.
Thank you.
November 11, 2016 at 9:59 am
I'm not sure what you mean by ON DUPLICATE - I don't think it's a T-SQL keyword. You'll need either a MERGE statement, or to do separate INSERT and UPDATE statements.
John
November 11, 2016 at 10:02 am
If I do a separate UPDATE statement, what would I set Application and Timestamp equal to? Would I need to create a variable from the existing items and update that way?
November 11, 2016 at 10:03 am
Matt.Altman (11/11/2016)
So I have a stored procINSERT INTO dbo.Table2 (Application, Timestamp, Performance, Availability)
SELECT
Application,
CAST(Timestamp AS DATE) as Date,
AVG(CAST(Performance AS decimal(6,2))) as Average_Performance,
AVG(Availability) as Average_Availability
FROM dbo.Table1
GROUP BY CAST(Timestamp AS DATE), Application
END
It works fine but if I get an existing Application and Timestamp, it creates a new row. I know I need an IF EXISTS UPDATE but I am not sure how to write it to only update if duplicate. I have seen some examples of this but my ON DUPLICATE doesn't seem to work in the above code.
Thank you.
ON DUPLICATE is a mysql thing. What DBMS are you using? And be careful with such ambiguous names like timestamp. If this is sql server you should not use that because it is a datatype (which has nothing to do with time of day). But in any DBMS you should avoid that name because it doesn't tell you what it is. Is that DateCreated? DateUpdated? Some other relevant time? In general your column names are so vague they are not very usable. Give your column names some meaning.
_______________________________________________________________
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/
November 11, 2016 at 10:06 am
Sorry, I didnt realize the ON DUPLICATE was mysql. Should have seen that. I am using T-SQL. And I will look at changing the name of that column.
November 11, 2016 at 10:23 am
Matt.Altman (11/11/2016)
Sorry, I didnt realize the ON DUPLICATE was mysql. Should have seen that. I am using T-SQL. And I will look at changing the name of that column.
The two methods you can use for this are something along these lines.
Update table
set Col1 = SomeValue
where MyKeys = Mykeys
if (@@ROWCOUNT = 0)
Insert table
(Cols)
Values
(Vals)
Or you can use MERGE. https://msdn.microsoft.com/en-us/library/bb510625.aspx
_______________________________________________________________
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/
November 15, 2016 at 12:39 pm
>> So I have a stored procedure <<
Why? You are doing a computation, so if you put it in the view. It will always be correct, it will port. Since we have no DDL (did you read the forum rules?). We have no idea about the data types, and since you used incorrect data element names (get a book on data modeling or read ISO 11179 – this will be really painful; standards documents are their own language). Here is my attempt at fixing up what you had. A sure sign that you got design problems, by the way, is that you are constantly casting things.
Your original design should have made good choices about the data types of the columns in the base tables, and you do not need to constantly correct them at the database level.
CREATE VIEW Foobar_Summary
AS
SELECT application_name, something_timestamp,
AVG(performance_score) AS performance_score_avg,
AVG(availability_duration) AS availability_duration_avg
FROM Generic_Somethings
GROUP BY something_timestamp, application_name;
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
November 16, 2016 at 11:19 am
Something like this should do it:
IF OBJECT_ID('tempdb.dbo.#table2_data') IS NOT NULL
DROP TABLE #table2_data
--create the table structure for #table2_data
SELECT TOP (0)
Application,
CAST(Timestamp AS DATE) as Date,
AVG(CAST(Performance AS decimal(6,2))) as Average_Performance,
AVG(Availability) as Average_Availability
INTO #table2_data
FROM dbo.Table1
INSERT INTO #table2_data
SELECT
Application,
CAST(Timestamp AS DATE) as Date,
AVG(CAST(Performance AS decimal(6,2))) as Average_Performance,
AVG(Availability) as Average_Availability
FROM dbo.Table1
GROUP BY CAST(Timestamp AS DATE), Application
UPDATE t2
SET
Performance = t2d.Average_Performance,
Availability = t2d.Average_Availability
FROM dbo.Table2 t2
INNER JOIN #table2_data t2d ON t2d.Application = t2.Application AND t2d.Timestamp = t2.TimeStamp
INSERT INTO dbo.Table2 (Application, Timestamp, Performance, Availability)
SELECT
Application,
CAST(Timestamp AS DATE) as Date,
AVG(CAST(Performance AS decimal(6,2))) as Average_Performance,
AVG(Availability) as Average_Availability
FROM #table2_data t2d
WHERE NOT EXISTS (
SELECT 1
FROM dbo.Table2 t2
WHERE t2d.Application = t2.Application AND t2d.Timestamp = t2.TimeStamp
)
GROUP BY CAST(Timestamp AS DATE), Application
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 16, 2016 at 11:42 am
In T-SQL there is an IGNORE_DUP_KEY option that can be added to the primary key. Unlike MySQL's ON DUPLICATE keyword, it doesn't result in an update for a row with a duplicating key, but it rather ignores the row (doesn't insert it).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 16, 2016 at 2:32 pm
Sean Lange (11/11/2016)
Update table
set Col1 = SomeValue
where MyKeys = Mykeys
if (@@ROWCOUNT = 0)
Insert table
(Cols)
Values
(Vals)
November 16, 2016 at 3:03 pm
Here is what I ended up doing to get this to work
;WITH CTE AS
(
SELECT
Application = t.Application,
Timestamp = CONVERT(date, t.Timestamp),
Performance = AVG(t.Performance),
Availability = AVG(t.Availability)
FROM
Table2 a
INNER JOIN
Table1 t
ON a.Application = t.Application
WHERE a.Application = t.Application AND a.Timestamp = CONVERT(date, t.Timestamp)
GROUP BY CONVERT(date, t.Timestamp), t.Application
)
UPDATE Table2
SET
Application = c.Application,
Timestamp = c.Timestamp,
Performance = c.Performance,
Availability = c.Availability
FROM Table2 a
JOIN CTE c ON c.Application = a.Application
AND c.Timestamp = a.Timestamp
;
INSERT INTO Table2 (Application, Timestamp, Performance, Availability)
SELECT
t.Application,
CAST(t.Timestamp AS DATE) AS DATE,
AVG(t.Performance) AS AVG_PERF,
AVG(t.Availability) AS AVG_AVAIL
FROM Table1 t
LEFT JOIN Table2 a
ON t.Application = a.Application
WHERE a.Application IS NULL
GROUP BY CAST(t.Timestamp AS DATE), t.Application
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply