How Can I Use A For Loop In A Query ?

Nov 15, 2007

hi all i have the following stored procedure.

declare @AreaID int

select @AreaID = 1

select DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT

from
table1 INNER JOIN
table2 ON table1.JobId = table2.JobEvent_JobId
where

(table1.Job_AreaId = @AREAID)

the there are about 300 AREAID's so my question is do i do a "LOOP" or is that no efficient and chews up resources on the server ?
what would be a nice clean way to do this ?

thanks
robby

View 5 Replies

Loop Through Query

Jun 13, 2007

Out of one messed up table I need to create two related tables. I am stuck in trying to get 'some' of the columns from the messed up table to the new table.
How can I select only the columns I need and insert them in the new table?
Thanks in advance!

View 1 Replies View Related

Query In A Loop

Jul 20, 2005

I am using Report Writer for Ingres II.Is it possible to write a query in a loop?e.g. My table is like thistime position09:01 pos0109:02 pos0309:02 pos0109:04 pos05Can I loop a query to count the number of times each position occurs in each30 minute period in a day?I wish to generate a report that goes something like09:00 09:30 10:00----------------------------------------pos01 3 5 3pos02 0 6 4pos03 4 3 1

View 1 Replies View Related

SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?

Jun 27, 2014

I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:

SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],

[Code] ....

What i need is:

1] to create the resultant tbl_ALLCOMBINED table

2] transform this table using PIVOT command with following transformation as shown below:

PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'

3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?

P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.

View 9 Replies View Related

Sql Query To Loop Over Data From Xml, Help!

Apr 7, 2008

Hi All. How do i loop over to extract data out of a xml parameter in order to insert that into a table along with other input. For example if the xml is <Id>1</Id> <Id>2</Id> <Id>3</Id> I want to input values into table (1,data2,data3) and (2,data2,data3) and (3,data2,data3). How do i do that?

View 6 Replies View Related

Nested Loop In SQL Query

Nov 1, 2005

Hi,I'm probably missing something obvious (either that or doing this totally wrong).I'm trying to use a nested loop to generate the following results:Unit Day1 Day2 Day3 Day4 Day5Name1 25 45 89 54 76Name2 48 54 81 74 98What I have so far is this:WHILE @FCount < @TotalFoodUnitsBEGINSELECT (SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@FCount + 1)) AS Unit WHILE @FDCount < @Days BEGIN SELECT (SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @FDCount, @pdStartDate)) AS Rev SET @FDCount = @FDCount + 1 END SET @FCount = @FCount + 1ENDAny suggestions please

View 3 Replies View Related

If, For, Loop,... In Sql Query Analizer

Dec 7, 2001

HI

I am quite new in query building so I would like to know where can I find some useful information about if, for,.. clauses to use in query analizer.
I would like to set a variable for date(datetime) and use it in if, for,...
clause to get results for each day in selected month.For example:

SELECT Uporabnik AS Expr1,
MIN(Ura) AS Expr3, MAX(Ura) AS Expr5, COUNT(*) AS Expr4, Datum AS Expr2
FROM BatchIndeksNEW
GROUP BY Uporabnik, Datum
HAVING (Datum = CONVERT(DATETIME, '2001-11-30 00:00:00', 102))
ORDER BY COUNT(*) DESC

thank's Tomaz

View 2 Replies View Related

SQL Loop Query Problem.

Jul 25, 2007

I'm a relative novice at using sql and I'm struggling with a particular problem. I'm not sure if this is the appropriate place to post this sort of thing but after much fruitless research I need to try and enlist someone's help.

I have a table of pay dates with a paid amount. I want to write a statement that creates a table that breaks those out into the daily amount paid. So it would create a table with a row for each date in the pay period and the amount paid each day.

For example: My existing table has an employee Bob who was paid $1000.00 on January 14. I want then create a table that has records for January 1 - 14 and $71.43 for each date. I think it I need some kind of loop for the whole thing to run through but I can't get the syntax right. I've thought about creating a temp table with all of the dates in that pay period but again, in order to create that temp table I feel like I need some sort of while loop that creates the rows for each date.

Any help or suggestions would be greatly appreciated. I'm at wits end .

Will

View 7 Replies View Related

Dynamic Query In While Loop

Nov 18, 2011

