Do sybase(SQL Anywhere) support table valued parameters similar to SQL server.

[Former Member]

Hi ,

I Am very much new to sybase (SQL Anywhere). I have a .net application in which am supposed to read almost 30 huge text files and insert into sybase SQL Anywhere database table.There are a set of business rules which are needed to be applied on some of these text files. Right now am processing each and every row and validating against the business rules and inserting them to sybase SQL Anywhere database table for which is taking very long time and effecting the performance.

I want to know does sybase ( SQL Anywhere database) support table valued parameters similar to SQL server 2005/2008 or is there any other solution of my above problem. I am converting the text file to datatable and want to pass this whole datatable from .net as parameter to sybase SQL Anywhere database (planning to write a stored procdure) and process the data over server instead of client side.

It will be very greatful if anyone of you would suggest me a better solution/approach for improving performance.

Thanks in advance. Satish

[Former Member]

Can you give an example?

[Breck_Carter]

The LOAD TABLE statement can be used to rapidly load text files into tables.

If the data needs to be analyzed and/or processed before being stored in tables, you can LOAD TABLE into a "preliminary" table, then use SQL commands to select rows from that table and insert them into the "final" table.

[Former Member]

@Breck Carter thanks for the answer load table is not taking much time. But processing/analysing the data and updating back to table as row by row is taking more time than anticipated.

Is there any better approach for dealing this scenario in a faster way. Pls suggest.

Thanks, Satish

[VolkerBarth]

As Zote has asked: Can you give a sample of what you're trying to do?

SQL is a set-based language, therefore there are lots of ways to modify many rows in one step instead of "row by row".

But it's difficult to give better advice when the requirements are rather vague...

[Former Member]

Thanks for replying

Am following the below approach. This is an datamigration process:

1.First read the text files and load them to table.

2.Again read them back to .net application

3.datatable = (select * from DBtable)

4.Am updating table data from another table

5.So read both the tables and compare and update the actual table with reference table.

6.Process the data (foreach datarow in datatable.rows)

7.Update back to table (UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value)

Hope this inforation is fine

[VolkerBarth]

read both the tables and compare and update the actual table with reference table

I'm not really understanding the required steps (and if there's more than one "update step" for your table - or are there several ones?).

However, IMHO that all sounds like a typical task for set-based operations, cf. the SQL MERGE statement that is precisely meant to modify the contents of one table based on data from different table(s). This is particularly true if their are strict rules how to migrate data, and if no "user decision" is necessary.

So possibly you would simply need to:

• load the text file data into the desired table
• use merge to compare with the contents of the second table (or a part of it) and store the updated/added/deleted data
• forget about client-side computing:)