February 24, 2013 at 8:40 pm
I have read a few posts on how to shrink TempDb but I am not even sure if "Shrink" is what I need to do.
Here is what is going on. I have a query that runs against a table with many rows. In VB.Net I establish a SQLConnection to the server and database. Next I create a SQLCommand which executes a query. The query calculates a 20 day moving average for a single day. But within that day there are thousands of rows which need the Moving average calculated. After the query is run for the first day, it is then run for the next day and then the next, etc for an entire year. I never close the SQLConnection (But if that would help it can be easily done.).
The problem is that after only 23 cycles of the query (23 days) the TempDB has grown from 8 MB to 780 GB and all the space on the TempDB drive is consumed. I think what i need to do is empty out the TempDb after the query is run for each day. But the only way I know to do this is to stop SQL Server so that the TempDb is recreated. And that is not a practical solution. There is no other activity on this server when this series of queries is run.
How can I "reset" the TempDb using a script so that I do not consume all the free space on the drive where the TempDb is stored?
Thanks,
pat
February 25, 2013 at 2:18 am
You don't need to reset TempDB, you need to tune the query so that it doesn't need so much TempDB space or drop the temp objects that you're using between each run.
You're either not dropping some temp tables or are running the entire year's worth in a single transaction, so the space in TempDB isn't getting reused.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2013 at 5:06 am
I completely agree with you. Whatever is being created in the TempDb is not being reused between when the query runs the second, third, fourth, etc times. I do not explicitly create any Temp tables so I am not sure how to delete what the Server creates automatically/internally. I would like to reuse the space used by the individual queries but I do not know how to do that.
I am using a CTE to compile a group of records that is smaller than a year, just the data needed for 1 day's calculation. The results of the CTE is used to do the calculation and the update.
How do I identify/manage the results of a CTE in between queries?
If you need to see the CTE and get an idea of the idea how much data is being processed, just let me know. I can post that later today.
Thanks,
pat
February 25, 2013 at 5:26 am
mpdillon (2/25/2013)
I completely agree with you. Whatever is being created in the TempDb is not being reused between when the query runs the second, third, fourth, etc times. I do not explicitly create any Temp tables so I am not sure how to delete what the Server creates automatically/internally.
That's handled automatically, there's nothing you need to do there. Work tables that are created internally are managed and dropped internally
How do I identify/manage the results of a CTE in between queries?
CTEs don't store results and have a scope limited to the statement they are created in, hence there are no results to manage and a CTE does not and can not persist over multiple queries.
Two options here:
- You're creating a temp object somewhere in your query and not dropping it (temp table or table variable)
- The query you're running is reusing TempDB properly but needs more space each time it runs (eg 10 MB the first time, 20 the second, 40 the 3rd, etc). Very likely if you're using triangular joins or other inefficient query mechanisms over a data set that's increasing in size. Tune the query, use more efficient methods.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2013 at 5:56 am
mpdillon (2/24/2013)
I have read a few posts on how to shrink TempDb but I am not even sure if "Shrink" is what I need to do.Here is what is going on. I have a query that runs against a table with many rows. In VB.Net I establish a SQLConnection to the server and database. Next I create a SQLCommand which executes a query. The query calculates a 20 day moving average for a single day. But within that day there are thousands of rows which need the Moving average calculated. After the query is run for the first day, it is then run for the next day and then the next, etc for an entire year. I never close the SQLConnection (But if that would help it can be easily done.).
The problem is that after only 23 cycles of the query (23 days) the TempDB has grown from 8 MB to 780 GB and all the space on the TempDB drive is consumed. I think what i need to do is empty out the TempDb after the query is run for each day. But the only way I know to do this is to stop SQL Server so that the TempDb is recreated. And that is not a practical solution. There is no other activity on this server when this series of queries is run.
How can I "reset" the TempDb using a script so that I do not consume all the free space on the drive where the TempDb is stored?
Thanks,
pat
I can't tell for sure because I've not seen your code but many people make the mistake of building a Triangular Join to do such a thing. Please see the following article for why it gets so bad.
http://www.sqlservercentral.com/articles/T-SQL/61539/
There's a great article somewhere here on SQL Server Central for calculating moving averages in a very high performance manner. I just don't know where it is offhand and I don't have the time to do the search for you. Give it a search and see what you come up with.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2013 at 6:46 am
Jeff,
I know you've written a number of articles on running totals, this one (the most recent) sprang to mind, http://www.sqlservercentral.com/articles/T-SQL/68467/, but I'm not sure if this is the one you were thinking of.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 25, 2013 at 10:15 am
Jason,
I haven’t read/studied that article yet. Thank you. There was another article which is referenced in a previous post I made when I was trying to build the query which is also very useful. I will try to get to reading that tonight.
Jeff,
I will be posting more information about the query, including the details and the execution plans, in just a few minutes. I have everything but it will take me about 30 minutes to shape it all into a post. Please stay tuned.
Gail,
What I am finding is that when I run the query from “New Query” rather than Visual Studio that the behavior of the TempDB is as you describe. But I have only tested two days because of the time the query takes to run. Please take a moment to read my next post for a more thorough description of the problem.
Thanks,
pat
February 25, 2013 at 10:55 am
GilaMonster (2/25/2013)
CTEs don't store results
Never??? What if enough memory does not exist to store the results? Surely the results would spill to disk then, presumably to tempdb ...
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".
February 25, 2013 at 11:34 am
Let’s begin with the actual query that is giving me fits.
with MstrTbl as (Select N.Source, N.Sink, N.TimePoint, D.TPIntDate, D.TPHour, N.Delta From dbo.ECoUpToPairedData N
Inner Join dbo.EcoDate D
On N.TimePoint = D.TimePoint
Where D.TimePoint >= DATEADD(dd,-19,'02/01/2012 00:00:00') and d.TimePoint <= '02/01/2012 23:59:00'
)
Update dbo.ECoUpToPairedData
set MA1 = CteMA.DeltaB
From (
Select A.Source, A.Sink, A.TimePoint as TPA,
A.TPHour as HourA,
avg(B.Delta) as DeltaB, COUNT(A.Source) as NoOfDaysAvged
from MstrTbl A
Inner Join MstrTbl B
On A.source = B.Source and A.Sink = B.Sink and A.TPHour = B.TPHour and A.TPIntDate >= B.TPIntDate and A.TPIntDate -19 <= B.TPIntDate
Group by A.Source, A.Sink, A.TimePoint,A.TPHour
) as CTEMA
where ECoUpToPairedData.Source = CTEMA.Source and ecouptopaireddata.Sink = CTEMA.Sink and TimePoint = CTEMA.TPA
and ctema.TPA >= '02/01/2012 00:00:00' and ctema.TPA <= '02/01/2012 23:59:00'
This query is run against two tables. Their definitions are below.
The Date lookup table
CREATE TABLE [dbo].[EcoDate](
[TimePoint] [smalldatetime] NULL,
[TPYear] [int] NULL,
[TPMth] [int] NULL,
[TPDay] [int] NULL,
[TPHour] [int] NULL,
[TPDOWName] [char](10) NULL,
[TPMthName] [nchar](10) NULL,
[TPIntDate] [bigint] NULL
) ON [PRIMARY]
Clustered
CREATE UNIQUE CLUSTERED INDEX [IX_ECoDate] ON [dbo].[EcoDate]
(
[TimePoint] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Non Clustered
CREATE NONCLUSTERED INDEX [IX_EcoDate_1] ON [dbo].[EcoDate]
(
[TPIntDate] ASC,
[TPHour] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Next is the Paired Data table:
CREATE TABLE [dbo].[ECoUpToPairedData](
[n] [bigint] NULL,
[TimePoint] [datetime] NOT NULL,
[Source] [varchar](100) NOT NULL,
[Sink] [varchar](100) NOT NULL,
[SinkDaCongestion] [decimal](9, 5) NULL,
[SourceDaCongestion] [decimal](9, 5) NULL,
[SinkDaLoss] [decimal](9, 5) NULL,
[SourceDaLoss] [decimal](9, 5) NULL,
[SinkRtCongestion] [decimal](9, 5) NULL,
[SourceRtCongestion] [decimal](9, 5) NULL,
[SinkRtLoss] [decimal](9, 5) NULL,
[SourceRtLoss] [decimal](9, 5) NULL,
[SinkVersID] [bigint] NULL,
[SourceVersID] [bigint] NULL,
[SinkExternalNodeID] [bigint] NULL,
[SourceExternalNodeID] [bigint] NULL,
[DayAhead] [decimal](9, 5) NULL,
[ReatTime] [decimal](9, 5) NULL,
[Delta] [decimal](9, 5) NULL,
[MA1] [decimal](9, 5) NULL,
[MA2] [decimal](9, 5) NULL,
[MA3] [decimal](9, 5) NULL,
[MA4] [decimal](9, 5) NULL,
[MA5] [decimal](9, 5) NULL,
[EMA1] [decimal](9, 5) NULL,
[EMA2] [decimal](9, 5) NULL,
[EMA3] [decimal](9, 5) NULL,
[EMA4] [decimal](9, 5) NULL,
[EMA5] [decimal](9, 5) NULL,
CONSTRAINT [PK_ECoUpToPairedData] PRIMARY KEY NONCLUSTERED
(
[TimePoint] ASC,
[Source] ASC,
[Sink] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_TimePointSSLookup] ON [dbo].[ECoUpToPairedData]
(
[TimePoint] ASC,
[Source] ASC,
[Sink] ASC
)
INCLUDE ( [Delta]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ECoUpToPairedData] ADD CONSTRAINT [PK_ECoUpToPairedData] PRIMARY KEY NONCLUSTERED
(
[TimePoint] ASC,
[Source] ASC,
[Sink] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Here are the statistics:
First time the query is run it is run for 02/01/2012. The TempDB is 8 MB at the start and is 3.8 GB at the end. It takes 5 min 45 seconds to execute. And 2.392.480 records are updated.
The second time it is run for 02/02/2012. The TempDb is 3.8 GB at the start and 4.9 GB at the end. However, it took 39 minutes to do the update. And it only updated 797,160 rows.
I reran the first date of 02/01/2012. The TempDb size did not change. It stayed at 4.9 GB. And the execution time was 3 min 30 seconds. It updated 2.392.480 records (the same number as the first attempt).
Why is 02/01/012 with three times as many records updating 10 times faster than 02/02/2012?
I have included the Estimated Execution Plan for 02/01/2012. I have attached the Actual Execution plan for 02/01/2012. I am waiting on the Actual Execution plan for 02/02/2012. I will attach it when it completes.
I have run the tuning advisor. There were no recommended changes (or I don’t know how to interpret the results. Very possible that the latter is the case.). How do you save the Tuning advisor reports. I tried Right clicking and was not presented with a menu.
If it will help, I do have sample data. Just let me know if I should post it.
February 25, 2013 at 12:05 pm
Yes, please post the sample data.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 25, 2013 at 12:08 pm
Car problems. Will post in 3-4 hours. Sorry.
February 25, 2013 at 3:48 pm
ScottPletcher (2/25/2013)
GilaMonster (2/25/2013)
CTEs don't store results
Never??? What if enough memory does not exist to store the results? Surely the results would spill to disk then, presumably to tempdb ...
They are not temp tables or table variables, they do not store results any more than a plain select statement or normal view does. Talking about a CTE's results is a meaningless concept, as the SQL inlines them into the query during the parsing and binding phase, just like a subquery or view. The CTE is not first calculated and the results stored somewhere, then the rest of the query run against those results and as such, one cannot talk about a CTE's results as if it were a temp table or table variable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2013 at 4:06 pm
GilaMonster (2/25/2013)
ScottPletcher (2/25/2013)
GilaMonster (2/25/2013)
CTEs don't store results
Never??? What if enough memory does not exist to store the results? Surely the results would spill to disk then, presumably to tempdb ...
They are not temp tables or table variables, they do not store results any more than a plain select statement or normal view does. Talking about a CTE's results is a meaningless concept, as the SQL inlines them into the query during the parsing and binding phase, just like a subquery or view. The CTE is not first calculated and the results stored somewhere, then the rest of the query run against those results and as such, one cannot talk about a CTE's results as if it were a temp table or table variable.
But they may need spool space just like any other table or view used multiple times for lookups, etc., yes?
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".
February 25, 2013 at 4:33 pm
Gail,
I do not understand. If there is no activity other than this query and the TempDb grows so dramatically in size, what would the cause be?
Also, I have attached the SQL Actual Execution plan for Feb 2nd. The day that takes so long to process despite affecting fewer rows.
Thanks for staying with this thread. This discussion will lead to a better understanding of the process for me.
pat
February 25, 2013 at 5:17 pm
Below you will find the CTE again and data. The data is preceeded by the Create table statement and the Alter statements for creating the indexes.
CTE Update
with MstrTbl as (Select N.Source, N.Sink, N.TimePoint, D.TPIntDate, D.TPHour, N.Delta From dbo.ECoUpToPairedData N
Inner Join dbo.EcoDate D
On N.TimePoint = D.TimePoint
Where D.TimePoint >= DATEADD(dd,-19,'02/01/2012 00:00:00') and d.TimePoint <= '02/01/2012 23:59:00'
)
Update dbo.ECoUpToPairedData
set MA1 = CteMA.DeltaB
From (
Select A.Source, A.Sink, A.TimePoint as TPA,
A.TPHour as HourA,
avg(B.Delta) as DeltaB, COUNT(A.Source) as NoOfDaysAvged
from MstrTbl A
Inner Join MstrTbl B
On A.source = B.Source and A.Sink = B.Sink and A.TPHour = B.TPHour and A.TPIntDate >= B.TPIntDate and A.TPIntDate -19 <= B.TPIntDate
Group by A.Source, A.Sink, A.TimePoint,A.TPHour
) as CTEMA
where ECoUpToPairedData.Source = CTEMA.Source and ecouptopaireddata.Sink = CTEMA.Sink and TimePoint = CTEMA.TPA
and ctema.TPA >= '02/01/2012 00:00:00' and ctema.TPA <= '02/01/2012 23:59:00'
The code to Create, Alter and Populate the Date lookup table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EcoDate](
[TimePoint] [smalldatetime] NULL,
[TPYear] [int] NULL,
[TPMth] [int] NULL,
[TPDay] [int] NULL,
[TPHour] [int] NULL,
[TPDOWName] [char](10) NULL,
[TPMthName] [nchar](10) NULL,
[TPIntDate] [bigint] NULL
) ON [PRIMARY]
--Clustered
CREATE UNIQUE CLUSTERED INDEX [IX_ECoDate] ON [dbo].[EcoDate]
(
[TimePoint] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--Non Clustered
CREATE NONCLUSTERED INDEX [IX_EcoDate_1] ON [dbo].[EcoDate]
(
[TPIntDate] ASC,
[TPHour] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Insert into EcoDate(Timepoint,TPYear,TPMth, TPDay, TPHour, TPIntDate)
select '01/01/2012 05:00:00',2012,1,1,5,1
union
select '01/02/2012 05:00:00',2012,1,2,5,2
union
select '01/03/2012 05:00:00',2012,1,3,5,3
union
select '01/04/2012 05:00:00',2012,1,4,5,4
union
select '01/05/2012 05:00:00',2012,1,5,5,5
union
select '01/06/2012 05:00:00',2012,1,6,5,6
union
select '01/07/2012 05:00:00',2012,1,7,5,7
union
select '01/08/2012 05:00:00',2012,1,8,5,8
union
select '01/09/2012 05:00:00',2012,1,9,5,9
union
select '01/10/2012 05:00:00',2012,1,10,5,10
union
select '01/11/2012 05:00:00',2012,1,11,5,11
union
select '01/12/2012 05:00:00',2012,1,12,5,12
union
select '01/13/2012 05:00:00',2012,1,13,5,13
union
select '01/14/2012 05:00:00',2012,1,14,5,14
union
select '01/15/2012 05:00:00',2012,1,15,5,15
union
select '01/16/2012 05:00:00',2012,1,16,5,16
union
select '01/17/2012 05:00:00',2012,1,17,5,17
union
select '01/18/2012 05:00:00',2012,1,18,5,18
union
select '01/19/2012 05:00:00',2012,1,19,5,19
union
select '01/20/2012 05:00:00',2012,1,20,5,20
union
select '01/21/2012 05:00:00',2012,1,21,5,21
union
select '01/22/2012 05:00:00',2012,1,22,5,22
union
select '01/23/2012 05:00:00',2012,1,23,5,23
union
select '01/24/2012 05:00:00',2012,1,24,5,24
union
select '01/25/2012 05:00:00',2012,1,25,5,25
union
select '01/26/2012 05:00:00',2012,1,26,5,26
union
select '01/27/2012 05:00:00',2012,1,27,5,27
union
select '01/28/2012 05:00:00',2012,1,28,5,28
union
select '01/29/2012 05:00:00',2012,1,29,5,29
union
select '01/30/2012 05:00:00',2012,1,30,5,30
union
select '01/31/2012 05:00:00',2012,1,31,5,31
union
select '02/01/2012 05:00:00',2012,2,1,5,32
union
select '02/02/2012 05:00:00',2012,2,2,5,33
union
select '02/03/2012 05:00:00',2012,2,3,5,34
union
select '02/04/2012 05:00:00',2012,2,4,5,35
union
select '02/05/2012 05:00:00',2012,2,5,5,36
union
select '02/06/2012 05:00:00',2012,2,6,5,37
union
select '02/07/2012 05:00:00',2012,2,7,5,38
union
select '02/08/2012 05:00:00',2012,2,8,5,39
union
select '02/09/2012 05:00:00',2012,2,9,5,40
union
select '02/10/2012 05:00:00',2012,2,10,5,41
union
select '02/11/2012 05:00:00',2012,2,11,5,42
union
select '02/12/2012 05:00:00',2012,2,12,5,43
Go
The code to Create, Alter and popululate the Data table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ECoUpToPairedData](
[n] [bigint] NULL,
[TimePoint] [datetime] NOT NULL,
[Source] [varchar](100) NOT NULL,
[Sink] [varchar](100) NOT NULL,
[SinkDaCongestion] [decimal](9, 5) NULL,
[SourceDaCongestion] [decimal](9, 5) NULL,
[SinkDaLoss] [decimal](9, 5) NULL,
[SourceDaLoss] [decimal](9, 5) NULL,
[SinkRtCongestion] [decimal](9, 5) NULL,
[SourceRtCongestion] [decimal](9, 5) NULL,
[SinkRtLoss] [decimal](9, 5) NULL,
[SourceRtLoss] [decimal](9, 5) NULL,
[SinkVersID] [bigint] NULL,
[SourceVersID] [bigint] NULL,
[SinkExternalNodeID] [bigint] NULL,
[SourceExternalNodeID] [bigint] NULL,
[DayAhead] [decimal](9, 5) NULL,
[ReatTime] [decimal](9, 5) NULL,
[Delta] [decimal](9, 5) NULL,
[MA1] [decimal](9, 5) NULL,
[MA2] [decimal](9, 5) NULL,
[MA3] [decimal](9, 5) NULL,
[MA4] [decimal](9, 5) NULL,
[MA5] [decimal](9, 5) NULL,
[EMA1] [decimal](9, 5) NULL,
[EMA2] [decimal](9, 5) NULL,
[EMA3] [decimal](9, 5) NULL,
[EMA4] [decimal](9, 5) NULL,
[EMA5] [decimal](9, 5) NULL,
CONSTRAINT [PK_ECoUpToPairedData] PRIMARY KEY NONCLUSTERED
(
[TimePoint] ASC,
[Source] ASC,
[Sink] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_TimePointSSLookup] ON [dbo].[ECoUpToPairedData]
(
[TimePoint] ASC,
[Source] ASC,
[Sink] ASC
)
INCLUDE ( [Delta]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Insert into ECoUpToPairedData(Timepoint,Sink, Source, Delta)
select '01/01/2012 05:00:00','PEACH BOTTOM','OVEC',1
union
select '01/02/2012 05:00:00','PEACH BOTTOM','OVEC',1.5
union
select '01/03/2012 05:00:00','PEACH BOTTOM','OVEC',1.7
union
select '01/04/2012 05:00:00','PEACH BOTTOM','OVEC',9
union
select '01/05/2012 05:00:00','PEACH BOTTOM','OVEC',6
union
select '01/06/2012 05:00:00','PEACH BOTTOM','OVEC',-8
union
select '01/07/2012 05:00:00','PEACH BOTTOM','OVEC',5
union
select '01/08/2012 05:00:00','PEACH BOTTOM','OVEC',4
union
select '01/09/2012 05:00:00','PEACH BOTTOM','OVEC',5.1
union
select '01/10/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/11/2012 05:00:00','PEACH BOTTOM','OVEC',3
union
select '01/12/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/13/2012 05:00:00','PEACH BOTTOM','OVEC',35
union
select '01/14/2012 05:00:00','PEACH BOTTOM','OVEC',-9
union
select '01/15/2012 05:00:00','PEACH BOTTOM','OVEC',3
union
select '01/16/2012 05:00:00','PEACH BOTTOM','OVEC',47
union
select '01/17/2012 05:00:00','PEACH BOTTOM','OVEC',1.6
union
select '01/18/2012 05:00:00','PEACH BOTTOM','OVEC',7.4
union
select '01/19/2012 05:00:00','PEACH BOTTOM','OVEC',5
union
select '01/20/2012 05:00:00','PEACH BOTTOM','OVEC',6.3
union
select '01/21/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/22/2012 05:00:00','PEACH BOTTOM','OVEC',8
union
select '01/23/2012 05:00:00','PEACH BOTTOM','OVEC',6.5
union
select '01/24/2012 05:00:00','PEACH BOTTOM','OVEC',-7
union
select '01/25/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/26/2012 05:00:00','PEACH BOTTOM','OVEC',6.2
union
select '01/27/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/28/2012 05:00:00','PEACH BOTTOM','OVEC',1
union
select '01/29/2012 05:00:00','PEACH BOTTOM','OVEC',-8
union
select '01/30/2012 05:00:00','PEACH BOTTOM','OVEC',-3
union
select '01/31/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '02/01/2012 05:00:00','PEACH BOTTOM','OVEC',10
union
select '02/02/2012 05:00:00','PEACH BOTTOM','OVEC',9
union
select '02/03/2012 05:00:00','PEACH BOTTOM','OVEC',6
union
select '02/04/2012 05:00:00','PEACH BOTTOM','OVEC',-9
union
select '02/05/2012 05:00:00','PEACH BOTTOM','OVEC',-74
union
select '02/06/2012 05:00:00','PEACH BOTTOM','OVEC',6
union
select '02/07/2012 05:00:00','PEACH BOTTOM','OVEC',3
union
select '02/08/2012 05:00:00','PEACH BOTTOM','OVEC',34
union
select '02/09/2012 05:00:00','PEACH BOTTOM','OVEC',-54
union
select '02/10/2012 05:00:00','PEACH BOTTOM','OVEC',-41
union
select '02/11/2012 05:00:00','PEACH BOTTOM','OVEC',-35
union
select '02/12/2012 05:00:00','PEACH BOTTOM','OVEC',100
Union
select '01/01/2012 05:00:00','PEACH BOTTOM','ELMARA',1
union
select '01/02/2012 05:00:00','PEACH BOTTOM','ELMARA',1.5
union
select '01/03/2012 05:00:00','PEACH BOTTOM','ELMARA',1.7
union
select '01/04/2012 05:00:00','PEACH BOTTOM','ELMARA',9
union
select '01/05/2012 05:00:00','PEACH BOTTOM','ELMARA',6
union
select '01/06/2012 05:00:00','PEACH BOTTOM','ELMARA',-8
union
select '01/07/2012 05:00:00','PEACH BOTTOM','ELMARA',5
union
select '01/08/2012 05:00:00','PEACH BOTTOM','ELMARA',4
union
select '01/09/2012 05:00:00','PEACH BOTTOM','ELMARA',5.1
union
select '01/10/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/11/2012 05:00:00','PEACH BOTTOM','ELMARA',3
union
select '01/12/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/13/2012 05:00:00','PEACH BOTTOM','ELMARA',35
union
select '01/14/2012 05:00:00','PEACH BOTTOM','ELMARA',-9
union
select '01/15/2012 05:00:00','PEACH BOTTOM','ELMARA',3
union
select '01/16/2012 05:00:00','PEACH BOTTOM','ELMARA',47
union
select '01/17/2012 05:00:00','PEACH BOTTOM','ELMARA',1.6
union
select '01/18/2012 05:00:00','PEACH BOTTOM','ELMARA',7.4
union
select '01/19/2012 05:00:00','PEACH BOTTOM','ELMARA',5
union
select '01/20/2012 05:00:00','PEACH BOTTOM','ELMARA',6.3
union
select '01/21/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/22/2012 05:00:00','PEACH BOTTOM','ELMARA',8
union
select '01/23/2012 05:00:00','PEACH BOTTOM','ELMARA',6.5
union
select '01/24/2012 05:00:00','PEACH BOTTOM','ELMARA',-7
union
select '01/25/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/26/2012 05:00:00','PEACH BOTTOM','ELMARA',6.2
union
select '01/27/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/28/2012 05:00:00','PEACH BOTTOM','ELMARA',1
union
select '01/29/2012 05:00:00','PEACH BOTTOM','ELMARA',-8
union
select '01/30/2012 05:00:00','PEACH BOTTOM','ELMARA',-3
union
select '01/31/2012 05:00:00','PEACH BOTTOM','ELMARA',70
union
select '02/01/2012 05:00:00','PEACH BOTTOM','ELMARA',10
union
select '02/02/2012 05:00:00','PEACH BOTTOM','ELMARA',9
union
select '02/03/2012 05:00:00','PEACH BOTTOM','ELMARA',6
union
select '02/04/2012 05:00:00','PEACH BOTTOM','ELMARA',-9
union
select '02/05/2012 05:00:00','PEACH BOTTOM','ELMARA',-74
union
select '02/06/2012 05:00:00','PEACH BOTTOM','ELMARA',6
union
select '02/07/2012 05:00:00','PEACH BOTTOM','ELMARA',3
union
select '02/08/2012 05:00:00','PEACH BOTTOM','ELMARA',34
union
select '02/09/2012 05:00:00','PEACH BOTTOM','ELMARA',-54
union
select '02/10/2012 05:00:00','PEACH BOTTOM','ELMARA',-41
union
select '02/11/2012 05:00:00','PEACH BOTTOM','ELMARA',-35
union
select '02/12/2012 05:00:00','PEACH BOTTOM','ELMARA',100
The update statement should update 2 rows.
If anything is needed, please let me know.
thanks,
pat
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply