Transact SQL :: Removing Cursor And Updating Flag For Each Row?
Jul 15, 2015
After parsing unformatted XML file, we are loading XML in formatted for into a SQL table rows, so that SSIS can read it and load it to DW tables.
We have a flag column in the above table, which gets updated after each row is extracted successfully by the Procedure(cursor inside Proc) used in SSIS, but cursor inside Procedure is taking 16 hours to load 100k xml source files, if we remove cursor and use bulk load then it takes only 1.5 Hrs. but with bulk load we cannot update the flags.
Right now I have a cursor that makes me want to puke. This is the last cursor in my current project and I want to replace it with a much faster set based operation.
Here is the problem. I have a table with say 1-3 million records. There are fields that get loaded in with date information. These fields are varchar because the date information could very well be mangled data that needs to be reviewed by a user. What I need is to go through these varchar fields and flag the values that cannot convert to smalldatetime.
I have another table that houses the primary key and the field of the record that cannot convert.
Essentially, I have a series of filters that run and flag using set based stored procedures. If there is a record that gets through that contains a value that cannot be converted, I have a cursor that steps through the data and attempts to convert the value. If it is able to be converted, then it continues on until it finds the value that is holding up the conversion.
I guess if I can run a query that will return all records that can convert for the field (or can't convert) I'd be all set. Any help here is appreciated.
-Whenever terminatedate is not null -Also with in the ID group if one record terminatedate is null then set flag=yes
Rule-2: Set flag=No - with in the ID group Whenever terminatedate is null
My output should look like this..
ID WorkId HireDate TerminateDate Flag 1 123 2006-01-05 2013-01-13 yes ( Rule-1: set flag = yes within the group for ID =1 because all the 3 records terminatedate is not null) 1 456 2006-02-23 2013-04-13 yes 1 789 2005-12-12 2013-06-13 yes 2 913 2004-12-15 2012-01-13 yes (Rule-1: set flag = yes within the group for ID =2 because all the 2 records terminatedate is not null)
I was using Type 2 for one of our Fact table.... and need to put a flag to know which one is the Current record... I couldn't able to figure how to implement logic in the merge statement... This is an example Query ....I was using like this for my fact table...
Basically I need to track CustomerName and City... So I need a Currentflag (Y) for latest record....
MERGE INTO [dbo].[TargetCustomer] AS TRG USING [dbo].[MyCustomers] AS SRC ON TRG.[CustomerID] = SRC.[CustomerID] AND TRG.[CustomerName]=SRC.[CustomerName] AND TRG.[City]=SRC.[City]
STATIC Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor declare ll cursor global static for select name, salary from ag open ll fetch from ll
while @@FETCH_STATUS=0 fetch from ll update ag set salary=200 where 1=1
close ll deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
i have a time series table which generates a flag for every 10 mins. the table looks as below. I am trying to write a sql query which gives me the start date and end date of a particular flag, each time the flag starts. i tried to do it using cursors and actually joining the table to itself based on a 10 min offset but couldn't get the required results.
I have to write a query to update the table to remove dots .... from the string value. so i could write.
UPDATE tbaddress SET Title='New address' WHERE Title='New address....'
but, there could be more than this specific record in the table, so i have to find each single record to do this. Is there a way to update the table so I can just remove the .... but rest of the text remain.
Here's my predicament. I changed the filter on an article within a subscription, but not in a meaningful way. I just added "database.dbo." before a column name, but now my subscription is flagged for reinitialization, and won't replicate any transactions unless I start over with a new snapshot. I don't want that to happen. How can I reset the article properties somewhere to get it back the way it was and continue replicating transactions.
(I needed to add "database.dbo" so that another job that looks at the filter info would point to the correct database. Next time maybe I'll just modify the table that stores the filter info)
The reason I don't want to snapshot from the beginning is that I have procedures on the subscribing server that fire triggers from the transactions, so all the jobs "downstream" would get all out of sync.
I have a table with 5 columns (col1, col2, col3, col4). I want to do is: 1) To check if any two records are duplicates (if the the values in col1 of record A are identical to Record B, two records are considered as duplicates);
2) if two records are duplicate, I want to mark Record B as "Dup" in col4 ;
3) move the data of Col2 of Record B to col3 of Record A.
I have tried to use CURSOR for the job. I would appreciate if anyone can give me some hints for updating records using Cursor.
My script looks like this: CREATE PROC Mark_duplicate AS DECLARE @var1_a, /* To hold the data from Record A */ @var2_a, @var3_a, @var4_a, @var5_a,
@var1_b, /* To hold the data from Record B */ @var2_b, @var3_b, @var4_b, @var4_b,
/*** Create a CURSOR ***/ DECLARE Dup CURSOR FOR SELECT col1, col2, col3, col4 FROM TableA ORDER BY col1, col2 FOR UPDATE OF col1, col2, col3, col4
/**** OPEN the CURSOR ****/ OPEN Dup FETCH NEXT FROM Dup into @var1_a, @var2_a, @var3_a, @var4_a WHILE ( @@FETCH_STATUS =0 ) BEGIN FETCH NEXT FROM Dup into @var1_b, @var2_b, @var3_b, @var4_b WHILE ( @@FETCH_STATUS =0 ) BEGIN If ( @var1_a = var1_b ) THEN . .Updating statements . . ELSE SET @VAR1_a = @var1_b, @VAR2_a = @var2_b, @VAR3_a = @var3_b, @VAR4_a = @var4_b FETCH NEXT FROM Dup into @var1_b, @var2_b, @var3_b, var4_b END END CLOSE DUP . . .
I have a partitioned view defined by a UNTION ALL of member tables. I can update the member tables through the view without any problem. However, when I declare a cursor on this partitioned view and try to update the view using WHERE CURRENT OF, I get an error saying 'The target object type is not updatable through a cursor'. Does anyone know if it's the case that updating a partitioned view through cursor is not supported in SQL Server 2000?
I got a problem. I am working on DTS package. The last step is updating a table field. I wrote a stored procedure as below:
CREATE PROCEDURE [Update_product_manufacturer] AS
Declare @product_id int Declare @supplier_name VarChar (255)
Declare ValueCursor Cursor For
select product.product_id, [P21_SUPPLIER_id_name_ke].[supplier_name] from [VARIANT],[P21_INV_MAST_uid_itenID_weight_ke],[product], [P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e],[P21_SUPPLIER_id_name_ke] where [product].product_id = [VARIANT].[product_id] and [P21_INV_MAST_uid_itenID_weight_ke].[item_id]=[VARIANT].[SKU] AND [P21_INV_MAST_uid_itenID_weight_ke].[inv_mast_uid]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[inv_mast_uid] AND [P21_SUPPLIER_id_name_ke].[supplier_id]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[supplier_id] order by [product].[product_id] for read only
Open ValueCursor while (0 = 0) begin fetch next from ValueCursor Into @product_id, @supplier_name
update product set manufacturer = @supplier_name where product_id = @product_id
end
close ValueCursor Deallocate ValueCursor
Notes: Table: Product has 28,000 rows, other tables with 28,000 - 56,000 rows
it's been 2 hours, the job is still working.
Who has this kind of experience? How can I make updating quickly?
I'm trying to pull records from a source/staging table where there is a duplicate row in it.I don't need that as the requirement is to garbage in /garbage out.when I do that from mart and use joins btw fact and dimensions, Im not getting this duplicate record as Im using distinct/group by. If I removed it, then it returns more than 3000 rows which is not correct. Is there a way I can keep these duplicates without removing group by...Im using correct joins and filters.
I have an existing stored table with duplicate rows that I want to delete.Using a cte gives me
WITH CTE AS ( SELECT rn = ROW_NUMBER() OVER( PARTITION BY employeeid, dateofincident, typeid, description ORDER BY Id ASC), * FROM dbo.TableName ) DELETE FROM cte WHERE rn > 1
This is what I want to do basically. But this is only deleting in my CTE, is there anyway I can update my existing table "TableName" with this, without using temp tables?
I need write a query for removing duplicates, for Example in my table I have columns
A_ID name id 1 sam 10 2 sam 10 3 sam 10 4 sam 10 5 ccc 15 6 ccc 15 7 ccc 15 8 fff 20 9 fff 20 10 fff 20
So now I have duplicates values in id column so now I need to take only one value of each and delete the remaining. I need to take first id value 10,15,20 so only 3 rows should be there in my table.
declare @error int, @rowcount int select @rowcount = COUNT(1) FROM STG_BCDR; while @rowcount > 0 begin BEGIN TRAN Deletion
[code]....
Above code i try to delete records batch by batch to avoid table locking at BCDR table.total records in this BCDR table is 40,000 records. However I run the code at execution plan, the BCDR table still clustered index scan which means that the locking still happend.
If i change the delete top (5000)...... to delete top (5).... then thre is clustered index seek, which is good..The problem here is each time only delete top 5 records which is means it will realy take very long time to remove those data.
how to cater the situation inorder for me to delete those huge data without table locking happend. If table locking happend , then other user will not be able to access this table at the same time.
create table #datatable (id int, name varchar(100), email varchar(10), phone varchar(10), cellphone varchar(10), none varchar(10) ); insert into #datatable exec ('select * from datatable where id = 1') select * from #datatable
I still want to retrieve the data and present it in the software's presentation layer but I still want to remove the temp table in order to reduce performance issue etc.
If I paste the code DROP
TABLE #datatable after insert into #datatable exec ('select * from datatable where id = 1')
Does it gonna work to present the data in the presentation layer after removing the temp table?
The goal is to retrieve the data from a stored procedure and present it in the software's presentation layer and I still need to remove the temptable after using it because I will use the SP many time in production phase.
I have a Stored Procedure. In that SP, I am calling 10 table-valued user defined function to calculate different pricing charges (lets say delivery charge, fuel surcharge, etc). In that SP, there is one Temporary table to store the data required for calculating charges and some columns will hold the calculated data in the same temp table.
1. First step is loading the columns in Temporary table in the SP which are required for calculating charges (will be having 1000 records for example) 2. Second step is to calculate charges ( delivery charge, fuel surcharge, etc). for each rows(1000 rows) from the temporary table using table-valued user defined functions one by one*Call function to calculate DeliveryCharge in the SP and calculate DeliveryCharge for all 1000rows. For this step i am using cursor to loop through 1000records and find DeliveryCharge for each row and update it in the DeliveryCharge column in the same temporary table. I am using 10 cursors for 10 different price calculations in the SP 3. Finally, the SP will return that 1000 records with calculated prices. The question is how to avoid Cursor for these operations. How to pass all 1000 records to a function and get table valued results from that function and update those results in the Temporary table without using cursors?
Am I using the cursor feature properly here? -- of course there would be actual processing (replacing a while loop) going on and not simple print statements.
Declare @BadgeNumber varchar(20), @name varchar(100), @phone int, @status varchar(25) Declare cursor Cursor For Select jt.BadgeNumber, tj.Name, jt.Phone, zt.status From employees jt join employeestatus zt On jt.id = zt.id
When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
I write few lines to do a bottom-up calculation, with 'fetch last' and 'fetch prior'.
It seems that the condition 'WHILE @@FETCH_STATUS = 0' does not work when cursor arrives at the first line, as there is an error message:
'(1 row(s) affected) 6255.84 (1 row(s) affected) Msg 16931, Level 16, State 1, Line 18
There are no rows in the current fetch buffer.
The statement has been terminated.'
how to fix the error?
Here is my code:
DECLARE @lastprice real DECLARE @updatedprice real DECLARE @updatedRe real DECLARE @updatedAUX real SET @lastprice = ( SELECT Close_P from #ClosePrice where #ClosePrice.DateTD = (SELECT MAX(#ClosePrice.DateTD) FROM #ClosePrice) )
I have a table which table has :Identity Column (Identity), Schema name and Table_Name.
So I would like to write a cursor For each Table Count(*) from Table--@Cnt Int , Schemaname and Tablename need to store another table.
Implement a USP, using a cursor that scan the table, generate select count statement from configuration table and fire the select count statement and record the result of the query in the log table :
how can I write a cursor and Import Those results into to Another table.
I have a table which is a configuration table, I have declared cursors whereby the cursor doesn't get to all the rows in the configuration table even though there is no where clause in the select statement and the cursor ought to loop/go through every single row. Changed the cursor to the below and it started to go through all rows.
DECLARE XXX CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY TYPE_WARNING FOR
Now with the definition above, I am now having a situation whereby a column in row 1 which is a bit data type and has a value of 0, then the cursor gets to row 2 the same column but with a value of 1 and an if statement in the cursor saying
IF @row2columnX = 0 set @myval = 99
For some reason within the cursor the IF statement is being implemented even though the value in row2 columnX is = 1
I find it so weird. Is there a database property that affects the way cursors react or is there something that I am doing incorrectly ? Lastly, I would like to have a simple cursor template which simply goes through a configuration table or any table.
i read in the SET ROWCOUNT documentation URL.... that 'The ROWCOUNT option does not affect dynamic cursors', it does affect my dynamic cursor created in a table function which looks like this :
CREATE FUNCTION MyTableFunction() RETURNS @MyTable TABLE (MYFIELD INTEGER) AS BEGIN DECLARE @xxx INTEGER DECLARE My_Cursor CURSOR DYNAMIC FOR
[code]...
I would like the number of rows retruned by MyTableFunction limited to 2, but NOT the inside proc cursor's select !Set Rowcount 0 is forbidden in table function. I cannot use TOP in select * from MyTableFunction instead of setting ROWCOUNT to 2. I'm using SQL Server 2008 or 2012.
I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten. Below is the stored procedure I've created:
ALTER PROCEDURE [dbo].[ap_CalcGrade] -- Add the parameters for the stored procedure here @studId int, @secId int, @grdTyCd char(2), @grdCdOcc int, @Numeric int output,
[Code] ....
And below is the "test query" I'm using:
-- *** Test Program *** Declare @LetterVal varchar(2), -- Letter Grade @Numeric int, -- Numeric Grade @Result int -- Procedure Status (0 = OK) Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3,
[Code] ....
This is resulting in an output of:
A+ 97 A+ 97 C- 72
but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
A+ 97 No Find C- 72
I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it. Below is the assignment requirements:
Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:
1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE 2. Outputs the numeric grade and the letter grade back to the user 3. If the numeric grade is found, return 0, otherwise return 1 4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
I have a table like above where cid is unique but status is not for all the status id i need put 1 as they sent out .but till now i used max listindex because they used to send files sequentially but now list index is random so how to update sent to 1 for all the status ids.
No error is thrown, but the update is not made? Possibly due to the dreaded inline sql being used? The data structure for these 2 servers is horrific (but that is a story in itself). The current structure (which needs immediate change) is each employee all 10 of them, have their own database. If the userid is 6 or higher they are on server2 if the userid is 5 or below they are on server1. Then they each have their own table that stores the data. (A story for another day, but def needs overhaul). However, here is the sql -- what needs to be altered to work with the current data structure and still get the updates needed? Or we can scratch the update statements entirely if we can get the correct counts needed.
I would like to use the following code for querying summary records with paging.
DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 1 SET @RowspPage = 10 SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY create_date) AS NUMBER, * FROM summary ) AS TBL WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage) ORDER BY create_date
Paging is implemented for fast response since the data pool is very large up to 10000000.
The above query works fine in testing. However, in reality, since new records are keep inserting to the tables, I have concern about the accuracy of viewing another page of result.
E.g. At 12:00pm, the result of page 1 (5 per page) is R20, R19, R18, R17, R16
After 2 mins, 12:02pm, the user press next page button Since records R21, R22, R23, R24, R25, R26 are inserted page 2 result would be R21, R20, R19, R18, R17
So the result is showing many records same as page 1 which has already been seen. Could this situation be improved?
I am trying to update a large table which consists of 45 million records , it is taking more than 2 days to the update , below is my approach
1. The table has only one clustered index and no other indexes on the table. 2. I am updating in batches say 20000 record-wise. 3. Changed the recovery mode to bulk logged and auto-growth size is set to 300MB and there is enough space in my disk for transaction log .
