October 24, 2013 at 6:55 am
Hi,
I am using Drop and create table in Loop because of results I want to achieve using loop. But my question is
Is this better to use Truncate table rather than Drop and creating table again.
October 24, 2013 at 7:09 am
Rakesh.Chaudhary (10/24/2013)
Hi,I am using Drop and create table in Loop because of results I want to achieve using loop. But my question is
Is this better to use Truncate table rather than Drop and creating table again.
If you need a clean table every time you enter in loop, just TRUNCATE it.
October 24, 2013 at 7:11 am
Rakesh.Chaudhary (10/24/2013)
Hi,I am using Drop and create table in Loop because of results I want to achieve using loop. But my question is
Is this better to use Truncate table rather than Drop and creating table again.
Best is probably to not loop 😀
October 24, 2013 at 8:09 am
From a core behavior stand point, there's no real difference between the two. Both are basically just deallocating the resource. But, you then have to go back and recreate the structure after a drop.
Why would you truncate in a loop?
"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
October 24, 2013 at 8:44 am
I have to calculate the amonts for Previos day ,SO I am using where
condition
WHILE ( @DateMinRes <= @DateMaxRes )
BEGIN
INSERT INTO tABLE A
(
a,
b,
c
)
SELECT
a,
b,
c
FROM xyz
Where Day < @DateMinRes
SELECT SUM(a) from Table A
DRop Table A
SET@DateMin = @DateMin +1
END
----------AS in above query I have to calculate sum from table A for previous day and thats why i am dropping it and creating again
October 24, 2013 at 8:58 am
I'm not sure you are asking the right question. I don't believe you need to drop tables or loop through the data. It seems like you would be able to handle this in a single query using a CTE possibility. If you can provide a script to create the necessary tables and another script to populate the tables and then provide us with the desired outcome someone maybe able to help you with a query to handle this case.
EDIT:
After reading the last post again you may be able to simply add a GROUP BY on the date with a sum i.e.:
SELECT
sum(a)
b,
c
FROM xyz
Where Day >= @DateMinRes
and Day < @DateMaxRes
group by Day, b, c
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply