Hi all, my requirement is to get rows (it may be 1 or 2 or 100 or 1000 or xxx) from the database, primary keys for all the rows will be available for me but mostly they will not be in serial. so my concern here is how to get the data from the database. approach 1: Get each row at a time (but in this case I have to execute select xxx times and may be every time I have to get the connection and close it) approach 2: Get all rows in one select query, but I have to append xxx or's in the where clause. Even if I do it what would be the max support of or's in the where clause and even if it supports some 5000 or 10000 or unlimited, is it a good way to do it. Is there any other good or best way to get the data for this type of situation.
hi shabbir, We are working on the concept of work object, each work object will be stored in the database as a record/row, my actually requirement is to purge (or remove) work objects from the existing database, but before removing it from the present database we want to take a backup to the secondary (user specified) database. Now, we will show a list of work objects to the user and user may select any number of work objects (generally it will be older work objects) , after the user selects x number of work object and clicks on the 'purge' button, we have to take all those selected work object's records from the present database and write it to the secondary database and delete it from the present database. So for this situation what would be a best or better solution.
For moving from the present database to the secondary database you can use triggers and so you dont need to be writing any code for that in the front end. Refer to Audit Trail in SQL Server 2000 or SQL Server 7. Also if user selects the rows for deleting you dont need the or statement but you can use the "IN" or BETWEEN to make the query simple.
I am not sure can I use Triggers or not for our project, if I can't use triggers I think I can write a procedure to copy rows from primary database to secondary database and then delete the rows from primary database. I have one more doubt, to copy rows from one table to other table using 'insert into table ....... select .. from othertable ....' can I copy rows from a table (in x1 databse on y1 system) to other table (in x2 database on y2 system)?
Yes you can copy from one database to the other on different systems provided you write the sql to mention the database name as well. Something like insert into database1.table1 ... select * from database2.table2 Its better not to be doing in the above manner but use the same database as far as user processing is concerned and then use a seperate scheduler program to remove the table from one database and copy the data into the other.
thx shabbir for the above replies One more concern. We have decided to write a procedure to copy records from present table to some xyz table (presently in the same database), and then delete the records from the present table. But to copy and delete the data, it can be on different conditions, like user can say delete 3 years old data (where condition on creation date column) or delete records created by some xyz operator (where condition on creation operator column) like this we may have 5 to 6 conditions operating on different columns in table. I think we can write 5 to 6 queries putting it in if conditions, so 5 to 6 if conditions and 5 to 6 queries, my concern is this the right way to do it or is there any better way for this type of problem?
You should write one single query based on all condition (ANDed) because there will be soon requirment that we can have date and operator as combined for deletion.
