Save and re-load contents of a table as a file?

[Amesville]

Hi folks, still very new to this SQL Server stuff (my background is Oracle) but I'm developing an application using SQL Server express 2008 and VB.NET 2008 to learn more about it.

My application builds a list of records in a table which are used to create a set of reports. After printing I would like to be able to save the table contents as a file so it can be called up later and re-loaded if needed. I am wondering if there is a recommended way to do something like this?

How should I approach this problem? I am sure I could build a routine to read out the values and store them as a flat CSV file of the like, but I would think there must be a better way to do this?

Any advice would be very appreciated. Thank you.

Craig

 

[djj55]

Could you save the data to an archive table?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Amesville]

Djj,

First I've ever heard of it, how would that work?

Craig

 

[Amesville]

Hmm, OK - so I suppose I could create the archive table fairly easily by using a "select * into <destination table> from <source table> " statement, substituting the archive table name for the <destination table> name.

But I have seen recently it is not so easy to copy the table contents of one table into another empty table. How would you suggest I do that?

And then there is the need to be able to present a user with a list of the archived tables so they could select one and have it re-loaded. How would I get the list of archived table names to present as a list of "files" to them?

Thanks

Craig

 

[djj55]

I was hoping my brief post would get others involved.

The way I use archive tables is ALL data eventually goes into them using the INSERT INTO function. You would then have a column that is a date entered or batch number or something that groups the data together. Thus when you wanted to get the data for 2/1/2012 you use the group information in the criteria.

For the archive table I would suggest creating it with the same schema as the table used in the report PLUS the group information column.

So if your report table has columns A, B, and C then the archive table would have columns A, B, C and GroupNum. And the insert would be something like:

INSERT INTO archtable (A, B, C, GroupNum)
SELECT A, B, C, CAST(GETDATE() AS DATE) 
FROM reporttable

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!