I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc SET stc.[Talks] = fmt.found_talks_type FROM @Find_Missing_Talks fmt WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks) AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
I'm looking to update a field in a table variable with the results from a SQL query. The SQL query howver needs to use the ID filed of the table variable which has already beeen populated.
Effectively, for each ID in the table variable run the query using that ID field and then pass the scalar result into another column.
I'm having a bit of trouble doing this. Can anyone help?
I am writing a query to update table variable. It is throwing me some error.
I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table
update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID
I am using a local variable to capture datetime and then select records from another table by making use of the above local variable result. But the query is running too slow when I use the local variable
declare @a smalldatetime select @a=last_run_time from job_status where job_des='sample'
SELECT * FROM History WHERE CHANGE_DATE> @a
Instead of the above select statement if I use the below statement it is returning results quickly. Can someone help me in tuning the above query. SELECT * FROM History WHERE CHANGE_DATE> '5/23/2008 6:22:00.000 AM '
History table has columns ( case number, change_date, change_desc). I have two indexes defined on the above table one is on case_number and the other on change_date.I tried using force index but still the query is running slow.
UPDATE TABLE SET FIELD1=NULL WHERE FIELD2=something - is running veryvery slow. Table has 200 000 recors and on FIELD2 is index. If number of records to be updated is about 40 000, it takes about 3-4 hours.
I tried this: - create a new MSSQL DB. - migration of TABLE from original databes to new test DB - So, in new DB was only one table, with 200 000 records. Index was only on FIELD2. - I ran that update and it takes about 3-4 hours
I tried that on Interbase and update takes under ONE SECOND!!!!!!!
I have the following stored procedure that I use for an update. The table now has just over two million rows and the stored procedure takes days to run. I am looking for any help that would produce the same results faster. This runs on SQL Server 6.5, on an NT machine with 4 pentium pro 200 processors and 512 MB of RAM so I am relatively sure that the performance issue is in the below statement.
UPDATE PAY_CHECK_DETAILS SET JobID = j.JobID ,HROrganizationID = j.HROrganizationID ,JobDetailID = j.JobDetailID ,GLAccountNumber = j.GLAccountNumber FROM JOBS j, PAY_CHECK_DETAILS p WHERE j.EmployeeID = p.EmployeeID AND j.HRActionEffectiveDate = (SELECT max(HRActionEffectiveDate) FROM JOBS jj WHERE p.EmployeeID = jj.EmployeeID AND jj.HRActionEffectiveDate <= p.PayDate)
AND j.HRActionSequence = (SELECT max(HRActionSequence) FROM JOBS jjj WHERE p.EmployeeID = jjj.EmployeeID AND jjj.HRActionEffectiveDate = (SELECT max(HRActionEffectiveDate) FROM JOBS jjjj WHERE p.EmployeeID = jjjj.EmployeeID AND jjjj.HRActionEffectiveDate <= p.PayDate)) AND NOT (ISNULL(p.JobID,1) = ISNULL(j.JobID,1) AND ISNULL(p.HROrganizationID,'A') = ISNULL(j.HROrganizationID,'A') AND ISNULL(p.JobDetailID,1) = ISNULL(j.JobDetailID,1) AND ISNULL(p.GLAccountNumber,'A') = ISNULL(j.GLAccountNumber,'A'))
Thanks for any help or suggestions you have. Keith
I have designed a 22 table database in sql server that is to act as a backup/alternate access to the data we have stored in an ADABAS database. I've also written a vb.net console program that will take data from ADABAS through a broker connection (one row at a time), checks the sql server database to see if that information is already stored, and then either performs an insert or an update.
I can write the rows from ADABAS to a text file (not using the broker), at the rate of about 1.3 million rows in 1.3 hours. Data can be imported (I'm not sure how this import is done, possibly via a CSV file. SELECTS/UPDATES are not done, just INSERTS) at about 1 million or so an hour. But when I do the update, receiving information via the broker from ADABAS to the VB program (with it's SELECT, then UPDATE/INSERT), I'm only doing about 20-25 thousand rows an hour.
I ran a trace using the SQL Server Analyzer on the database while running the update program, and then ran Profiler using the generated workload. It created a few indices, but I just restarted the update program (I'm still developing, so I delete all rows from all tables each time I rerun the update), but I haven't seen that it's really any faster.
I have a rather large set of data to transfer over, and this 20-25 thousand row time is not nearly fast enough.
I have the following statement that takes quite a long time. Longestof any of my SQL statment updates.UPDATE F_REGISTRATION_STD_SESSIONSET PREVIOUS_YEAR_SESSION_ID = (SELECT s.previous_year_session_idFROM F_REGISTRATION_STD_SESSION R,DS_V4_TARGET.dbo.D_H_Session_By_SessQtr SWHEREr.STUDENT_ID = F_REGISTRATION_STD_SESSION.STUDENT_IDand s.previous_year_SESSION_ID = r.SESSION_IDand s.session_id = F_REGISTRATION_STD_SESSION.SESSION_ID)STUDENT_ID varchar(20) and SESSION_ID char(10) and are indexedWhat I want to accomplish:I want to know if there was a student registration from the prior yearof a registration.Example, if there is a registration for Fall 2004, was there also aregistration for the same student Fall 2003?Maybe there is a better way to approach this?TIARob
I have a table with 110 nvarchar (255) columns in the database. I receive the data from the server (array) and them loop through it to insert the data into the database. The problem is it takes more than 3 minutes to insert (or update) (in memory) the 310 records it got from the array. I am reusing the Statement (with parameters). How could I improve the performance?
It's really a small quantity of data, the CPU in the device is 520Mhz... it should be alright!
I'm having what you might call an optimisation issue - but I'm also not sure if my approach to this problem is right. I've spent the whole day trying various methods but none seem to be performing as admirably as I'd hoped.
Basically, here's the scenario:
1. Log files are being inserted into a SQL table via Log Parser 2.2. 2. I have a lookup table of ip address ranges to countries and other geographic data. 3. Once the log row has been inserted from Log Parser, I want to update the same log table with data from the lookup table.
The main problem seems to be the updating (the initial insert from Log Parser is lightning quick).
I initially wrote a trigger on AFTER INSERT on the log table that converted the actual user's IP address into IPNumber format using a simple algorithm, then pumped the IPNumber into a quick select query which retrieved the geolocation data. Then, in the same trigger, there was an update statement which basically said:
update dbo.Logs set [Country] = @Country where [IpNumber] = @IpNumber and [Country] = Null
Therein lies the problem. The update statement slows everything down to almost a standstill and also seems to obtain a lock on the table.
Critical factors:
1. The log table must remain readable. 2. The query must execute in seconds -- not over half hour :)
I've experimented with various combinations of indexing, so far to no avail.
I have two tables:T1 : Key as bigint, Data as char(20) - size: 61M recordsT2 : Key as bigint, Data as char(20) - size: 5M recordsT2 is the smaller, with 5 million records.They both have clustered indexes on Key.I want to do:update T1 set Data = T2.Datafrom T2where T2.Key = T1.KeyThe goal is to match Key values, and only update the data field of T1if they match. SQL server seems to optimize this query fairly well,doing an inner merge join on the Key fields, however, it then does aHash match to get the data fields and this is taking FOREVER. Ittakes something like 40 mins to do the above query, where it seems tome, the data could be updated much more efficiently. I would expectto see just a merge and update, like I would see in the followingquery:update T1 set Data = [someconstantdata]from T2where T2.Key = T1.Key and T2.Data = [someconstantdata]The above works VERY quickly, and if I were to perform the above query5 mil times(assuming that my data is completely unique in T2 and Iwould need to) it would finish very quickly, much sooner than theprevious query. Why won't SQL server just match these up while it ismerging the data and update in one step? Can I make it do this? If Iextracted the data in sorted order into a flat file, I could write aprogram in ten minutes to merge the two tables, and update in onestep, and it would fly through this, but I imagine that SQL server iscapable of doing it, and I am just missing it.Any advice would be GREATLY appreciated!
I have a linked server set up on my local SQL 2000 instance. I try and run an update against an SQL 2005 database and it take 29 seconds. I checked the execution plan and it says it takes the entire time on the Remote Scan. Is there something I need to do to speed this up? There is an index on the PK that I am searching against.
The following basic UPDATE SQL statement has been running for 16 hours and counting. I need to get this done ASAP.
UPDATE Recipients SET UndeliverableTime = getdate() FROM Recipients INNER JOIN Domains ON (Recipients.DomainID = Domains.ID) INNER JOIN Undeliverables ON ( Recipients.UserName + '@' + Domains.Domain = Undeliverables.EmailAddress)
Is there any way I can see how far this has gone and how long it will take to finish? Will this take another hour to finish or another week?
Both tables (Recipients and Undeliverables) have approximately 80 million records
I did a nearly identical operation with another table that had only 7 million records and it took 10.5 hours. I hope this doesn't scale linearly to 115 hours.
I am tempted to cancel, retune, and rerun but that may be trigger a really expensive rollback operation that could take days. Any ideas?
am using FOR UPDATE triggers to audit a table that has 67 fields. Myproblem is that this slows down the system significantly. I havenarrowed down the problem to the size (Lines of code) that need to becompiled after the trigger has been fired. There is about 67 IFUpdate(fieldName) inside the trigger and a not very complex selectstatement inside the if followed by an insert to the audit table. WhenI leave only a few IF-s in the trigger and comment the rest of thecode performance increased dramatically. It seems like it is checkingevery single UPdate() statement. Assuming that this was slowing downdue to doing a select for every update i tried to do to seperateselects in the beginning from Deleted and Inserted and assigningcolumns name to specific variables and instead of doingif Update(fieldName) i didif @DelFieldName <> @InsFieldNamebeginINSERT INTO AUDITSELECT WHAT I NEEDENDThis did not improve performance. If you have any ideas on how to getaround this issue please let me know.Below is an example of what my triggers look like.------------------------------------Trigger 1 -- this was my original designCREATE trigger1 on TableFOR UPDATEASif update(field1)begininsert into AuditSELECT What I needENDif update(field2)begininsert into AuditSELECT What I needEND...... Repeated about 65 more timesif update(field67)insert into AuditSELECT What I needEND---------------------------------------------------------------------------Trigger 2 -- this is what i tried but did not improve performanceCREATE trigger2 on TableFOR UPDATEASDeclare @DelField1 varcharDeclare @DelField2 varchar....Declare @DelField67 varcharSelect@DelField1 = Field1,@DelField2 = Field2,....@DelField67 = Field67From DeletedDeclare @InsField1 varcharDeclare @InsField2 varchar....Declare @InsField67 varcharSelect@insField1 = Field1,@insField2 = Field2,....@InsField67 = Field67From Inserted-- I do not do if Update() but instead compare variablesif @DelField1 <> InsField1beginInsert into AUDITSELECT what I needendif @DelField2 <> InsField2beginInsert into AUDITSELECT what I needend............if @DelField67 <> InsField67beginInsert into AUDITSELECT what I needend----------------------------------------------IF you have any idea how to optimize this please let me know. Anyinput is greatly appreciated. I do not have a problem with triggersdoing what they are supposed to, they are very slow this is myconcern. The reason I gave you two examples is because i suspect ithas something to do with the enormouse amount of code inside thetrigger. both examples perform about the same whether i use the twohuge selects from the Inserted and Deleted or not.Thanks,Gent
we just moved a database from a shared SQL 2000 server to SQLExpress on a VPS server. Every thing seems to work great, really no performance loss at all, except for one stored proc that is giving us problems. It's function is to update a history table, and then update the production table. On the old server, it would take less than a second to run this, now it takes anywhere from 45 seconds to 1 minute or it times out. This database is used on a classic asp web app. ANY help at all on this would be appreciated as I am pulling my hair out trying to figure out what's wrong here. here is the proc.
------------------------------------------ code ---------------------------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON SET NOCOUNT ON go
On 1 pc i installed VS2008 besides VS2005 and SQL 2005 (before i installed VS2008 everything was fine)
On the other is a fresh installed XP machine with only VS2008 SQL2550 and Office2007 Latets service packs are installed
On both machines when i open a table in SQL 2005 with the 'open table' command the show of the table is very slow. Every row is updated file by field, the grid is not showing, the row and column headers are not 'grey' as normal.
Scrollign is impossible as the screen updates start again that slow.
Even a small table with 10 rows and 15 field behaves this slow. Its not workable.
I would like to know whether it is possible to add and updated date column in a slow changing dimension table using the slow changing dimension data flow transformation.
I would like to keep track of what record is updated in the dimension table based on the data being processed.
insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.
However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.
I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.
I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).
How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?
We have a big problem here with merge replication, specifically whenever a schema change occurs. We are replicating schema changes, and triggers/stored procedures. The example is that we changed about 150 stored procedures and about 30 triggers. This is then replicated to the subscriber database (which is also a merge publisher for further remote systems that were offline at the time) over a 10Mb link - hardly low bandwidth. However, the replication process takes about an hour and a half - considering the SQL on the primary server took less than a minute to run this is a big suprise.
We've run a trace to see if we can identitfy what is going on. There seems to be a great number of calls to sp_MSunmarkschemaobject - we are still waiting for a trace to complete to fully analyse this however it looks like it calls this repeatedly for every stored procedure in the database. We are currently re-testing to one of the remote servers with the merge agent set to the slow profile (not much hope that this will alter the poor performance).
This task looks to be excessive - and certainly does not seem to function in a sensible manner. Has anyone else had similar issues or have any suggestions. This is very infuriating as it means that the servers are effectively offline for a minimum of and hour and a half (in fact the remoter servers take over 4 hours !).
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
Hy, i have this problem in vb.net: I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code: Dim variable_name As StringDim variable As Integer Dim sqlString As String = ("UPDATE table_name SET " variable_name " = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery()
When I don't use a variable after SET statement, everything work fine. This code works fine: Dim variable As Integer Dim sqlString As String = ("UPDATE table_name SET column_name = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery()
I am having difficulties with some sql syntax with sql server 2000. I am trying to write code to update a column in which the name of it is unknown. At run time, I am able to set a variable equal to the correct column name but in doing so, treats the value as a String. Ex. Declare @varA varchar(12) select @varA = (select top 1 Value from #temp)
Update TableX set @varA = y.ColTest from TableX x, TableY y where x.Colid = y.Colid
It sets the variable = to the last value from TableX.ColTest I want the Update statement to update the value for the Variable which represents the correct column to update.
Yesterday I was working to try to resolve a issue with not Using a cursor for a unique type of update.
TG was able to resolve my issue using this query
Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY Clustered, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4)) Insert Into @Stage(StartDate ,BenefitInterestID, Amount, InterestAmount, Interest , ai ) Select convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai Union all select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0 Union all select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0 Union all select '2009-12-01 00:00:00.000',5,0.00,0.00,0.0900,0 Union all select '2010-12-01 00:00:00.000',6,0.00,0.00,0.0200,0
update s set @ai = ai = s.interest * (isNull(@aiTot,0) + @a + @ia) ,@a = isNull(@a, 0) + s.amount ,@ia = isNull(@ia, 0) + s.interestamount ,@aiTot = isNull(@aiTot, 0) + @ai from @stage s
TG pointed out that although this query should always work, there is a underlying issue in this type of update method, due to it is dependent on the order in which sql updates (Currently the clustered index is the only way I know of to secure the order is in tact).
Although it is unlikely, TG pointed out that do to this inherent flaw in the methodology, if future releases of SQL were to be modified to improve performance in updates, this method COULD possibly not work, and there is no 100% guarentee that the index would be used.
My questions are
1. Is there any way to Ensure that the style of update I am using uses the clustered index (i.e. "from @stage s WITH (INDEX = ...)") but I do not know the ID of the index since it is a TMP table?
2. Force the order of the update to be the order of the index (Just because there is a index, that doesn't ensure that a future release of sql will reference the index from top to bottom (I know this is unlikely, but I still would like to know if there was a way to ensure the update did always occur from top to bottom of the index).
I wonder why this script IS NOT UPDATING the user-def variable varFileName (package-scoped) and I do not get any errors when running the package at all.
Hello All I m trying to update a table whose col name will be read from another table. For e.g. Table1 gives the result: 'emp1', 1, 'John' 'emp2', 2, 'Mike' Now in the second table, i need to update the table with Col name = 'Emp1' and then from the second row (above), I need to update Col name= 'Emp2' I need to write one Update Statement which will handle all the cases. I tried Update Table2 set @VariableName = ....... but didnt work... How can i do that ?
