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.

View 3 Replies


ADVERTISEMENT

Save Me From A CURSOR. Flag Values That Won't Convert.

Dec 10, 2007

Hey all:

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.

--Thanks--

View 3 Replies View Related

Transact SQL :: Flag Yes Or No Within ID Group

May 20, 2015

I have a table with 4 columns,let say ID, WorkId , HireDate ,TerminateDate

My source date

ID   WorkId   HireDate    TerminateDate
1     123    2006-01-05   2013-01-13
1     456    2006-02-23   2013-04-13
1     789    2005-12-12   2013-06-13
2     913    2004-12-15   2012-01-13
2     406    2006-04-13   2014-04-13
3     113    2009-11-15   2011-01-13
3     106    2010-04-13  
4     513    2002-11-15  
4     168    2011-10-23  
5     342    2007-01-05   2009-01-13      
5     416    2008-02-23                   
5     159    2008-12-12   2010-06-13   
6     542    2005-01-03   2007-01-13      

Rule -1:  set flag = yes

-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)

[Code] .....

View 3 Replies View Related

Transact SQL :: Current Flag On Merge Statement

Nov 12, 2014

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]

[Code] .....

View 7 Replies View Related

Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor

Aug 12, 2015

In MSDN file I read about static cursor

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.

View 3 Replies View Related

Transact SQL :: Time Series Table Which Generates A Flag For Every 10 Mins

May 15, 2015

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.

startdate enddate
flag
2009-11-02 23:30 2009-11-02 23:50
-999
2009-12-08 04:50 2009-12-08 04:50 -990

View 3 Replies View Related

Updating The String Removing The Strings.

May 20, 2008

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.

thanks

View 2 Replies View Related

Un-flag Reinitialization Flag On Trans Subscription

Nov 21, 2004

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.

HELP !

View 3 Replies View Related

SQL Server 2012 :: Removing Cursor In Table Valued Function

Oct 16, 2015

I need removing cursor in my table valued function with alternate code.

ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
(
@containerSqlId SMALLINT,
@containerIncId INT
)
RETURNS @Results TABLE

[Code] ....

View 2 Replies View Related

How To Use CURSOR For Updating Records

Mar 8, 2000

Hello Everyone,

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
.
.
.

Thanks a lot. Have a good day!

Lunjun

View 2 Replies View Related

Updating A Partitioned View In A Cursor

Jan 13, 2004

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?

Thanks

View 2 Replies View Related

It Is Very Slow At Updating By Use Cursor (fetch Method)

Feb 5, 2001

Hi all,

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?

Thanks,

Kevin Zhang

View 1 Replies View Related

Transact SQL :: Way To Keep Duplicates Without Removing Group By

May 6, 2015

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.

View 5 Replies View Related

Transact SQL :: Removing Duplicates Rows With OVER Clause

Jun 2, 2015

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?

View 4 Replies View Related

Transact SQL :: Removing Duplicate Values In ID Column Of Table

Oct 14, 2015

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.

View 4 Replies View Related

Transact SQL :: Table Locking Happening When Removing Huge Data

May 14, 2015

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.

View 6 Replies View Related

Transact SQL :: Present Data In Presentation Layer After Removing Temp Table?

May 21, 2015

Inside of stored procedure you have the code

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.

[URL]

View 5 Replies View Related

Cursor / Transact-SQL Question

Jul 1, 1999

Hi all,

I need to run a long process in a cursor. I'd like to perform a special action every 10,000th row. How would you implement that using Transact-SQL?

Many thanks,

-Kevin Kline

View 1 Replies View Related

Transact SQL :: Alternative For Cursor

Oct 28, 2015

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?

View 5 Replies View Related

Transact SQL :: Proper Use Of Cursor?

Jun 16, 2015

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

[code]....

View 6 Replies View Related

Transact SQL :: Update Statement With Cursor

Jun 16, 2015

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?

Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)

[Code] ....

View 5 Replies View Related

Transact SQL :: Cursor Fetch From Bottom To Top?

Sep 14, 2015

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)
    )

[code].....

View 4 Replies View Related

Transact SQL :: How To Write A Cursor For Row Count

May 20, 2015

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.

View 3 Replies View Related

Transact SQL :: Standard And Correct Cursor Format

Aug 12, 2015

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.

View 11 Replies View Related

Transact SQL :: SET ROWCOUNT Applies To Dynamic Cursor

Jul 23, 2015

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.

View 3 Replies View Related

Transact SQL :: Cursor To Create Multiple Dynamic Procedures

Jun 29, 2015

The requirement is create a sql script(1 proc or cursor) which will create multiple procedures dynamically.

Table A
Col1 
Col2

A
Alpha

For Example: If table A has 3 rows(distinct) so 3 procedures will be created dynamically.

Result: 
1 PROC_A_ALPHA
2 PROC_B_BETA
3 PROC_C_charlie

View 6 Replies View Related

Transact SQL :: Creating Stored Procedure With Cursor Loop

Sep 18, 2015

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

View 6 Replies View Related

Transact SQL :: Updating Table Set Sent To 1 For All Status IDs

Nov 30, 2015

SID statusid listindex listsize sent
1           12     25        25       1
2           12    25        50       0
3            12   75       150       1
4            14     25     25        1

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.

View 5 Replies View Related

Transact SQL :: Updates Are Not Updating Database

Jun 12, 2015

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.

Declare
@employeeID varchar(50)
,@managerID varchar(50)
,@sql varchar(Max)
,@employee varchar(max)
,@itemsold varchar(max)

[Code] ....

View 13 Replies View Related

Transact SQL :: Paging Of Records Which Are Keep Updating

May 7, 2015

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?

View 2 Replies View Related

Transact SQL :: Updating Only One Row Based On Where Condition

Sep 16, 2008

How can I update only one row doing an update query?
 
For example:
 
update from table1
set category = 'C'
where country = 'Brazil'
 
One want to update only one row with country = 'brazil'

View 5 Replies View Related

Transact SQL :: Grouping And Updating The Table

Dec 5, 2015

I have a table like this. 

AS-IS
 
Column1 Column2  count
a b 20
a b 25
c d 12
c d 22

And I need to update the same as below.

TO-BE
 
Column1 Column2  count
a b 45
c d 34

How to do it?

View 4 Replies View Related

Transact SQL :: Updating A Table With 45 Million Records

Jul 21, 2015

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 .

But still the query is running slowly.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved