Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What would be right approach? Expand / Collapse
Author
Message
Posted Sunday, September 2, 2012 9:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:40 PM
Points: 248, Visits: 569
I have a table which contains data for 12 months. Now i want to delete the data for the month of Aug, and insert new data which is present with me. For which i would do the following

delete from MyTable where Cdate between ('2012-08-01','2012-08-31')

Now my question is How to insert the new data into database. Is it like writing 31 insert statement?
Post #1353315
Posted Sunday, September 2, 2012 9:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 3,610, Visits: 5,223
Why don't you just use UPDATE (if all 31 rows are present) or MERGE (if they are not)?


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1353316
Posted Sunday, September 2, 2012 10:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:40 PM
Points: 248, Visits: 569
dwain.c (9/2/2012)
Why don't you just use UPDATE (if all 31 rows are present) or MERGE (if they are not)?


But that means i have to write update 31 times. Can't this be eliminated by some means
Post #1353323
Posted Sunday, September 2, 2012 10:27 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 3,610, Visits: 5,223
Shadab Shah (9/2/2012)
dwain.c (9/2/2012)
Why don't you just use UPDATE (if all 31 rows are present) or MERGE (if they are not)?


But that means i have to write update 31 times. Can't this be eliminated by some means


No it does not. You'd need to do something like this:

;WITH MyNewData (a,b,c) AS (
SELECT 1, 2, 3
UNION ALL SELECT 2, 3, 4
-- etc. for 28 more records
UNION ALL SELECT 31, 4, 5)
UPDATE t
SET b=d.b -- new data for column b
,c=d.c -- new data for column c
FROM MyTable t
INNER JOIN MyNewData d
ON d.a = t.a


My column "a" is your "CDate."



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1353327
Posted Sunday, September 2, 2012 10:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, December 16, 2012 9:11 PM
Points: 66, Visits: 39
May be that:
SELECT	DATEADD (DAY,sv.Number,'20120801')
FROM master.dbo.spt_values sv
WHERE sv.Name IS NULL AND sv.Number < 31

Post #1353328
Posted Tuesday, September 4, 2012 10:16 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
But that means i have to write update 31 times. Can't this be eliminated by some means


You put you put the new data in a table, then invoke that table in the USING clause of a MERGE statement.

A better question is why you have a table limited to 12 months instead of a correctly declared table of what ever entities it models, with a temporal attribute.


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
Post #1354307
Posted Friday, September 7, 2012 7:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
Shadab Shah (9/2/2012)
I have a table which contains data for 12 months. Now i want to delete the data for the month of Aug, and insert new data which is present with me. For which i would do the following

delete from MyTable where Cdate between ('2012-08-01','2012-08-31')

Now my question is How to insert the new data into database. Is it like writing 31 insert statement?


I'm pretty sure the answer is "NO" but have to make sure... do any of those dates have times other than midnight on them? Also, what is the data-type of the Cdate column?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1356329
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse