Updating Only Changed Fields
Sep 6, 2007
My client wants to minimize concurrency issues by only updating fields that the user actually changed. Whereas most update stored procedures I have created just updated all of the fields WHERE key=value, they want to update individual fields.
What I came up with so far does not look very efficient and I was wondering if anyone has suggestions for improvement.
Here is a snippet from the stored proc script:
Code Snippet
CREATE PROCEDURE dbo.DriverSaveChangedColumns
@DriverNumber VarChar(10) OUTPUT,
@CompanyName VarChar(50),
@DriverName VarChar(50),
@Address1 VarChar(50),
...
IF @CompanyName <> @Org_CompanyName
BEGIN
UPDATE Driver
SET DR_CompanyName = @CompanyName
WHERE DR_DriverNumber = @DriverNumber AND DR_CompanyName = @Org_CompanyName
END
IF @DriverName <> @Org_DriverName
BEGIN
UPDATE Driver
SET DR_DriverName = @DriverName
WHERE DR_DriverNumber = @DriverNumber AND DR_DRiverName = @Org_DriverName
END
...
Any suggestions?
The only other thing I thought of is to forget the stored procedure and do inline SQL ... but I'd rather have a proc.
Thanks!
View 15 Replies
ADVERTISEMENT
Mar 6, 2008
Ha all again.
I have this issue:
I have 2 tables:
t1 (id,field1,field2,field3)
and t2 (id,field_name,field_value)
is possible with trigger to insert only changed fields on t1 to t2 like
(changing field1 on t1 with id 21 cause a new row in t2 with fields id=21,field_name="field1", field_value=field1.value)
i hope you understand what i look for.
Is this trigger solution ?
CREATE TRIGGER MAP_CHANGESON t1AFTER INSERT,UPDATEBEGIN
DECLARE @ID SOMEDATA;DECLARE @FIELD1 SOMEDATA;DECLARE @FIELD2 SOMEDATA;DECLARE @FIELD3 SOMEDATA;SET @ID=(SELECT ID FROM INSERTED)SET @FIELD1=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD1<>T2.FIELD1)SET @FIELD2=(SELECT FIELD2 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD2<>T2.FIELD2)SET @FIELD3=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD3<>T2.FIELD3)IF @FIELD1<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD1')IF @FIELD2<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD2','@FIELD2')IF @FIELD3<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD3')RETURNEND
View 7 Replies
View Related
Jul 20, 2005
Hi All,I'm a relatively newbie to SQL Server 2000, having come from a MySQLbackground.I'm creating my first Trigger statement on a table, and I'd like toknow how I go about performing an update on the row that was changedwhen the trigger was fired.To explain, I have 2 columns, one which contains a member number, theother which contains a flag that is supposed to indicate whether ornot the member number in the row has changed since the last time thetable was processed for updates.So, whenever the value in the member number field [memnum] is updated,I want to set the flag [igproc] to true.The best I've been able to do is:CREATE TRIGGER [updateignoreprocflag] ON [dbo].[dd_testtable]FOR UPDATEASdeclare @key as intIF UPDATE (memnum)select @key = recid from insertedUPDATE dd_testtable set igproc=1 where recid=@keyThis seems to work, but I'd like to know if there's a better way ofretrieving the recid value of the changed row to pass to the UPDATEstatement? Also, I read somewhere in passing that using SELECTstatements and variable assignments within triggers can cause problemswhen called from other applications; in this case it will either be aweb site using ASP.or an application developed in FOXPRO. I can't findwhere I read this originally, so it's entirely possible I imagined itor misunderstood it, but I'd very much appreciate it if someone couldconfirm whether or not this is the case?Many, many thanks in advance!Much warmth,Murray
View 2 Replies
View Related
Mar 3, 2006
hi ,
I recently moved from SQL 2000 to SQL 2005.
The client side is vb6 and using Dcom dll's hosted on the db server.
I have a table which has oninsert trigger
When the recordset is updated in the com class it throws the foll error :
"Row cannot be located for updating. Some values may have been changed since it was last read."
The same code was working with SQL 2000 !!!
Any clues.
implicit tran are off on the sql 2005 server.
Thanks in advance.
View 2 Replies
View Related
Oct 24, 2006
I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.
SqlConnection1.Open()
strSQLu = "UPDATE table1 " _
& "SET Field1Tag = @Field1Tag, Field2Tag = @Field2Tag " _
& "WHERE (Field1Tag = @Field1Tag) "
cmdCategoriesUpdate.CommandText = strSQLu
With cmdCategoriesUpdate
.Parameters("@Field1Tag").Value = txtFld1.Text
.Parameters("@Field2Tag").Value = txtFld2.Text
End With
cmdCategoriesUpdate.ExecuteNonQuery()
SqlConnection1.Close()
View 3 Replies
View Related
Jan 15, 2007
Hello,I'm creating an audit table and associated triggers to be able to captureany updates and deletes from various tables in the database. I know how tocapture the records that have been updated or deleted, but is there any waythat I can cycle through a changed record, look at the old vs new values andcapture only the values that have changed?To give you a better idea of what I'm trying to do, instead of creating acopy of the original table (some tables have many fields) and creating awhole record if a type or bit field has been changed, I'd like to onlycapture the change in a single audit table that will have the followingfields;AuditID int INDENTITY(1,1)TableName varchar(100)FieldName varchar(100)OldValue varchar(255)NewValue varchar(255)AuditDate datetime DEFAULT(GetDate())Any direction would be greatly appreciated.Thanks!Rick
View 6 Replies
View Related
Sep 22, 2005
How can I update each record in a table, based on a value in another tablewith a single SQL statement?For example, suppose I have the following two tables:Table1Name Something Color-----------------------------------------John GHAS BlueJohn DDSS BlueJohn EESS BluePaul xxxx RedRingo HJKS RedRingo FFFS RedSara hjkd PurpleSara TTHE PurpleJimi sdkjls GreenTable2Name Color------------------------John ?Paul ?Ringo ?Sara ?Jimi ?How can I update the color field in table 2 to correspond with the colorfield in table1 (so I can normalize the db and delete the color field fromtable1)?I know I could open table2 and loop through within my app; just wonderingabout a single SQL statement that would do it. I need a similar technique inother places as part of my app.Thanks,Calan
View 5 Replies
View Related
Jul 20, 2005
I am trying to update text fields where the field only has the value"closed." So a field that has closed and anything else will not beupdated.update dbo.Histset dbo.Hist.Notes = ''FROM dbo.HistWHERE (Notes LIKE 'closed_')update dbo.Histset dbo.Hist.Notes = ''FROM dbo.HistWHERE (Notes LIKE 'closed%') and len(convert(varchar(7000),notes)) = 7Neither code works and i get this error: "Subquery returned more than1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.The statement has been terminated."What's another way to make the update?
View 1 Replies
View Related
May 8, 2008
I am trying to make a field that has info from othe fields
Table = Page0
Fields = LName, FName, SS
I want a new field (Folder) to be all three fields.. for example
LName= Smith
FName= John
SS= 1234
I want to update Folder = Smith_John_1234
View 5 Replies
View Related
May 30, 2014
I have a table where one of the fields contains XML as in the following. Is there an SQL update statement that can change any occurrences of timezoneIdfrom 0 to 1, and timezone from America/New_York to America/Chicago and time to time - 1 hour?
<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>22:30:00</time><timezoneId>0</timezoneId><timezone>America/New_York</timezone></schedule></scheduleitem>
After the query is run, rows like the above would be changed to
<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>21:30:00</time><timezoneId>1</timezoneId><timezone>America/Chicago</timezone></schedule></scheduleitem>
View 6 Replies
View Related
Sep 30, 2015
I need to build an update query for all my article beginning with '0.%' for my varchar field refkey, but depending on some conditions.
For example.
Case st.base <> '' and st.qttbase <> 0
Theo refkey = B
Case st.cos <>'' and st.qttcos <> 0
Then refkey = C
Case st.refo <> '' and st.qttrefo <> 0
Then refkey = R
But my problem are That i can have Many combination on refkey, for example:
B,BC,BCR,C,CB,CBR,R...and so on.
Also i need to separate the letters with comma, like:
B
B,C
B,C,R
.....
View 6 Replies
View Related
Apr 30, 2015
I am trying to multiple update records in Table B from a single record in Table A. To identify the records that need to be updated I used this:
Select
Table1.cfirstnameas'Table
1 First',Table1.clastnameas'Table
1 Last',Table1.ifamilyid,CR.icontactid,CR.lLiveswithStudent,
Table2.cfirstnameas'Table
2 First',Table2.cLastNameas'Table
2 Last',Table2.ilocationidas'Table
2 Location',Table1.iLocationIDas'Table
1 Location'
fromTable1
JoinTable3
CRonTable1.istudentid=CR.istudentid
JoinTable2
onCR.icontactid=Table2.iContactID
whereCR.lLivesWithStudent=1
andTable1.ifamilyid>0
andTable1.ilocationid<>Table2.iLocationIDandTable1.lCurrent=1
I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2. I needed Table 3 to make the connection from Table 1 to 2.
View 2 Replies
View Related
Sep 21, 2006
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
View 7 Replies
View Related
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
View 3 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Jan 26, 2008
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
View 1 Replies
View Related
Jul 23, 2005
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies
View Related
Jul 2, 2015
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
View 3 Replies
View Related
Jul 20, 2005
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies
View Related
Jun 30, 2000
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
View 2 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
Jan 21, 2008
Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
View 1 Replies
View Related
Dec 7, 1999
Is there a way to produce a query to look at when and who changed database objects last?
View 1 Replies
View Related
Apr 9, 2003
I have a table as part of a replicated database where i need to change the allow nulls value (set it to not allow nulls) but when i try to save the table i get this error.
'CONTACTS' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.CONTACTS' because it is being used for replication.
will i have to stop replication to make the change or is it worse and i have to delete the publication and start all over again?
View 7 Replies
View Related
Jun 19, 2001
I have an IDENTITY value in a table that was incorrectly created, and needs to be modified. Can this be accomplished?
Thanks.
View 1 Replies
View Related
Feb 1, 2008
hi all
im really have a problem in my project.
i have server and client side each side contain SQL Server DB.
and i have excel file on the server side this excel file conected with another server, this file changed data in continuosly each less than 1 sec by data feed.
now i need to read each changed data cell from this file to save it on server DB and Client DB (just changed data).
my problem :
u know changed event is not fired when change cell by data feed or not edit manually, just calc event is rised . but calc event do not specify the changed cell range (address).
so i do this to know changed cell range:
when run the program saved all excel tabel into SQL server table.
and then check row by row between excel and sql if any change , when i get any change, i update the excel row insted this DB row.
and rise event to send this row to client by socket over internet to update the row in client side too.
but i tell u that the excel file updated each less than 1 sec, and i noted that many changed excel data missed until checked row by row for whole excel sheet with DB tabel and updated change.
this is my problem ( please help me as soon as posible coz i have dead line to Delivered this program)
and if u recomended me for another techneque to be easy or quickly to solve this problem i will thaks so much for u)
thank u.
AL-Khateeb
View 3 Replies
View Related
Jan 22, 2007
Maybe somebody has asked this question, but I could not find it....
I used SSIS to transfer data from Oracle database to SQL Server 2005 (with Microsoft OLE DB Provider for Oracle). It worked fine with the TNS alias name said oracle1. Then I changed the TNS alias name to oracle2. I could connect to Oracle database with oracle2 using Oracle's tool. But SSIS could not find (or solve) oracle2, if I changed the name back to oracle1 in SSIS, then SSIS worked.
Any idea?
Thanks!
View 7 Replies
View Related
May 21, 2007
Hello,
I have developed a report based on a stored procedure which will generate an excel file. There is a requirement that this file needs to be sent to an external domain with the name changed accordingly i.e., a date is to be suffixed to the file after the name has been changed based on their naming convention.
I would appreciate, if anybody has done so and can give me some steps for me to work out.
TIA
View 3 Replies
View Related
Nov 6, 2006
I have a page that uses an SQLDataSource web control to populate a GridView control. The SQLDataSource control uses three parameters, one of which is a text string (varchar(50)). I get timeout expired exceptions when I run the page with two specific string values, yet when I run the stored procedure in SqlSvr Management Studio, the procedure runs in 5 seconds and under 1 seconds. Being rnew to ASP.NET 2.0 and .Net in general, I have no idea where this lag is occurring. I would like to know two things:
1. What is causing the lag that causes the timeout in ASP.NET 2.0.
2. What can be done to adjust the timeout period SQLSVR, IIS, ASP.NET).
The underlying query is running as desired (considering the data it has to chew on). For all other values, data is returned almost instantanious. I can provide a copy of the query id need be.
Truly appreciate any insight you can give me!
View 2 Replies
View Related
Jun 3, 2004
i changed my state table from char 20 to varchar 20 and it still fills it with empty space at the end of the statename
I tried entering it with the trim command still no good
i looked a the sql command made in vs and it says that the variables are varchar
is there something i am missing with varchar
does it only work properly if it is over 50 or does it not change completely when you change from char to varchar
View 2 Replies
View Related
Mar 4, 2002
Is there any way to reconfigure SQLSERVER if the name of the server was changed after the initial installation of Sql was completed?
View 2 Replies
View Related
Apr 22, 2003
Hello,
Soon, we are going to redesign our servers and their positioning insofar as web access is concerned. The SQL Server will go to a DMZ.
Its IP Address (as well as hostname) is to be changed. Can you offer suggestions about what to be aware of on these two items?
I've been involved in a hostname change befoew (and, it's a pain), but never an IP Address.
Thanks, MichaelG
View 2 Replies
View Related
Aug 8, 2005
i have table Table1 with column lastname ,firstname ect.The Data type is varchar.
I have inserted values. I have selected records from this table and assign this records to the TDC control of the HTML .The query returns 130 records.The TDC control has the Unicode delimiter. Whene we select top 7 ,seven records are displayed ,when it is top 8 it does not show any records.
If we delete the Lastname and Firstname values for this 8th record then all the 130 records are displayed.
Why is this happning.Any great minds can help me in this pls
View 3 Replies
View Related