SQL Update / FOREACH Query
Apr 4, 2007
Hi Folks,
Could someone please enlighten me on the syntax I should be using to create a for-each structure within a Stored Procedure? Here's what I'm trying to do:Run through a "project" table, extracting the columns [id int], [name varchar] of each row, inserting these into the "reporting_table"Then, for each [project id] now in reporting_table, I need to calculate the value of the [total_cost numeric(18,2)] field in order to update it in the reporting_table:Run though the [orders] table (a master table), WHEN [project id] = [project id of current iteration], @totalCost += [orderTotalCost]Insert @totalCost into reporting_table's [total_cost] column.I.E. basically totalling the total cost of all orders in the order table by Project
I hope that makes sense? Maybe I'm over complicating it somehow? The reason I'm using a reporting_table is that .NET's built in GridView would be perfect for displaying this type of data, and it's very easy to bind a GridView object directly to a table.
Thanks in advance,
Ally
View 4 Replies
ADVERTISEMENT
Jul 30, 2007
Hello,
I have a SSIS package that has a SQL task which gets a result set and stores it in an SSIS object variable. The package then iterates through that result set in a foreach Loop Container. In the loop container I have a script task and a Send Mail Task. The script task sets the contents of the email message (through an SSIS String variable).
In the dataset I have an EmployeeCode column. I need to select a single EmployeeCode and grab all the records associated with that code to create a single EmailMessage for that Employee that includes all their records. I've got that done, however the way I'm implementing it I then have to update the SSIS Object variable that holds the result set to remove those records I've already processed. Doing this causes an error at the next ForEach loop iteration stating it can't find a source.
Result Set from SQL Query (16 columns, 674 rows, 145 distinct Employeecodes)
Employee Code EmailAddress Other columns
1 email1 set1
1 email1 set2
2 email2 set3
2 emial2 set4
2 email2 set5
etc etc etc
The first iteration should be sent to email1 where the body includes set1 and set2, the second iteration sends to email2 where the body includes set3, set4, and set5 and so forth.
The SSIS ForEachLoop Container is looping through the entire Result Set and passing into the .NET Script task the EmployeeCode (readonly) and the entire Result Set (readwrite) and setting the value of EmailMessage. So I create the value of EmailMessage based on the EmployeeCode and then use the value of Email1 and EmailMessage in the SendMail Task. I was trying to delete the rows I processed so I don€™t process them the next time the ForEachLoop Executes (otherwise I'll get duplicate emails).
Or if there's a different way to do this, I'm open to that as well.
See below for the code I'm using (colEmployeeCode is a ReadOnlyVariable and rsRecipients and EmailMessage are ReadWriteVariables in the Script Task).
Public Sub Main()
Dim Header As String
Dim Body As String
Dim Footer As String
Header = "blah"
Footer = "blah"
Try
Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
olead.Fill(dt, Dts.Variables("rsRecipients").Value)
For Each row As Data.DataRow In dt.Rows
If UCase(Trim(row("EmployeeCode").ToString())) = UCase(Trim(Dts.Variables("colEmployeeCode").Value.ToString())) Then
Body = Body + "Label: " + Trim(row("colum").ToString()) + System.Environment.NewLine
row.Delete()
End If
Next
Dts.Variables("rsRecipients").Value = dt
Dts.Variables("EmailMessage").Value = Header + Body + Footer
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
Thank you very much,
Aaron
View 3 Replies
View Related
Apr 4, 2007
How do you sort files from the Foreach loop container?
I love this component, except I can't get it to sort by filename.
Nice to know how to sort by timestamps also.
Dave
View 27 Replies
View Related
Apr 22, 2008
Hello
I have a question
How is foreach loop container - foreach ADO enumerator performace in SSIS package compares to use of cursors in stored procedures
Is there any articles comparing them
I understand a lot of factors can affect the performance, however what is expected performance for the foreach ADO enumerator loop for large dataset. What is Microsoft recommendation for that - recommended - not recommended (using large datasets - over million records)
Thank you
Arminr Bell
View 4 Replies
View Related
Apr 11, 2007
Hi,
I have no "Foreach File Enumerator" oprtion in the Enumerator Property of the Foreach Loop Component.
I have this enumerator in the c:Program FilesMicrosoft SQL Server90DTSForEachEnumerators folder.
Also I check it in the GAC - it does not here. I try to execute gacutil.exe -iF ForEachFileEnumerator.dll, but it is failed with "Failure adding assembly to the cache: The module was expected to contain an assembly manifest." Seems it is not managed enumerator.
Please help me.
Also information on how to regeister unmanaged enumerators are welcome!
View 3 Replies
View Related
Apr 16, 2006
Hi All
I'm sure this is a simple thing to do, but I'm new to SSIS and trying to catch up fast.
I want to execute a query on the database which will give me a path and a filespec, say:
c:apps estapp1
and
fred*.csv
No problems here.
I then want to feed them into a ForEach loop and interate through all the files matching the filespec at that location. I can't figure this out at all.
Thanks for you help in advance.
FG
View 4 Replies
View Related
Jan 30, 2006
I'm using a sql data source and I need to perform a foreach query. I have a collection of id's and I need to hit my database (the one available via sql data source) for each of the id's in the collection. I know that the sql data source returns a data view and it can be coverted to a data table. What's the best way to query a database using the sql data source ojbect for each id in a collection? Here's my code.
<code>
private void GetJobsFromPSI(JobQueueCollection jobs)
{
// Create a data view.
DataView dv = new DataView();
// Get the id's.
JobQueueCollection jobs = GetJobsFromQueue(Membership.GetUser().ProviderUserKey());
// Now that we have all of the user id's we
// need to call out the jobs from PSI.
foreach (JobQueue job in jobs)
{
// Get query for each id in the job collection.
// Somehow add a new datarowview to the data view for each
// query.
}
}
</code>
View 1 Replies
View Related
Aug 4, 2006
Ever since installing SQL Server 2005 SP1, when using the Foreach Loop
container in SSIS the Foreach File Enumerator and Foreach Item Enumerator are
missing from the Enumerator drop down box. Anyone else seen this issue? and
know how to fix it?
thanks
Eric
View 17 Replies
View Related
Mar 26, 2007
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
View 2 Replies
View Related
Sep 15, 2001
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
View 1 Replies
View Related
Jul 20, 2005
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Mar 30, 2006
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies
View Related
Nov 19, 2004
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
Dave
View 6 Replies
View Related
Jul 10, 2006
This is my first website done in ASP.NET and SQL Server 2005. I haven't ran into any major problems, until I tried to run an update on some rows. I've searched everywhere online, only to be stuck at the end of the day. I will post the code I have for the button that triggers it. I'll be crossing my fingers! int eid = Int32.Parse(Request.QueryString["id"]);
string updateSQL = "UPDATE Events SET event_title = @event_title, event_date = @event_date, ";
updateSQL += "event_time = @event_time, event_location = @event_location, event_description = @event_description WHERE (eid = @eid)";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
cmd.CommandType = CommandType.Text;
// Add the parameters.
cmd.Parameters.AddWithValue("@event_title", txtEditTitle.Text);
cmd.Parameters.AddWithValue("@event_date", txtEditDate.Text);
cmd.Parameters.AddWithValue("@event_time", txtEditTime.Text);
cmd.Parameters.AddWithValue("@event_location", txtEditLocation.Text);
cmd.Parameters.AddWithValue("@event_description", txtEditDescription.Text);
cmd.Parameters.AddWithValue("@eid", eid);
// Try to open database and execute the update.
try
{
con.Open();
int updated = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
con.Close();
Response.Redirect("./?msg=eus");
}
View 11 Replies
View Related
Feb 19, 2007
Hi everyone!I have a update query which all looks good and it looks like it executes, though it does not effect the database for some unknown reason. (No error messages).protected void Button1_Click(object sender, EventArgs e)
{
int areaId = 0;
if (Request.QueryString["doc_area_id"] != null)
{
areaId = Convert.ToInt32(Request.QueryString["doc_area_id"]);
}
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CPS_docshareConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE document_area SET doc_area_name = '" + AreaText.Text + "' WHERE doc_area_id = @areaId", myConnection);
command.Parameters.Add(new SqlParameter("@areaId", areaId));
myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
} Thanks for any help you can give, cheers, Mark.
View 8 Replies
View Related
Jul 19, 2007
I am using the update query listed below in my DetailsView. The issue i am having is the ManagerDecision value which gets populated in a DropDownList in EditTemplate, doesnt seem to update correctly. If I choose 'Approved' from the DropDownList, the result for Status_Type ends up being 'Closed' and not 'Open'. Could someone please Help.
Thanks!!
UPDATE ServiceRequest_Table SET ManagerDecision = @ManagerDecision, Comments = @Comments, Priority_Name = @Priority_Name, Status_Type = CASE WHEN (ManagerDecision = 'Approved') THEN 'Open' ELSE 'Closed' END, ManagerDateTime = CASE WHEN (Status_Type = 'Closed') THEN '' END, Closed_Date = CASE WHEN (Status_Type = 'Open') THEN '' END WHERE (ServiceRequest_ID = @ServiceRequest_ID)
View 33 Replies
View Related
Jul 23, 2007
OK, so I have this page where a user can edit multiple fields in a database record. My problem is, that once changes have been made and the user hits the "save" button, everything SEEMS to run just fine (no errors, other things that should happen when the button is clicked happen normally), but nothing is changed. It's almost like the update function (which was created with Microsoft ASP.NET Web Matrix) is working, but it's being fed the old values for the page, rather than the updated ones.
Any ideas on what might be causing this? It's not like anything I've seen before.
Thanks
Kaiti
View 13 Replies
View Related
Feb 16, 2008
I gave a storedprocedure for gatting data into gridview,just go through that.
Select Ind.Industry_Name,Com.Company_Address from Pcra_Industry Ind join Pcra_Company Com on Ind.Ind_ID_PK=Com.Ind_ID_FK Here iam getting two fields from two different table depending on the Id.
So how to write a Query to change the fields in the different tables.
please Help me...
View 1 Replies
View Related
Feb 19, 2008
Im having a problem when using my update statement on button click. I want the field to be updated to be true whenever the button was clicked but it doesn't seem to like my current syntax. Can someone point me in the right direction please? I know its really simple but when you have just been looking at something for so long you can never see it!!! Thanks
SqlCommand myCommand = new SqlCommand("Update Notice SET Resolute = '" + Resolute + "', DateClosed= '" + DateClosed + "', Resolved = 'True' WHERE NoticeID = " + NoticeID + "", myConnection);
View 3 Replies
View Related
Apr 22, 2008
Hi Guys
I have a problem with UPDATE query. Here is my Select query.
SELECT d.EID as EID,d.EName as EName,d.EDOB as EDOB,d.EDesignation as EDesignation,d.ESal as ESal,EI .EPhone as EPhone,EI .EEnddate as EndDate FROM Data_EMP d Join Emp_Info EI on d.EID=EI.EIDWhere d.EID ='1001'
According to this query I have to update another query.This is my original Update Query.
"Update Data_EMP set EStatus=0 where EID='" & TempEID & "'"
Now i wrote update query like this
UPDATE d.EID as EID,d.EName as EName,d.EDOB as EDOB,d.EDesignation as EDesignation,d.ESal as ESal,EI .EPhone as EPhone,EI .EEnddate as EndDate FROM Data_EMP d Join Emp_Info EI on d.EID=EI.EIDSet set EStatus=0 where EID='" & TempEID & "'"
Shall i write Update query like this and Is there any issues in this update query can any one let me know?
Thanks
View 2 Replies
View Related
Mar 7, 2006
Once a month I will have to run a query to update 2 fields
in one table, from another table.
Say Table1 is my main table to update and Table2 is the
table that it will update from.
Table2 is just a temporary table I will import monthly just
to update Table1.
Both tables have 2 pk/fk (userID and leaveDate).
Table1 has many of the same userID, but all different dates.
Table2 has only one instance of each userID
Example:
Table1
userID leaveDate other1 other2
1 1/1/06 6 14
1 2/1/06 2 12
1 3/1/06 <NULL> <NULL>
2 1/1/06 43 21
2 2/1/06 15 34
2 3/1/06 <NULL> <NULL>
Table2
userID leaveDate other1 other2
1 3/1/06 35 18
2 3/1/06 12 19
So Table 2 should update Table1.other1 AND Table1.other2
where userID AND leaveDate are equal
View 2 Replies
View Related
Jul 10, 2001
customerid lastname firstname emailaddress
14556928 tyler NANCY abc@hotmail.com
14556927 tyler NANCY abc@hotmail.com
I am going to create a mastercustomerid column whose value will be the min(customerid) in the above scenario.
I need a query where I can achieve this
I used the query below to achieve the above results
SELECT a.customerid, lastname, firstname, emailaddress
FROM account.Customer a Join account.Email b
ON a.customerid = b.customerid
WHERE lastname in
(
SELECT lastname
FROM account.Customer c Join account.Email d
ON c.customerid = d.customerid
GROUP BY lastname, firstname, emailaddress
HAVING count(*) >1
AND firstname = a.firstname
AND emailaddress = b.emailaddress
)
ORDER BY lastname, firstname, emailaddress
I need to update 349862 rows with a mastercustomerid . Please help me.
View 4 Replies
View Related
May 25, 2000
I have an update query which updates a field if the contents of 3 other fields in the table matches the result of a Where clause. Is it possible to only update the first matched row in the table rather than all rows that match the Where clause. Forgive if this is simple but I am a newbie to SQL 7.
Regards
Andrew Wall
View 1 Replies
View Related
Oct 26, 1999
I have the following table:
AreaName Population RankNumber
======== ========== ==========
Annerley 200 NULL
Balmoral 50 NULL
Chermside 350 NULL
Is it possible the write an update query that, after ordering the table by Population, it sets RankNumber to 1 for the first row, sets RankNumber to 2 for the second row, and so on??
The resultant table would look like:
AreaName Population RankNumber
======== ========== ==========
Balmoral 50 1
Annerley 200 2
Chermside 350 3
View 3 Replies
View Related
Oct 13, 1999
like so many i am upsizing some databases from access to sql server. how can i get the following access query to work in sql server 7.0
UPDATE dbo_t_Assignments INNER JOIN t_Assignments ON dbo_t_Assignments.AssignID = t_Assignments.AssignID SET dbo_t_Assignments.EmployeeID = [T_ASSIGNMENTS]![EMPLOYEEID], dbo_t_Assignments.DeptID = [T_ASSIGNMENTS]![DEPTID], dbo_t_Assignments.ShiftID = [T_ASSIGNMENTS]![SHIFTID], dbo_t_Assignments.JobClassID = [T_ASSIGNMENTS]![JOBCLASSID], dbo_t_Assignments.Regular = [T_ASSIGNMENTS]![REGULAR], dbo_t_Assignments.CreatedBy = [T_ASSIGNMENTS]![CREATEDBY], dbo_t_Assignments.TimeStampCreate = [T_ASSIGNMENTS]![TIMESTAMPCREATE], dbo_t_Assignments.UpdatedBy = [T_ASSIGNMENTS]![UPDATEDBY], dbo_t_Assignments.TimeStampUpdate = [T_ASSIGNMENTS]![TIMESTAMPUPDATE], dbo_t_Assignments.DateStart = [T_ASSIGNMENTS]![DATESTART], dbo_t_Assignments.DateEnd = [T_ASSIGNMENTS]![DATEEND], dbo_t_Assignments.rEmployeeID = [T_ASSIGNMENTS]![REMPLOYEEID];
thanks
Tony
View 1 Replies
View Related
Mar 15, 2001
I have a table with the following data.
CATEGORY ACCT CODE
C ABC
C XYZ
A EFG
A PQR
A LMN
B STU
D IJK
I want to update the code so that all records with the same category has the same code. It would also start with 1.
The end result would be the following:
CATEGORY ACCT CODE
C ABC 1
C XYZ 1
A EFG 2
A PQR 2
A LMN 2
B STU 3
D IJK 4
View 1 Replies
View Related
Apr 18, 2007
Hi,
I have got the following query that i want to concatenate to fields separate by a ; Can someone point out what is wrong please.
Thanks
Code:
Update wce_contact set Alt_Contact_Email = EmailAddress &';'& Alt_Email where Alt_Email <> ''
View 1 Replies
View Related
Feb 9, 2005
Maybe some one can help me...
I have 2 tables with the following structure:
CREATE TABLE [table1](
[GRID] [int] IDENTITY (1, 1) NOT NULL ,
[GID] [varchar] (10) NOT NULL ,
[RID] [int] NOT NULL ) On primary
CREATE TABLE [table2] (
[RID] [int] IDENTITY (1, 1) NOT NULL ,
[RText] [varchar] (400) NULL
) ON [PRIMARY]
If [RID] in table 1 exists more then one time (the first occurence leave as is)
and then create a new [RID] every time it is found in table2 with the RText from [RID] and update table1 with
applicable [RID] .
View 3 Replies
View Related
Jan 18, 2007
I have a query i need to write that will effect over 12,000 rows of data so i can't get it wrong! Can anyone give me some advice i can update individual records using ASP and uniqueid's but this query need to be done on SQL Exp browser.
What i have is 2 columns one called fxactivitystatus and one called activitystatus i need to copy all the fxactivitystatus fields to the activitystatus fields where the activitystatus are blank.
I would appreciate any help with this thanks.
View 8 Replies
View Related
May 19, 2008
I have a couple fields that I would like to run and Update Query on in SQL:
The Fields are called: Sample data
tp_Author | tp_Editor
785 785
785 785
758 785
What I am trying to do is update the two fields and each row of data with specific data, for example I would like the first
tp_Author
785 updated to 44
785 updated to 21
I want to update all the rows in both colums(fields) at the same time from the current data to the new data.
Will you method do the same for Dates?
PLEASESSSSSSSSSSSSS HELP Me:
View 7 Replies
View Related
Aug 11, 2004
HI Guys Wonderful to see you again and of course I have a question for ya.. I have a query that finds matching records in two tables one is the Conditional Table the other is the Termination table. What I would like to do is update the records it finds, which is basically a yes/no field. It bascially says if a terminated employee was once on a conditional they the field will say yes. and I need to update that field to yes. This query produces approximately 150 records that the Termination.tbl and the ConditionalLicense_View have in common and I need to change those 150 records Yes/No to a Yes?? does that make sense??
SELECT ConditionalLicense_View.[TM #], ConditionalLicense_View.FirstName, ConditionalLicense_View.LastName
FROM ConditionalLicense_View LEFT JOIN TERMINATION ON ConditionalLicense_View.[SS#] = TERMINATION.SocialSecurityNumber
WHERE (((TERMINATION.SocialSecurityNumber) Is not Null));
View 2 Replies
View Related
Oct 12, 2005
table1
(directoryid, firstname, lastname, extension, phonenumber)
table2
(directoryid, firstname, lastname, extension, phonenumber)
table2 is the latest table with updates, some of the fields in the extension field are nulls. i want to update table1.extension to table2.phonenumber based on the primary key directoryid
update table1
set table1.extension = table2.phonenumber
INNER JOIN table2
on
table1.directoryid = table2.directoryid
where table1.extension = '' or table1.extension is NULL.
Is my query correct ?
View 2 Replies
View Related