@strSql is a dynamic query in while loop which can return single record, single row of records, multiple rows of records.

So only if it returns single record then I have to store it otherwise convert to xml before storing.

1) If it returns 1 record(1row and 1 column) then save as it is.
2) if it returns a row with more than 1 columns then convert to xml before saving.
2) if it returns data rows then convert to xml before saving.

View 2 Replies View Related

Loop Through Result Set Using Value In Query

Mar 27, 2012

I have to pull values from a mysql table, then loop through the result set using the value in an mssql query as shown below. I also have an array ($all_lobs[]) of about 100 values that must be looped through for each value pulled from the mysql table:

$tod=date("n/j/Y",time());

//this is pulling the data from the mysql table
$query2="select CustId from prospect_CustId ";
$result2=mysql_query($query2,$link_id_mysql);
while($custs=mysql_fetch_row($result2))

[Code] .....

If I pull only 100 records from the mysql table, this takes about 1 second to run. However, if I pull 200 records, it takes about 60 secs to run. And, if I pull 300 records, it takes about 200 secs to run. After about 500 records, it takes almost a second per record to run!

Since I have 20,000+ records to pull, this takes hours...

Unfortunately, we are not allowed to modify the mssql tables at all, only query them.

View 14 Replies View Related

Avoiding Query In Loop

Jun 9, 2008

Hello all,

I currently have an asp script that is generating a 12 month rolling report. From asp I'm running a for loop with 12 iterations, each one sending the following query:

select count(a.aReportDate) as ttl from findings f left outer join audits a on a.aID = f.auditID
where f.findingInvalid <> 1 and month(aReportDate) = " & Mo & " and year(aReportDate) = " & Yr

where the Mo and Yr variables are incremented accordingly.

I actually have 4 sets of data being pulled back to populate a graph, so this results in 48 queries with each page load! Obviously not ideal. So I'm hoping to reduce this to 4 queries. I was playing with the following in enterprise manager:

DECLARE @DT DATETIME
DECLARE @CNT INT
SET @DT = '10/31/07'
SET @CNT = 1
WHILE(@CNT < 12)
BEGIN
select count(a.aReportDate) as ttl from findings f left outer join audits a on a.aID = f.auditID
where f.findingInvalid <> 1 and month(aReportDate) = month(@DT) and year(aReportDate) = year(@DT)

SET @CNT = @CNT + 1
END

I haven't yet added any logic to increment the date, but my concern is that it looks like it is returning 12 separate results. Is there any way to combine this all into one resultset that will be passed back to my asp script? Hopefully this makes sense?

Suggestions on a completely different approach would also be welcome.

Thanks!

View 2 Replies View Related

Update Table With A Loop Through A Query?

Sep 20, 2013

I want to make a SP to update table Product with information I get from table Orderdetail.

Create Procedure UpdateVoorraad
§OrderId (int)
As
Select ProductId, Tal From Orderdetail where OrderId = @OrderId

-- this query get info from table orderdetail : ProductId (integer) and Tal (smallint)

-- Tal = Number of Products

-- Here I want to loop through the query above

-- and for each record in the query I want to update

-- table Product.

Update Product Set Product.Voorraad = Product.Voorraad - Tal where ProductId = ProductId

To do this must I make a create a tempory table, store the query result in the table loop through the table and update table product, or can I try to create a function without a temporary table.

View 3 Replies View Related

Imitating Nested For Each Loop In SQL Query

May 25, 2007

Dear All,

I need to create a query to list all the subfolders within a folder.

I have a database table that lists the usual properties of each of the folder.

I have another database table that has two columns

1. Parent folder
2. Child folder

But this table maintains the parent child relationship only to one level.

For example if i have a folder X that has a subfolder Y and Z.
And Y has subfolders A and B.
and B has subfolder C and D
and C has subfolder E and F

The database table will look like

parentfolder child folder
X Y
X Z
Y A
Y B
B C
B D
C E
C F

I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.

I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.

View 3 Replies View Related

Loop By Passing Variable Into Query

Nov 2, 2007

Hi all,

I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store.

The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.

View 3 Replies View Related

Query : Running A Job/step In A Loop For ALL Databases

Jul 20, 2005

