How can I avoid using a CURSOR inside of another CURSOR

I am trying to avoid using a CURSOR inside of another CURSOR.

Here's what I have to do...

I have 3 tables:

Table1 has 500,00 rows

Table2 has 25 rows

Table3 is empty

For each row in Table1, I want to INSERT 25 rows (from Table2) into Table3.

Using a CURSOR inside of another CURSOR, my code looks like this...

Define and open CURSORA for TABLE1

FETCH the first row from TABLE1 using CURSORA

WHILE (@.@.FETCH_STATUS<>-1)

Define and open CURSORB for Table2

FETCH the first row from Table2 using CURSORB

WHILE (@.@.FETCH_STATUS<>-1)

INSERT a row into TABLE3 (using the data from Table1 and Table2)

FETCH the next row from Table2 USING CURSORB

CLOSE the CURSORB cursor.

FETCH the next row from Table1 USING CURSORA

CLOSE the CURSORA cursor.

Using this CURSOR inside of another CURSOR is taking way too long.

Is there an SQL technique that I can use to get around using multiple cursors?

TIA!!!

DLdfrd

Use a setbased operation, its hard to guess the solution if you don′t give us more information what you actually want to do.

HTH, jens Suessmeyer.

|||You will probably want to use a Cross Join query -- TableA Cross Join TableB. Make a select statement that returns the columns you desire, then turn it into an Insert/Select.|||

Hey anomulous,

Thanks for the advice...

A Cross Join works very well. The join still takes awhile but one of the tables had 1.7 million rows but the Cross Join is a real big improvement.

This week, I have learned Inner Joins and Left Outer Joins. Today, I learned about Cross Joins.

Thanks again...

How can I avoid this redundancy? [Sybase tSQL]

I'm kind of a newbie to Sybase tSQL, so I can't seem to figure this out.

Unfortunately, I have to use a bunch of nested queries to get data from a database I didn't create. My stored procedure is become rather huge because of the requirements my client is giving me which involves pulling bits of data from all sorts of random tables. Anyway...

Is there a way to simplify this?

...<snip>...
'varSomeVariable1' =
CASE WHEN CARD_FILE.company_nm = THEN
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 1)
ELSE
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 2)
END,
'varSomeVariable2' =
CASE WHEN CARD_FILE.company_nm = THEN
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 2)
ELSE
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 3)
END,
...<snip>...

Note that this is greatly simplified so as to not make it too confusing for everyone.

As you can see, the only difference between the nested SQL statements is the value of the 'borrower_no' in the WHERE clause... is there a better way to do this so I can avoid writing two complete SQL statements for each value I'm trying to get? I.e., can I have a conditional statment in a WHERE clause?See if the COALESCE keyword is in sybase and see if that will help you re-write the code. Using COALESCE along with LEFT JOINs have help me make query simpler.

Note without out the from and where clause, people can only guess on what could help. I mean that info is needed to know if the two tables are related to one another. If they are not related to one another I see no way to help you. And, I think that you are in trouble because a database poor design is harder to fix than stored proc bad design.

IN this case and most cases, knowing the Primary and Unique Keys would be great help to helping you.

Tim S|||Hi,
You can use decode in your where clause..

i.e
SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE
BORROWER.borrower_no = DECODE(varSomeVariable1,CARD_FILE.company_nm , 1,2)

Hope this helps you.|||Hi Shelva;

