How would I update a table where id = a list of ids?
Do I need to parse the string idList? I am being passed a comma seperated string of int values from a flex application.
example: 1,4,7,8
Any help much appreciated
Barry
1 [WebMethod]
2 public int updateFirstName(String toUpdate, String idList)
3 {
4 SqlConnection con = new SqlConnection(connString);
5
6 try
7 {
8 con.Open();
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = con;
11 cmd.CommandText = "UPDATE tb_staff SET firstName = @firstName, WHERE id = @listOfIDs";
12
13
14
15 SqlParameter firstName = new SqlParameter("@firstName", toUpdate);
16 SqlParameter listOfIDs= new SqlParameter("@listOfIDs", idList);
17
18
19
20 cmd.Parameters.Add(firstName);
21 cmd.Parameters.Add(listOfIDs);
22
23 int i = cmd.ExecuteNonQuery();
24 con.Close();
25 return 1;
26
27 }
28 catch
29 {
30 return 0;
31 }
32 }
33
34
Hi,New to writing sql scriptI get this error in my sql scriptServer: Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated.I want to write a single sql script which will update column 1 (FK)in table A with column 1 (PK) in table BHere's an example of what I need to doTable DATA_A-------------column C1 (Foreign Key)111111222222333333334455Table DATA_B------------column C1 (Primary Key) Column C2 Column C311 ABC NULL12 ABC 2004-12-1222 EFG NULL23 EFG 2003-12-1233 HIJ NULL34 HIJ 2003-12-1244 KLM 2005-02-0255 JJJ NULLI need to update Table DATA_A set column C1 with 11 data to point toTable DATA_B column C1 with 12 data. Currently, the problem is theTable DATA_A Column C1 is pointing to the wrong primary key which hasNULL data in COLUMN C3. I need to point to the correct Primary Key withDate filled in Column 3. The two primary key is tied together bycolumn C3.Here's my SQl scriptUPDATE DATA_A SET C1 =(SELECT C1 FROM DATA_BWHERE C2 in(SELECT B1.C2 FROM DATA_B B1WHERE EXISTS(SELECT * FROM TABLE_B B2 WHERE B2.C3 is NOT NULL)AND EXISTS(SELECT * from TABLE_B B2 WHERE B2.C3 is NULL)AND B2.C2 = B1.C2GROUP BY B1.C2HAVING COUNT(B1.C2) = 2)AND C3 IS NOT NULL)WHERE(SELECT C1 FROM DATA_BWHERE C2 in(SELECT B1.C2 FROM DATA_B B1WHERE EXISTS(SELECT * FROM TABLE_B B2 WHERE B2.C3 is NOT NULL)AND EXISTS(SELECT * from TABLE_B B2 WHERE B2.C3 is NULL)AND B2.C2 = B1.C2GROUP BY B1.C2HAVING COUNT(B1.C2) = 2)AND C3 IS NULL)Thanks - Been struggle at this for a whileMLR
I need to update a empty column in our SQL database with the login ID for employees of our company.The table is called SY01200 and were I need to put the login ID is column INET5, and the login ID is just me stripping off the company's email address(removing the @company.com), and I need to update the INET5 column only where Master_Type = 'EMP'
And here is the Query that I am using to strip the email select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp'And here is what I thought the Query would be to update however I got and error saying more than 1 arguement returned
UPDATE sy01200 SET INET5 = (select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp') WHERE Master_Type = 'EMP'
So to the above table I have added a new field/column named "ProdLongDescr(varchar, Null)"
So, I need to populate this newly added column with specific values for each row depending on "ProductCode" which is different forevery row. The problem is that I have 25 rows.So instead of Writing 25 individual update scripts, is there a way in which single query will do the same job instead of writing one update query for each row ?. If so can some one guide me how to achieve that OR point to me a good resource.
Below are a couple of Individual update scripts I Wrote. "ProductCode" is different for all 25 rows.
Update tblValAdPackageElement SET ProdLongDescr = 'Slideshows' WHERE ProductCode = 'SLID' And szElementDescr='Slideshow' if @@error <> 0 begin goto ErrPos end
Update tblValAdPackageElement SET ProdLongDescr = 'CategorySlideshows' WHERE ProductCode = 'SLDC' And szElementDescr='CategorySlideshow' if @@error <> 0 begin goto ErrPos end
First of all Thanks for all the help, I received over the years from MSDN..
Here is my new problem...
I have a SQL table like following table..
State
City
StartDt
EndDt
1
AK
ANCHORAGE
4/1/2007
12/31/2049
2
AK
ANCHORAGE
4/1/2007
12/31/2049
3
AK
ANCHORAGE
5/1/2006
3/31/2007
4
AK
ANCHORAGE
5/1/2006
3/31/2007
5
AK
ANCHORAGE
6/1/2004
4/30/2006
6
AK
ANCHORAGE
6/1/2004
4/30/2006
7
AK
COLDFOOT
10/1/2006
12/31/2049
8
AK
COLDFOOT
10/1/1999
12/31/2049
Now here is what I want to do€¦ 1> Sort the table based on Start Date (the picture shown is already sorted..) for example first 6 rows for AK - Anchorage 2> select the rows with same city (rows 1-6) 3> Select the rows with distinct start date (rows 1-3-5) 4> Change the End Date of the second selected row (row 3 in this case) to I day below the start date of 1 selected row. (4/1/2007 €“ 1 day = 3/31/2007) 5> proceed till end of selected rows.. 1-3-5 6> do the same thing for rows 2 and 4. 7> follow the same procedure for rest of the file.
The selected file should look like this when done..
State
City
StartDt
EndDt
1
AK
ANCHORAGE
4/1/2007
12/31/2049
2
AK
ANCHORAGE
4/1/2007
12/31/2049
3
AK
ANCHORAGE
5/1/2006
3/31/2007
4
AK
ANCHORAGE
5/1/2006
3/31/2007
5
AK
ANCHORAGE
6/1/2004
4/30/2006
6
AK
ANCHORAGE
6/1/2004
4/30/2006
7
AK
COLDFOOT
10/1/2006
12/31/2049
8
AK
COLDFOOT
10/1/1999
9/30/2006
Is there way to do this in SSIS? any recommened appprach>?
Any help with this is highly appreciated.. Thank You..
So to the above table I have added a new field/column named "ProdLongDescr(varchar, Null)"
So, I need to populate this newly added column with specific values for each row depending on "ProductCode" which is different forevery row. The problem is that I have 25 rows.So instead of Writing 25 individual update scripts, is there a way in which single query will do the same job instead of writing one update query for each row ?. If so can some one guide me how to achieve that OR point to me a good resource.
Below are a couple of Individual update scripts I Wrote. "ProductCode" is different for all 25 rows.
Update tblValAdPackageElement SET ProdLongDescr = 'Slideshows' WHERE ProductCode = 'SLID' And szElementDescr='Slideshow' if @@error <> 0 begin goto ErrPos end
Update tblValAdPackageElement SET ProdLongDescr = 'CategorySlideshows' WHERE ProductCode = 'SLDC' And szElementDescr='CategorySlideshow' if @@error <> 0 begin goto ErrPos end
I'm using a stored proceedure which should update a number of rows in atable depending on a key value supplied (in this case 'JobID'). Butwhat's happening is when I call the proc from within the program, onlyone row gets updated.SoWhen I call the proc from Query Analyser, all rows get updated.When I call the proc from within the program, only one row gets updatedAny ideas as to why this is happening??JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 073412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0^^^^Only one record gets updated, so the table ends up being...JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 473412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0Public Sub UpdateAllItems() As BooleanDim objCnn As ADODB.ConnectionDim objCmd As ADODB.CommandSet objCnn = New ADODB.ConnectionWith objCnn.ConnectionString = cnConn.CursorLocation = adUseClient.OpenEnd WithSet objCmd = New ADODB.CommandSet objCmd.ActiveConnection = objCnnWith objCmd.CommandText = "sp_UpdateJobItem".CommandType = adCmdStoredProc.Parameters.Append .CreateParameter("@Status", adInteger,adParamInput, 4, Me.Status).Parameters.Append .CreateParameter("@JobID", adInteger,adParamInput, 4, Me.iJobID).ExecuteEnd WithSet objCnn = NothingSet objCmd = NothingEnd Sub-----------------------------------------------------------------SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE dbo.sp_UpdateJobItem@JobID As int, @Status As intAS--================================================== ===========================================SET XACT_ABORT OFF -- Allow procedure to continue aftererrorDECLARE @error integer -- Local variable to capture theerror OnHoldAction.--================================================== ===========================================BEGIN TRANSACTIONUPDATE tbl_JobItemsSET Status = @statusWHERE JobID = @JobID--================================================== ===========================================-- Check for errors--================================================== ===========================================SELECT @error = @ERRORIf @error > 0BEGINROLLBACK TRANSACTIONENDElseBEGINCOMMIT TRANSACTIONENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with.
I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER PROCEDURE rptArFLSalesByCustItemized_sp @custId pCustID, @dateFrom datetime, @dateThru datetime, @itemIdFrom pItemId, @itemIdThru pItemId as set nocount on
-- define some variables for previous year declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime
-- set defaults SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem)) SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem)) SET @LYfrom=DATEADD(YEAR,-1,@dateFrom) SET @LYthru=DATEADD(YEAR, -1, @dateThru)
-- create small temp table to hold customer info Create Table #tmpArCustInfo ( custId pCustID, custName VARCHAR (30), ) -- populate customer temp table with info Insert into #tmpArCustInfo select custId, custName from tblArCust WHERE custId = @custId
-- create a temp table to hold the Data for each Item Create Table #tmpArSalesItemized ( itemId pItemId, productLine VARCHAR (12), pLineDesc VARCHAR (35), descr VARCHAR (35), LYQtySold int, LYTDQtySold int, QtySold int, LYTDsales money, totalSales money, LastInvDate datetime, )
-- populate the temp table with all of the inventory items insert into #tmpArSalesItemized select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL from tblInItem ii, tblInProductLine ip where ip.productLine = ii.productLine AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru
-- update table with this years quantities update #tmpArSalesItemized SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd where TransId IN (select TransId from tblArHistHeader where custId = @custId) AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru) AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru GROUP BY hd.partId )
-- Return the temp tables results select * from #tmpArSalesItemized, #tmpArCustInfo
drop table #tmpArSalesItemized, #tmpArCustInfo
return
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables.
Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things.
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
I am working on a SQL Server table designed by a partner company and cannot change the data structures. There is a table with a list of people available for calling out to a security system (keyholders).
From a web form, I need to allow my users to change the telephone calling order of the keyholders in the table.
The two important fields are AccountCode nVarChar and CallOrder nVarChar - where AccountCode + CallOrder must be unique.
As an example, the table may contain records with the following data..
1234, 1, Fred 1234, 2, Bert 1234, 3, Bob
If the user wants to make Bob the number 1 keyholder, Fred number 2 and Bert number 3 - what is the best practice for me to approach the update ?
Does anybody have a sample SQL script that will select table A and compare it to table B. If a row exists in both table A and table B, it will update the columns in table B with the columns in table A. If the row does not exist in table B, it will insert a row in table B using the row in table A. Is this possible?
I am new here, and I am sure this is a simple query, but im being forcefed database chores from my job, so i have to teach this stuff to myself/get help from places like this, I need help with a query, lets say that there are columns a,b,c,d,e,f,g if columns c,d,e are the same, than I want the info in column g changed to the info in column b in the first record of that group the reason I am doing this is, I have like items (sku's) grouped in my database, and i want to create a blanket part number for skus that have matching descriptions which is the information in colums c,d,e, I want to link them to the part number of the first product with that description, and add that part number in a new column at the end of the grouped SKU's record
this is what i start out with
a b c d e f g 2 4 5 6 9 8 2 5 5 6 9 9 2 7 5 6 0 5 1 2 3 4 5 6 1 3 3 4 5 7 1 4 3 4 5 8 1 5 3 4 5 9
i want to end up with a b c d e f g 2 4 5 6 9 8 4 2 5 5 6 9 9 4 2 7 5 6 0 5 7 1 2 3 4 5 6 2 1 3 3 4 5 7 2 1 4 3 4 5 8 2 1 5 3 4 5 9 2
I have put this thread on as a follow up discussion to some concepts that surfaced here:http://www.dbforums.com/showthread.php?t=1208316here:http://www.dbforums.com/showthread.php?t=1604936and also here:http://www.dbforums.com/showthread.php?t=1606555 The gist is: Is it ever correct excusable advisable to pass a non-normalised csv list of data to a stored procedure? When I first asked this I thought it was simply an implementation issue. Pat thinks it is but he also holds an ideological objection too. If I seem a little obsessed with this topic it is simply thata) I don't want to hijack yet another threadb) I am Anyway - I PMed Pat about this and he (quite rightly) suggested I post publicly. So here we go: I think I have struggled a little getting my head round the concepts you are using. And here (I think) is why: Normalisation starts with a logical model and ends with the physical implementation of the database (as I see it...). Relational theory pervades these two but extends further to SQL and database objects (views, sprocs etc). I work exclusively (with the exception of some legacy I stuff I simply must update) with the sproc database-API methodology. As such, my databases are abstracted from the other tiers. Now - I think this is the crux of it for me - I typically think of the sproc API as another tier (perhaps tier 1.5). Although the database itself and the objects that manipulate it are housed by a single application I don't actually think of the sprocs as part of the database. They are dependant upon it and inextricably intertwined with it but they are not a part of it in my eyes. As such, I don't worry about producing denormalised output and I have never (up until you got me thinking about it) worried about accepting non-normalised input. The sproc is not the database. The sproc is just a code procedure that acts upon the database. As such - if it accepts a typless, denormalised input but subsequently types (validates) it and normalises it before it makes any changes to the data within the database then I struggle to see the scale of the problem. I am writing as something of a stickler for normalisation myself - I think that rather than querying how closely one should adhere to normalisation principles I am pondering at what point these principles come into play. As a follow up question - if SQL Server sprocs were to accept (for example) a table variable as an argument would this meet your requirement (since the data is verified and normalised before it gets to the sproc) or would you require a solution like this to be an ANSI Standard too? So - that's the nub of it - comments welcome ona) The practicality of passing csv lists to sprocs and subsequently parsing them and b) How "correct" or "incorrect" this is. Blindman and Pat both object on both counts (I think).Thrasmachus didn't have a problem with it (however to be fair that was a very early response to a specific question I asked). I don't hold a particularly strong opinion either way – in fact the “loop through the changes at the client and fire the sproc n times” route is probably much easier to code in any case - I'm just interested in the idea that it violates relational theory in some way.
Hi, I need to update column week14 in table PastWeeks with data from Eng_Goal and then result of some calculation from table AverageEngTime in the row Goal and Used respectively. I used the following and was not successful. Please advice. Thank you.
Hi, I am preparing a project that contains multiple tables, because of huge operations to be performed, so i split these into two forms. I used one form for "Inserting Data in the multiple tables" and another form used for "Updating and Deleting Data from the multiple tables". I used a common identification number in all the tables are "License_Number" for inserting, retreving, updating and deleting the data. I used tabcontrol to place lot of controls on it.
The form-1 which is used for Inserting, works fine. The problem occuring in the second form which i used for updating.
The following code fill the data into the Textboxes and as well as in the datagridview. Below two tables "SetBack_Details" and "Far_Details" are fill the data in datagridview.
Private Sub FillDataFromAllTables(ByVal LicenseNumber As Long)
Try 'Fill Owner Personal Details Data CheckOwnerID = RetrieveOwnerIDFromPD(LicenseNumber) Me.Owner_Personal_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Owner_Personal_Details, CheckOwnerID)
'------------------------------------------------------------------- 'Fill Project Details Data Me.ProjectDetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.ProjectDetails, LicenseNumber)
'------------------------------------------------------------------- 'Fill Consultant Details Data Me.Consultant_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Consultant_Details, LicenseNumber) 'Fill Applicant Details Data Me.Applicant_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Applicant_Details, LicenseNumber) '------------------------------------------------------------------- 'Fill Property Details Data Me.Property_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Property_Details, LicenseNumber) '------------------------------------------------------------------- Me.SetBack_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.SetBack_Details, LicenseNumber) '------------------------------------------------------------------- Me.FAR_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.FAR_Details, LicenseNumber) Catch ex As Exception MessageBox.Show(ex.Message.ToString, "Project Details") End Try End
The data which i retrieves in a single record easily updated but problem occurs in DataGridView.
The data which binds to textboxes is easily updating because of a single record. Example is below UPDATE PROJECTdETAILS SET PNAME=@PNAME, ZONE=@ZONE, ZONEAREA=@ZONEAREA WHERE LicenseNumber=@LicenseNumber
The data which binds to datagridview is problem. I know the following statement is not appropriate to update the data rows in datagridview. UPDATE SETBACK_DETAILS SET DIRECTION=@DIRECTION, BORDERS=@BORDERS, DIMENSION=@DIMENSION WHERE LICENSENUMBER=@LICENSENUMBER What happening to the above statement is , all rows with this LICENSENUMBER contains same data. Before updating data contains in different information but after updating which ever last row is updating, it contains the last row values to all records.
Example : 101 xyzcompany X-Road X-Area 101 xyzcompany X-Road X-Area 101 xyzcompany X-Road X-Area 101 xyzcompany X-Road X-Area 101 xyzcompany X-Road X-Area . . The following code i used to save the data Private Sub SaveData() Me.ValidateChildren() Me.ProjectDetailsBindingSource.EndEdit() 'EndEdit for Project Details Me.Consultant_DetailsBindingSource.EndEdit() 'EndEdit for Consultant Details Me.Applicant_DetailsBindingSource.EndEdit() 'EndEdit for Applicant Details Me.Property_DetailsBindingSource.EndEdit() 'EndEdit for Property Details 'Me.SetBack_DetailsBindingSource.EndEdit() 'Endedit for SetBack_Details
Me.ProjectDetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.ProjectDetails) 'Update Project Details Me.Consultant_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Consultant_Details) 'Update Consultant Details Me.Applicant_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Applicant_Details) 'Update Applicant Details Me.Property_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Property_Details) 'Me.SetBack_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.SetBack_Details) End Sub
Please help me in the regard how to update the data in DataGridView which contains multiple recrods with different information for a single LicenseNumber. I try so many times with different sort of techniques but i fails, sometimes i get concurrency violation error and many more. Hope anybody understand my problem. Please help me in this regard.
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part | 1 | RAM 2 | Disk Drive 10| CPU 11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive| ---------------------------------------------- A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item SELECT SYSTEM PART FROM System Info WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
I am using ASP.NET 2.0 WebForms and I was trying to use a DataSet to add rows programatically without adding the actual records to the MS SQL Server Databases. Is this possible or should I be doing this another way? DataSet myDS = new DataSet();DataTable myTable = new DataTable("table1");myTable.Columns.Add("col1", typeof(string));myDS.Tables.Add(myTable);myTable.Rows.Add("MyValue"); Thanks.
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
I have a sql data source that is filtered by a date range. The results are then presented in a Gridview. What I am hoping to do is add a button that will update all the filtered rows in the sql data source. Is this possible? The data source is shown below: <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:MYSTR%>" SelectCommand="SELECT * FROM [dbo_cheques] WHERE (([chq_banked] >= @chq_banked) AND ([chq_banked] <= @chq_banked2))" UpdateCommand="UPDATE dbo_cheques SET chq_printed = @chq_printed WHERE (chq_id = @chq_id)"> <SelectParameters> <asp:ControlParameter ControlID="Calendar1" Name="chq_banked" PropertyName="SelectedDate" Type="DateTime" /> <asp:ControlParameter ControlID="Calendar2" Name="chq_banked2" PropertyName="SelectedDate" Type="DateTime" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="chq_printed" /> <asp:Parameter Name="chq_id" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource>
I need to update about 1.3 million rows in a table of mine. I am getting the data from one of the columns of the same table and updating the new column. I am doing this using a cursor which I have put in a stored procedure. As this is a production table which users might be accessing.It is a web based application and I can't slow the system down. So I am willing to run the stored prcedure during off peak hours. However, do I need to put this in a transaction? If I did put it in a transaction what type of isolation level should I opt for? Data integrity is very important for me and I don't mind to compromise on the performance. I am doing this because one of the columns which has "short description" entry is has become too small for business purposes and we want to increase it's length from varchar(100) to varchar(150). As this is SQL 6.5, I can't increase the lenght of the column. So Iadded a new column and will run the stored proc. What precautions are to be taken? This is on a high priority basis and very important too.
Thanks in advance...
Stored procedure code:
USE DB_Registration_Dev GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='usp_update_product' AND TYPE='P') DROP PROCEDURE usp_update_product GO CREATE PROC usp_update_product AS DECLARE @short_desc varchar(100) DECLARE @prod_id int
DECLARE sdesc_curs CURSOR FOR SELECT [Product].[product_id] , [Product].[short_description] FROM Product
OPEN sdesc_curs
FETCH NEXT FROM sdesc_curs INTO @prod_id, @short_desc
WHILE @@FETCH_STATUS = 0 BEGIN UPDATE Product SET [Product].[sdesc] = @short_desc WHERE Product_id=@prod_id FETCH NEXT FROM sdesc_curs INTO @prod_id, @short_desc IF @@FETCH_STATUS <> 0 PRINT ' Finished Updating the table...go ahead and have fun ...! ' END DEALLOCATE sdesc_curs GO
Hi I haven't included DDL etc as this is theoretical at this time. I can rustle up an illustrative example if required.Following applies to disconnected environment. In general, most edits are broad (i.e. n fields affected) against a single record. However, there are certain circumstances where a single field will be edited against a deep (n) set of records. So business request:User needs to access and edit n records at a time (for arguments sake n is unlikely to be enourmous - say 50 max) but only editing one single field, and always the same field. The values for this edited field will differ for each record. Initially retrieving data for the app is no problem, nor is identifying those records that have been edited. What is the best means of updating the table? There are, to my mind, three ways of dealing with the latter senario - 1) Client calls the server n times editing a record at the time.2) Client creates a csv string and passes to sproc. Sproc parses string using some UDF split function (probably chucking into table variable) and updates using a single set set based operation.3) Client creates some flat file or other that is BULK inserted (or similar) by SQL Server. 3 - I think would only be an option if n was a very large number and or having the changes immediately reflected in the data is not priority.1 - would create a chatty app and presumably put the most load on the server. So I am left thinking 2 is (depending on circumstances) the best method. Is this fair? Are there any particular considerations gotchas I should be aware of? I know where to get hold of as many TSQL split functions as I could want so I'm not looking for code just opinions. OR - am I just plain wrong? Is there a better alternative or am I dismissing the other two methods prematurely? Thanks in advance