Hi,I've written a job to export user and database permissions for alld/b's on a server. As you can see below, the T-SQL commands are thesame for each d/b. Can anyone assist with regard to re-writing this sothat any new d/b's added do not require ammending the job (loop)?Thx,GC.use mastergoSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use msdbgoSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use test1goSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use test2goSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'

View 1 Replies View Related

Multiple Tables - Loop Through Query Switching Table Name?

Mar 8, 2012

I would like to run the same query on multiple tables. So say I have a list of tables

@tableList = a|b|c|d

And then I have my query looping through the tables

for (@table in tableList)
{
update from @table
set = ''
}

Is there a simple way to do this in an mssql query, if so how do I get to loop through the query switching the table name?

View 4 Replies View Related

SQL Server 2008 :: Iterate Query Using A Loop As Many As 5 Times Max?

Mar 20, 2015

If exists (select fieldID from #tmploginfo where status <> 0
group by fieldID
having count(*) > 0)
begin
backup log rdb to disk = N'C:
db1.trn'
End

I want to iterate this query using a loop as many as 5 times max.

View 3 Replies View Related

Is There A Way To Loop Over Report Parameters, And Format Them Before Passing Them Onto The Query

Nov 13, 2007

Good morning all,

I have a report which is getting its parameters from an ASP.net page. My ASP developer wants to send in simple values, such as the list 1,2,3,4 for a parameter. However my report needs that list to look like [CD RSRC].[RSRC].&[1], [CD RSRC].[RSRC].&[2], [CD RSRC].[RSRC].&[3], [CD RSRC].[RSRC].&[4].

Is there any way, on the report services side, to capture an incoming report parameter, parse it, loop over the parsed values and format them?

I don't think there is, but I wanted to check before I go back to the developer and tell him he has to send in tuple lists.

Thanks,
Kathryn

View 1 Replies View Related

Can The Need For A Reader Loop Be Eliminated In ADO If I Want Query Results To Stream Right To A File?

Oct 23, 2007

I'd like the results of my query to stream right to a file rather than be processed row by row in an ADO reader loop. Is this possible?

View 5 Replies View Related

Newbie Question: Table Polling And Select Query In A Loop

May 2, 2007

Hi,

I am a newbie in SSIS.

Can anybody please help me in the following.

Here is the task that I want to achieve:

1. continously poll a db table every 1 minute,

if the value of a paticular cell in the table has changed since last poll,

then initiate the second task

2. do a select query that picks about 10,000 new rows off another db table,

the 10,000 rows should then be stored in a in-memory dataset.

Every time the poll initiates a new select query, it should insert the new rows to the existing in-memory dataset.

thus if the select runs for 2 times in 2 minutes, the the in-memory dataset would contain a maximum of 20,000 rows.

3. Then I want to apply a set of transformations on the dataset and then finally update some db tables, push some records to the ssas database. (push mode incremental processing)

which sub tasks can be achieved and which cannot.

if not, Is there a workaround?

Please do provide some specific links that accomplish some of these similar tasks.

I have tested some functionality, like

doing a full processing of a ssas database.

reading from a database table and inserting into a flat file.

I tired to use the ExecuteSQLTask, and i also assigned the resultant to an user:variable. the execution completed succesfully but I am not able to see the value of the variable change. also I am not able to use the variable to figure out a change in previous value and thus initiate a sql select. or use the variable to do anything.

Regards

Vijay R

View 6 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Loop Query To Update Output Depending Upon Count Of Distinct Entries

Jun 25, 2013

I need to update the result depending upon the count of distinct entries.

Example

ID Employee Region State
1 ABC AMEA MI
2 DEF AMEA MI
3 XYZ APAC TX

I want the result as below

ID Employee Region State
1 ABC AMEA MI-1
2 DEF AMEA MI-2
3 XYZ APAC TX

since the count of Region is 2

I tried using DECLARE @intFlag INT and stuff but wasn't able to get the solution.

View 2 Replies View Related

SQL Server 2012 :: Write A Loop On Result Of First Query Inside A Stored Procedure

Jan 23, 2015

I have to write a Stired Procedure with the following functionality.

Write a simple select query say (Select * from tableA) result is

ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4

Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?

View 2 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related

Fishing For A Clue. To Loop Or Not To Loop

Jul 8, 2006

I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I��m trying to accomplish.

Tbltimes

|empnum | empname | Tin | Tout | Thrs |

