console application for retrieving a large amount of data

i need to retrieve a large amount of data from the sql server database and make changes to one field and put the data back using a console application. how do i do it?Well, there are lots of ways, probably the easiest being to retrievethe data as a dataset, make the changes, and update the database.
But let's start with this. Why do you need to retrieve a large amountof data to only change one field? Do you mean one field in all or mostof the records, or one field in one record?
If you're doing an update that affects many rows and don't really needto pull the data off the server, you might be able to use a SQL UPDATEstatement that updates the data on the database server, saving thenetwork traffic of pulling it down to the client.
Tell us more about what you want to do and we'll try to help.
Don
|||well, i have to retrieve 3 million records from the sql server database. i need to encrypt one field present in all records and put that encrypted field back into the db.
thanks.|||Okay. Is this a one-time thing or will you be doing it regularly? If aone-time thing then you probably don't care too much about performanceor being inefficient with regards to memory usage. So I would probablypull down the data into a dataset, probably in ranges of data usingsome field that is reasonably well-distributed, make the changes, andthen update the database.
For example, if the data had a last name field, you could do it forlast names that begin with A to E, then F to M, and so on. Or whateverranges make sense. And bring down ONLY the data you need, presumablythe one field with the data to be encrypted, and perhaps the secondfield that is the destination for the encrypted data. Or does theencrypted data go into a different table? Then you'll need to generatethe insert statements or use a second data table in the data set.
This is going to be horribly inefficient, however, so you won't want togo this route if this is anything but a one-time thing. If it'ssomething you'll need to do regularly, I'd try to find a way to do thisentierly on the server. In that case you could write a stored procedurethat uses OLE Automation (the sp_OA* system stored procedures) to dothe encryption. Since that uses COM it's going to have its ownperformance issues, but at least you're not slepping three million rowsof data to the client across the network.
Depending on exactly how you need to do this, there are plenty of other ways to get it done.
Don

Consecutive numbering WITHOUT Identity

Hi,

I'mtrying to insert records from one table into another table. Thedestination table has a ROWID field which cannot be an identity key,but needs to 'act like' an identity key and have its value populatedwith (Max(ROWID) + 1) for each row added to the table.

To mythinking, simply using (Max(ROWID) + 1) in my SELECT statement will notwork as it will only be evaluated once so if I am adding 1000 recordsand Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of1235.

Is there a way to accomplish this?

Thanks

Hi,

you can increment the row id locally in your program after doing an update in a loop:
rowID = <select max rowid blablabla>
while <there are still rows to add>
Command.executenonquery() - using rowID
rowID +=1
Loop

you can also use a trigger in the database to update the rowid column after insert

|||

Yes.

You can readMax(ROWID) from database intoApplication("id") at the start of web application,e.g. 1234 to avoid frequent access to database.

After insert just add id by 1.

Hope it helps.