extract records from SQL database

[jayjay66]

Hi All,

I would need some help in extracting records from an SQL database. I'm running win2003server with SQL v8.0. I guess the "BCP" utility would be the best bet or does anybody suggestion anything else?

What I need to do is take all records in my SQL database called "Accounting" with the field "LogDate" and extract them all "for LogDate<Jan-01-2006" to a text file. Can anybody help on what the syntax would be ? Also, would that make my database smaller by extracting the records?

Much appreciated. Many thanks.

JJ

 

[AlexCuse]

I would look into DTS, but BCP should work also.

The only way to make your db smaller would be to delete the records after the transfer, although I am not sure why you want to do this. Is it really that large of a table?

HTH,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[jayjay66]

OK, that sounds like a good plan. I'll delete the records after.

What would be the BCP command to use ?

Please help.

Thanks,
JJ

 

[AlexCuse]

I don't know the syntax for BCP off the top of my head. If you open the folder 'Data Transformation Services' in enterprise manager and go to Tools --> Data Transformation Services --> Export Data in the top menu you will bring up a wizard that should help you through the export.

If you want to use BCP, look in BOL (Books On Line) they provide a lot of information on it.

Hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[sqlcasey]

why not make life really simple - just run a regular query to extract the dataset you need, then just "save results as" whatever type of file you need?

you can do this in 2005 management studio, i am assuming there is a similar way to do this in EM in earlier versions? at least i would hope so... but i don't remember now

hth