InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 22:46:31 GMT20http://www.sqlservercentral.com/Forums/Topic1353315-392-1.aspx[quote][b]Shadab Shah (9/2/2012)[/b][hr]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[code="sql"]delete from MyTable where Cdate between ('2012-08-01','2012-08-31')[/code]Now my question is How to insert the new data into database. Is it like writing 31 insert statement?[/quote]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?Fri, 07 Sep 2012 19:46:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353315-392-1.aspx[quote] But that means i have to write update 31 times. Can't this be eliminated by some means[/quote]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.Tue, 04 Sep 2012 22:16:14 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1353315-392-1.aspxMay be that:[code="sql"]SELECT DATEADD (DAY,sv.Number,'20120801')FROM master.dbo.spt_values svWHERE sv.Name IS NULL AND sv.Number < 31[/code]Sun, 02 Sep 2012 22:28:46 GMTdab1037http://www.sqlservercentral.com/Forums/Topic1353315-392-1.aspx[quote][b]Shadab Shah (9/2/2012)[/b][hr][quote][b]dwain.c (9/2/2012)[/b][hr]Why don't you just use UPDATE (if all 31 rows are present) or MERGE (if they are not)?[/quote]But that means i have to write update 31 times. Can't this be eliminated by some means[/quote]No it does not. You'd need to do something like this:[code="sql"];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 tSET b=d.b -- new data for column b ,c=d.c -- new data for column cFROM MyTable tINNER JOIN MyNewData d ON d.a = t.a[/code]My column "a" is your "CDate."Sun, 02 Sep 2012 22:27:29 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1353315-392-1.aspx[quote][b]dwain.c (9/2/2012)[/b][hr]Why don't you just use UPDATE (if all 31 rows are present) or MERGE (if they are not)?[/quote]But that means i have to write update 31 times. Can't this be eliminated by some meansSun, 02 Sep 2012 22:18:21 GMTShadab Shahhttp://www.sqlservercentral.com/Forums/Topic1353315-392-1.aspxWhy don't you just use UPDATE (if all 31 rows are present) or MERGE (if they are not)?Sun, 02 Sep 2012 21:29:06 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1353315-392-1.aspxI 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[code="sql"]delete from MyTable where Cdate between ('2012-08-01','2012-08-31')[/code]Now my question is How to insert the new data into database. Is it like writing 31 insert statement?Sun, 02 Sep 2012 21:21:44 GMTShadab Shah