| 1 | john | 2:00PM | 3:00PM |1hr |

| 1 | john | 2:00PM | 3:00PM | 1hr |

| 2 | joe | 1:00PM | 6:00PM | 5hr |

GridView1

| 1 | John | 2hrs |

| 2 | Joe | 5hrs |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

View 18 Replies View Related

ForEach Loop Or For Loop??

Feb 23, 2006

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

View 1 Replies View Related

How Do You LOOP In T-SQL?

Aug 20, 2007

I used to loop through recordsets with ease in old classic .asp pages.
Please Help me understand how Professionals now loop through and update tables using JUST SQL Query Analyzer using my pseudo-code provided below.
I would love to learn how to do it to better develop my skills.
SELECT * FROM zz_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Other Actuator],
[Product Interest - Chillers],
[Product Interest - Other Chiller],
[Product Interest - Electronic Products],
[Product Interest - Other network interfaces],
[Product Interest - Fittings],
[Product Interest - High Vacuum],
[Product Interest - Other high vacuum actuators],
[Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products],
[Product Interest - Training],
[Product Interest - Valves& Manifolds],
[Product Interest - Actuators]) Is Not Null
Order BY [Contact Name]

IF [Product Interest - Actuators] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]

IF [Product Interest - Other Actuator] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]

IF [Product Interest - Other Chillers] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Chiller = 1 Where [Contact Name] = [Contact Name]

View 24 Replies View Related

For Loop

Jan 12, 2008

Dear All.
Have a nice day.
I have db table, I need to update all fields of table.
Please can you write code," for loop " how can update all fields of my table by loop.
Thanks.
Zahyea.

View 3 Replies View Related

While Loop

Mar 3, 2008

Hello everyone,I've got this While loop here which is giving me a problem:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN DECLARE @ProdId int, @ProdSize varchar, @ProdQuan int SELECT @ProdId = ProductId, @ProdSize = ProductSize, @ProdQuan = Quantity FROM _ShoppingCart WHERE CartId = @CartId If @ProdSize = 'XL' BEGIN UPDATE _ProductBatches SET XL = '0' WHERE ProductId = @ProdId END DELETE FROM _ShoppingCart WHERE ProductId = @ProdId AND CartId = @CartIdEND The problem is that the IF statement isn't being executed. And I know for a fact that 'XL' is ProductSize in my _ShoppingCart database. Whats even stranger is that my delete statement is being executed. So @ProdId is Being set correctly, but when it gets to the IF @ProdSize = 'XL' it doesn't get executed for some reason. If @ProdId is being recognized correctly in my DELETE statement, why is my @ProdSize not being reconized correctly in my IF statement. I checked my _ShoppingCart database, and my ProductSize is definitely 'XL'. Can any one see what could be going on here. Thank you, Alec

View 7 Replies View Related

More Of This While Loop

Mar 4, 2008

Hello everyone...... I'm trying to do the following but am having issues:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN execute code with item......... erase itemEND In the while loop I want to execute code from each item in my _ShoppingCart and then erase them until there are no more items. However the above code gives me the error: "Subquery returned more than 1 value. This is not permitted........" It works fine when there is only one item. Does any one know what format to use when dealing with more that one entry? Thank you, Alec

View 2 Replies View Related

For Next Loop

Sep 21, 2000

hi,
I am trying to find a way of using a loop that won't be an endless loop because I have to insert parts of a string until the string reaches the end. I am unable to make the loop get to a point where the statement is false.

Is there anyway I can find out the length of the string so that I can tell the statement to loop while the statement is true?

Help!!!!!!!!!!!!1

View 1 Replies View Related

For Loop

Jan 26, 2004

HeaderLoop: for forHeader as curHeader dynamic scroll cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do
set AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(AcctNum);
set Cntr=Cntr+1
end for;

The above is the sybase version of a 'for loop' . The query
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results.
How do I change that in SQL?? Do we have a for loop ??
I checked in BOL but it is confusing with "browse" etc n some other options.

can I write like this?

for { Browse { declare curHeader dynamic cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 }
set @AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(@AcctNum);
set @Cntr=@Cntr+1
}

I duno its just my guess, can any one help me out. @Cntr and @Acctnum are declared
in the beginnning.

tks

View 14 Replies View Related