What size of a table and bcp for specific date of data

[babeo]

Hello,
- Is there any command to check how big a table is? or to check how big for a range of records? I don't want to know how many records, I am only interested in how big in memory of them.

- I want to bcp out data of 1 month, is there a way or how do I indicate in bcp out command for the specific month, notes that the table has a Date_Time field, I don't want to bcp out the whole table, only data of 1 month for that table.

- Actually I have another related question: If there is not any option to bcp out specific date of data, then should I "select into.. " with specific date of data into a temp table and then bcp out that temp table?

Thanks

 

[JMCraig]

- Is there any command to check how big a table is?

sp_spaceused will report the size of a table and you can calculate how big a given range of rows will be by just finding out what percentage of the total size that range comprises and applying that to the sizes reported by sp_spaceused. Note that if you're interested in estimating the BCP output size, you should only use the data size value (since indexes and unused portions aren't applicable to BCP out'd files).

- I want to bcp out data of 1 month...

What's not commonly known, but is an extremely useful fact in this case, is that you can use BCP against a SQL view. What I'd do is create a SQL view with the appropriate WHERE clause and use BCP OUT to get its data.

The only other option will only work if your data's pretty static at the point you're BCP'ing it out AND you have it clustered by date order. If so, you can find out where the data starts (in terms of row number--which is the only kind of control you have in BCP for selection of particular ranges of data--the -F and -L options [I think those are the right switches]).

So, if your data's clustered in such a way that the range you want is contiguous (and the number of rows above the end of the range won't change in number between the queries and the BCP operation), you could do something like this:

select count(*) from my_table
where the_date_column < '<your starting date here>'

Use the count value + 1 as the -F parameter's value. To calculate the -L value, do this:

select count(*) from my_table
where the_date_column <= '<your ending date here>'

Good luck!

John

J M Craig
Alpha-G Consulting, LLC

http://www.alphagconsulting.com

nsjmcraig@netscape.net

 

[babeo]

Hi John,
For the discussion of the 2nd question:
- I found from our previous DBA creating views for each month, and so I think I can bcp out from these views, but unfortunately they're empty tables !, so I don't want to touch these tables but instead I do "select into" other new tables first before doing the bcp.
- I am not sure how to find out what kind of data is clustered by date order, exact, I don't know how to do according to your instructions. Could you give me more specific command of bcp related to those you given instead?
Thanks