Isn't Decode() an Oracle function? I can't find it in the Sybase tSQL documentation :( I sure which I could use it 'cause I think that would solve my problem!|||DECODE is the same thing as a flattened-out case.

You can do the same thing with case, e.g. (I don't know if this solves the problem, but here is how to re-write the Oracle SQL):
SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE
BORROWER.borrower_no = CASE varSomeVariable1 WHEN CARD_FILE.company_nm THEN 1 ELSE 2 END|||Thanks MattR! That worked swell :cool: I didn't know you could use CASE in that way (i.e., in the WHERE clause.)sql

How can I avoid that the OLEDB Provider intercepts the error?

I have an stored procedure which is called by using ADO and OLEDB. I wish that when an error occurs (such as when a contraint is violated or trying to insert NULL in a not-null column) the error message is stored on an output parameter and returned to the client, without the error being raised by the OLEDB Provider.

How can I do that?

Thanks a lot in advance.For it is a lot of ways... true way and another ones...
Check inserted(input) data before ... insert update delete through sp... Using "INSTEAD OF
" triggers for checking yours data... client side errors handling ... ADO have got Errors collection...

http://msdn.microsoft.com/
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_errorformats_0tpv.asp

MSDN
Handling Errors in Visual C++
In COM, most operations return an HRESULT return code that indicates whether a function completed successfully. The #import directive generates wrapper code around each "raw" method or property and checks the returned HRESULT. If the HRESULT indicates failure, the wrapper code throws a COM error by calling _com_issue_errorex() with the HRESULT return code as an argument. COM error objects can be caught in a try-catch block. (For efficiency's sake, catch a reference to a _com_error object.)
Remember, these are ADO errors: they result from the ADO operation failing. Errors returned by the underlying provider appear as Error objects in the Connection object's Errors collection.
The #import directive only creates error-handling routines for methods and properties declared in the ADO .dll. However, you can take advantage of this same error-handling mechanism by writing your own error-checking macro or inline function. See the topic Visual C++ Extensions for examples.

MSDN
How Does ADO Report Errors?
ADO notifies you about errors in several ways:
ADO errors generate a run-time error. Handle an ADO error the same way you would any other run-time error, such as using an On Error statement in Visual Basic.
Your program can receive errors from OLE DB. An OLE DB error generates a run-time error as well.
If the error is specific to your data provider, one or more Error objects are placed in the Errors collection of the Connection object that was used to access the data store when the error occurred.
If the process that raised an event also produced an error, error information is placed in an Error object and passed as a parameter to the event. See Chapter 7: Handling ADO Events <pg_ado_eventhandling.htm> for more information about events.
Problems that occur when processing batch updates or other bulk operations involving a Recordset can be indicated by the Status property of the Recordset. For example, schema constraint violations or insufficient permissions can be specified by RecordStatusEnum values.
Problems that occur involving a particular Field in the current record are also indicated by the Status property of each Field in the Fields collection of the Record or Recordset. For example, updates that could not be completed or incompatible data types can be specified by FieldStatusEnum values.

How can I avoid table lock in SQL2000?

To any and all;

I have a very large table (16Mil+ records), for which I want to delete
about 8 Million records. There are a few indexes on this table.

Is there a way that I can run either a query or a series of queries
that will run against each record and delete based on criteria (date)?
If I do a single DELETE query, it will take forever, lock the table,
and my app that runs against it will stop INSERTING, which is bad.

If I do a cursor, I think it locks the table also, so that won't do,
right?

Any help would be appreciated.

Glenn Dekhayser
Contentcatcher.comOn 15 Feb 2005 13:19:01 -0800, "gdekhayser" <gdekhayser@.voyantinc.com> wrote:

>To any and all;
>I have a very large table (16Mil+ records), for which I want to delete
>about 8 Million records. There are a few indexes on this table.
>Is there a way that I can run either a query or a series of queries
>that will run against each record and delete based on criteria (date)?
> If I do a single DELETE query, it will take forever, lock the table,
>and my app that runs against it will stop INSERTING, which is bad.
>If I do a cursor, I think it locks the table also, so that won't do,
>right?
>Any help would be appreciated.
>Glenn Dekhayser
>Contentcatcher.com

With a table that size, have you thought of partitioning it by date? Then,
locks would be held only on the partition(s) that overlap the date range you
are deleting.|||Right, but I need to deal with the records in place before I can do
that...don't I? Once I get the table down in size to something
rational, then I can partition it...|||gdekhayser (gdekhayser@.voyantinc.com) writes:
> I have a very large table (16Mil+ records), for which I want to delete
> about 8 Million records. There are a few indexes on this table.
> Is there a way that I can run either a query or a series of queries
> that will run against each record and delete based on criteria (date)?
> If I do a single DELETE query, it will take forever, lock the table,
> and my app that runs against it will stop INSERTING, which is bad.
> If I do a cursor, I think it locks the table also, so that won't do,
> right?

First of all, if there is no index on this date column, you can never
avoid the table lock.

If there is a clustered index on the date column, you should be able
to say DELETE tbl "WHERE date < @.somedateinthepast" and still have
your insertes coming through. Then again, if you delete day by day,
it may still be leaner.

If there is a non-clustered index on date, it depends on how many
rows there are per date. If there are too many rows per date, a
DELETE per date could table-scan. You could force an index, but
plenty of rows would be locked, for some time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||For the future, you should consider partitioning , as deleting n
records is o(n) while dropping a table (or truncating it) is O(1).

For now , I would recommend deleting with rowcount< some number ,
probably in the low thousands, which should leave the table in a fairly
operational state while you do it.

Of course after such a massive delete, the table will be in a terribly
fragmented state internally, so the next step is to rebuild the
indexes.

another alternative is to create another table with the same schema but
without the indexes, insert into it the rows that you do want to keep
(the complement of the 'to be deleted') and then build the indexes. you
can then drop the old table and rename the new one.

hope this helps
Tzvika

gdekhayser wrote:
> To any and all;
> I have a very large table (16Mil+ records), for which I want to
delete
> about 8 Million records. There are a few indexes on this table.
> Is there a way that I can run either a query or a series of queries
> that will run against each record and delete based on criteria
(date)?
> If I do a single DELETE query, it will take forever, lock the table,
> and my app that runs against it will stop INSERTING, which is bad.
> If I do a cursor, I think it locks the table also, so that won't do,
> right?
> Any help would be appreciated.
> Glenn Dekhayser
> Contentcatcher.com|||Erland- I wish I would have read your post earlier. I would have
thought that removing the indexes would make the delete faster. I
goofed and left the 4 indexes in place, the date one wasn't clustered.
I really wouldn't have cared about the row locks if I forced an index,
as they were going to be deleted anyway and not accessed.

I ended up just performing the delete- and watching my logs grow REAL
large.

I now have 3 log files where I really only want one- is there a way to
condense back into one log file when you're split into 3?|||gdekhayser (gdekhayser@.voyantinc.com) writes:
> Erland- I wish I would have read your post earlier. I would have
> thought that removing the indexes would make the delete faster.

Yeah, removing the indexes not useful for the DELETE would have speedied
things up a little. It may not be a good idea to drop the clustered index
though.

> I ended up just performing the delete- and watching my logs grow REAL
> large.
> I now have 3 log files where I really only want one- is there a way to
> condense back into one log file when you're split into 3?

Well, you can say ALTER DATABASE REMOVE FILE, but you would have to
truncate the log first. Maybe the best is to use WITH TRUNCATE_ONLY
and the take a full backup of the database. I guess that then you
should be able to remove some of the files.

But I will have to admit that I have never had reason to drop a log
file, so I am just speculating here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How can i avoid subreports and datasource in ReportServer browser

Hi ,
How can i avoid dispalying of
"SubReports and datasource in ReportServer Browser"
if any one have answer plz let me know
ThanksOn the object properties select hide in list view.
"simmi" wrote:
> Hi ,
> How can i avoid dispalying of
> "SubReports and datasource in ReportServer Browser"
> if any one have answer plz let me know
> Thanks|||Thanks john
I think u r telling to hide it on the ReportManager browser.
But i want to hide in ReportServer Browser.
could u explain me in detail
"johnE" wrote:
> On the object properties select hide in list view.
> "simmi" wrote:
> > Hi ,
> >
> > How can i avoid dispalying of
> > "SubReports and datasource in ReportServer Browser"
> >
> > if any one have answer plz let me know
> >
> > Thanks|||and how do you unhide a report using ReportManager?
"simmi" wrote:
> Thanks john
> I think u r telling to hide it on the ReportManager browser.
> But i want to hide in ReportServer Browser.
> could u explain me in detail
> "johnE" wrote:
> > On the object properties select hide in list view.
> >
> > "simmi" wrote:
> >
> > > Hi ,
> > >
> > > How can i avoid dispalying of
> > > "SubReports and datasource in ReportServer Browser"
> > >
> > > if any one have answer plz let me know
> > >
> > > Thanks

How can I avoid lots of spaces being added to the end when saving contents of a textbox

Hi

I am using FormView, SQL 2005, VB 2005

When I save the contents of Title and Area entry fields , I have lots of spaces added to the end.

Title and Area are MultiLine Textboxes and database:varchar(100)

I thought I had solved the issue using;

TextBox Title = FormView1.FindControl("TitleTextBox")asTextBox;

TextBox Area = FormView1.FindControl("AreaTextBox")asTextBox;

TitleLenTrim = Title.Text.Trim().ToString();

if (TitleLenTrim.Length > 100)

{

TitleLenTrim = TitleLenTrim.Trim().Substring(0, 99);

}

string AreaLenTrim;

AreaLenTrim = Area.Text.Trim().ToString();

if (AreaLenTrim.Length > 100)

{

AreaLenTrim = AreaLenTrim.Trim().ToString();

}

string insertSQL;

insertSQL ="INSERT INTO Issue(";

insertSQL +="ProjectID, TypeofEntryID, PriorityID ,Title, Area)";

insertSQL +="VALUES ( '";

insertSQL += ProjectID.Text.ToString() +"', '";

insertSQL += EntryTypeID.Text.ToString() +"', '";

insertSQL += PriorityID.Text.ToString() +"', '";

insertSQL += TitleLenTrim.Trim().ToString() +"', '";

insertSQL += AreaLenTrim.Trim().ToString() +"', '";

Is there any other way I could remove spaces?

Thanks in advance.

My guess is that your columns are defined with data typechar instead ofvarchar. SQL Server will pad char columns with spaces.

Also, you should be using Parameters, not concatenating UI-supplied data directly to SQL statements. This is an insecure practice.

|||

Thankstmorton , you were right, I had declared the two columns as char.

Could you please explain more regarding using Parameters, not concatenating UI-supplied data directly to SQL statements?

Thanks in advance

|||

string insertSQL;

insertSQL ="INSERT INTO Issue(";

insertSQL +="ProjectID, TypeofEntryID, PriorityID ,Title, Area)";

insertSQL +="VALUES ( '";

insertSQL += ProjectID.Text.ToString() +"', '";

insertSQL += EntryTypeID.Text.ToString() +"', '";

insertSQL += PriorityID.Text.ToString() +"', '";

insertSQL += TitleLenTrim.Trim().ToString() +"', '";

insertSQL += AreaLenTrim.Trim().ToString() +"', '";

Becomes:

string insertSQL;

insertSQL ="INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@.ProjectID, @.TypeofEntryID, @.PriorityID ,@.Title, @.Area)";

cmdInsert SqlCommand;

cmdInsert=new SqlCommand(insertSQL,conn);

cmdInsert.Parameters.Add("@.ProjectID",SqlDbType.Varchar).Value=ProjectID.Text;

cmdInsert.Parameters.Add("@.EntryTypeID",SqlDbType.Varchar).Value=EntryTypeID.Text;

...

cmdInsert.ExecuteNonQuery;

(Sorry if my C# syntax isn't exact)

|||

Thanks Motley, It works fine until I try to insert a date.

Previous code was

insertSQL +="convert(datetime,'" +DateTime.Now.ToString("dd/MM/yy") +"',3), '";

I tried the code below but the record doesn't save?

Thanks in advance

string date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@.IDIssue, @.IDTask, @.TaskDone, convert(DateTime,@.Date,3),@.IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;