Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

babeo

Technical User
Mar 30, 2000
398
0
0
CA
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
 
- 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
nsjmcraig@netscape.net
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top