I have a dataset created in a data flow task. I want use a for each loop in the control flow area of the package to process every row individually in the dataset. How can I do this?
I am currently using a "Recordset Destination" because it says that it creates and populates in memory ADO dataset and I read somewhere that this could be used anywhere in the package. I have even assigned a variable DS which has an object datatype to the Recordset Destination task. How do I use this now in the for each loop container in the Control Flow task? I also need to know if I can use the DataReader Destination task instead as it uses ADO.NET vs just ADO for performance reasons?
Please help me with the above problem as I am working on a demo.
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.
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]
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.
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
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
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?
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.
I have a loop is running with no end point. What I'm trying to do is get the Grand total of each row where BudgetNodeID = 120. Your help is much appreciated.
AV
Set NoCount on Declare @Amt as bigint Declare @Cont as bigint Declare @Mark as Bigint Declare @Total as bigint Declare @BudgetNodeID as Bigint Declare @GTotal as bigint Set @BudgetNodeID ='120' Set @Amt = 0 set @Cont = 0 set @Mark = 0 set @GTotal = 0
While exists (Select * from xBudgetNodeCosts where BudgetNodeID =@BudgetNodeID) Begin select @Amt = IsNull(xBudgetNodeCosts.Qty,0) * IsNull(xBudgetNodeCosts.CostRate,0) FROM xBudgetNode INNER JOIN xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR pc.Status='Approved')
select @Cont = @Amt * (xBudgetNodeCosts.Contingency/100) FROM xBudgetNode INNER JOIN xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID ) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR pc.Status='Approved') select @Mark = @Cont * (xBudgetNodeCosts.Markup/100) FROM xBudgetNode INNER JOIN xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR pc.Status='Approved') -- compute the sell
select @Total = @Amt + @Cont + @Mark
-- add to grand total Select @GTotal = Sum(@Total+ @GTotal)
I need to keep the first 4 values above 80 or the first 2 values above 90. If there are not enough, I need to keep as many values as possible. Should this be done with a while loop, if so, how would it be done.
hello, i have this SP to tally up my inventory tables.. im finding a way to loop through my table tblitemdetail to get necessary parameter to be insert into my other SP (SP_StkAdj_tbl_alignmt) that should accept this params (from the itemdetail) :- @ItemID ='', @ClientID='', @CustomLotNo ='', @UDF1=NULL, @UDF2=NULL, @UDF3 =NULL, @UDF4 =NULL, @UDF5=NULL, @UDF6 =NULL, @UDF7 =NULL, @UDF8 =NULL, @UDF9 =NULL, @UDF10 =NULL, @StockID ='0950-4388', @RecvOwn ='OWN', @ConsignorID ='JAB1MY' EG:i will GROUP BY my itemdetail so it will give me the x records of data with :-
SELECT ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3, ownerstatus FROM tblItemDetail GROUP BY ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3,ownerstatus ORDER BY ItemID
with the result then, i need to insert the param into the SP:SP_StkAdj_tbl_alignmt so that it perform the calculation.
so i guess this will need some looping from the result set i get from the group by and some Sp calling from main Sp
hi all, ive no idea what's wrong with my while loop nested in IF .. it only work correctly when i remove the while from IF :-
IF @Picktype='FI' BEGIN -- Insert data into @Stage to play around DECLARE@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))
WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0 INSERT@Stage (ItemStorageID, Qty, RecvDate, BB) SELECT TOP 1t1.ItemStorageID, t1.Qty, t1.RecvDate, t1.BB FROM#DataList AS t1 LEFT JOIN@Stage AS s ON s.ItemStorageID = t1.ItemStorageID WHEREs.ItemStorageID IS NULL ORDER BYt1.RecvDate, t1.Qty DESC
IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue SELECTrecID, ItemStorageID, Qty, RecvDate, BB FROM@Stage ELSE select * from #DataList END correct result after i remove the while from inside IF
WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0 INSERT@Stage (ItemStorageID, Qty, RecvDate, BB) SELECT TOP 1t1.ItemStorageID, t1.Qty, t1.RecvDate, t1.BB FROM#DataList AS t1 LEFT JOIN@Stage AS s ON s.ItemStorageID = t1.ItemStorageID WHEREs.ItemStorageID IS NULL ORDER BYt1.RecvDate, t1.Qty DESC
IF @pickType='FI' BEGIn IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue SELECTrecID, ItemStorageID, Qty, RecvDate, BB FROM@Stage end
I have the following stored procedure which enters items bought by the usr in the database, inserting the user's ID, the item and the price. now every user has a unique id and every user can only buy three items; thus only three inputs must be inserted in the table, how can i do that? This is the current SP i have...
ALTER PROCEDURE [dbo].[spA_ALW_InsertIntoMLAGoods]
BEGIN BEGIN TRY --UPDATE MLAGoods --SET --MLAFormIDF=@MLAFormIDF, --Description=@Description, --Amount=@Amount --WHERE MLAFormIDF = @MLAFormIDF
INSERT INTO MLAGoods ( MLAFormIDF, Description, Amount ) VALUES ( @MLAFormIDF, @Description, @Amount ) END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS [ERRORMSG], ERROR_SEVERITY() AS [ERRORSEV] RETURN @@ERROR END CATCH
Hi i have a sql loop query which i have working in asp fine, i have altered it to try and get it working as a stored procedure.
but i am not sure what the syntax is.
can someone help please.
many thanks
DECLARE CURSOR GetWebOrder_cur IS SELECT O_R_ID, O_Name, O_Add_1, O_DB_Code, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_Instore, O_STC_Code, O_ID FROM [newserver].dbo.X_TBL_ORDER WHERE NewOrder = 0
BEGIN FOR GetWebOrder_rec IN GetWebOrder_cur LOOP
-- SET ALL FIELDS set R_ID34 = GetWebOrder_cur("O_R_ID") set R_Name = GetWebOrder_cur("O_Name") set R_Contact = GetWebOrder_cur("O_Add_1") set R_Code = GetWebOrder_cur("O_DB_Code") set R_Add_1 = GetWebOrder_cur("O_Add_2") set R_Add_2 = GetWebOrder_cur("O_Add_3") set R_Add_3 = GetWebOrder_cur("O_Add_4") set R_Add_4 = GetWebOrder_cur("O_Add_5") set R_Add_5 = GetWebOrder_cur("O_Add_6") set R_Add_6 = GetWebOrder_cur("O_Add_6") set R_PostCode = GetWebOrder_cur("O_PostCode") set R_CostCode = GetWebOrder_cur("O_CCode") set R_Delivery = GetWebOrder_cur("O_Service") set R_Instore = GetWebOrder_cur("O_Instore") set R_STCODES = GetWebOrder_cur("O_STC_Code") set WebOrderID = GetWebOrder_cur("O_ID")
-- GET MAX ID SELECT Max(O_ID) AS MAXOID FROM dbo.X_TBL_ORDER
-- UPDATE VIRTUAL SERVER SET NewOrder = 1 UPDATE [newserver].dbo.X_TBL_ORDER SET NewOrder = 1 WHERE O_ID = WebOrderID
-- SET CURSOR FOR ORDERLINES CURSOR orderlines_cur IS SELECT * FROM [newserver].dbo.X_TBL_ORDER_LINE WHERE OL_O_ID = @WebOrderID -- OPEN LOOP THROUGH ORDERLINES FOR orderlines_rec in orderlines_cur LOOP
-- SET ORDERLINE FIELDS set B_St_Code = orderlines_cur("OL_St_Code") set B_Description = orderlines_cur("OL_Desc") set B_Qty = orderlines_cur("OL_Qty") set B_dbcode = orderlines_cur("OL_DB_Code")
-- INSERT INTO F4 ORDERLINES INSERT INTO dbo.X_TBL_ORDER_LINE (OL_O_ID, OL_St_Code, OL_Desc, OL_Qty, OL_Allocated, OL_Despatch, OL_DB_Code) VALUES (B_preorderID, B_St_Code, B_Description, B_Qty, B_Qty, B_Qty, B_dbcode) -- CLOSE LOOP THROUGH ORDERLINES END LOOP;
I have this scenario: I have a table one database in sql called facttable_Temp with columns CustomerName, ItemKey.The fields are varchar.
I have another table called Accounts in a different database. Accounts contains fields such as CustomerName,Account. The fields are varchar.
What I need to do is to check if the values for CustomerName from table FactTable_Temp exists in the field CustomerName in the table Accounts. If it exists then I need to insert the entire row for that CustomerName including ItemKey into a 3rd table called FactTable.
What is the best way of accomplishing this in SSIS?
Thanks
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
What I am trying to do is to get balances at each month-end from Jan toDec 2004. Now I am doing it by manually changing the date for eachmonth, but I want to do all the months at one time. Is there a way toadd something like a do loop to achieve that goal? Please see my querybelow. Thanks so much!declare @month_date_b smalldatetime--B month beginning datedeclare @month_date_e smalldatetime--E month ending dateselect @month_date_b='9/1/2004'select @month_date_e='9/30/2004'select a.person_id, a.fn_accno, a.fn_bal, b.mm_openfrom fn_mm_fnbal as ajoin fn_mm_list as bon a.person_id=b.person_idand b.mm_open < @month_date_ewhere a.bal_date between @month_date_b and @month_date_egroup by a.person_id, a.fn_accno, a.fn_bal, b.mm_openorder by a.fn_accno, a.fn_bal
I have a table which has an identity column. I want to traverse through the table one row at a time using FOR Loop. Can someone help me with the syntax.
Can anyone please guide me in getting the max and minimum rows in a set while looping through the set. I know there is a way to use a while loop and preserve the value in a variable to get the max and minimum rows. Here is what I have:
As you can see I need to get one Max and one Minimum record. I want to avoid the use of cursors but was wondering if this can be easily done using a while loop and looping through it to get the next max and min values. If I have two dbs of the same size then both of them should be displayed. Sqno is unique in the table.
Hi, I'm trying to figure out a way to do this in one step with a loop or something in SSIS. I was trying to use a sql task in a foreach loop but can't figure out how to loop through each column in a row and then insert.
INSERT INTO Table1(Id,Sequence_Number,Date,TextCol)
SELECT a.Id,a.Sequence_Number,getdate(),'Changed first name value from ' + a.First_Name + ' to new value ' + b.First_Name
FROM Table1 a
JOIN Table2 b
ON a.Id = b.Id
WHERE a.First_Name <> b.First_Name and a.Rec_Type=10
GO
INSERT INTO Table1(Id,Sequence_Number,Date,TextCol)
SELECT a.Id,a.Sequence_Number,getdate(),'Changed prefix value from ' + a.Prefix + ' to new value ' + b.Prefix
I am totally missing something here and feel ridiculous but I need help. I have a DTS package that I plan to run once a month. The first task is as follows:
----Delete the records out of the table Reminder BEGIN DELETE FROM Reminder END ----Populate the table based on the following criteria BEGIN INSERT INTO Reminder (record_id, form_type, act_comp_date, resp_party, resp_party_email, subject) SELECT record_id, form_type, act_comp_date, resp_party, resp_party_email, subject FROM form_data WHERE (act_comp_date IS NULL) AND (form_type = 'Preventive Action') OR (act_comp_date IS NULL) AND (form_type = 'Corrective Action') END
Now what I want to do is send an email message to each resp_party_email record, there is usually about 5. The problem is only the last record is getting an email. Am I missing some kind of loop command here to make each record get the email? I'm guessing I need some type of loop or do while in there?
----This is what I am trying but only the last record is getting the email DECLARE @recordid varchar(10), @formtype varchar(50), @respparty varchar(75), @resppartyemail varchar(75), @subject varchar(50), @mailSTR varchar(800)
BEGIN Select @recordid=reminder.record_id, @formtype=reminder.form_type, @respparty=reminder.resp_party, @resppartyemail=reminder.resp_party_email, @subject=reminder.subject from reminder END
Just a reminder to submit an updated Work History for your Open ' + @formtype + ', record # ' + @recordid + ' - ' + @subject + ' - at least every 30 days.
Thank you, Quality Management" ' BEGIN EXEC master..xp_cmdshell @mailSTR END END
Hi, I am trying to do a loop while a list of array is assigned ('CHP,CNH,COW') ... I am using comma seperator to get each list value ... but, it donest really do what I am trying to do ... pls help!!! How do I loop through each value and do the rest ...?? ===================================== DECLARE @ABBR AS NVARCHAR(50)SET @ABBR = 'CHP,CNH,COW' DECLARE @SEP AS NVARCHAR(5)SET @SEP = ',' WHILE patindex('%,' + @ABBR + ',%', @ABBR ) > 0 BEGIN -- do the rest END
I have 2 tables, i am trying to write a query that will loop through table1, select 3 fields and populate table 2. All fields are nvarchar, using Sql2000...
