July 14, 2005 at 7:53 pm
I am trying to implement backup and restore of an Application. I have data in abt 100 tables with varying table schemas. Say I am storing inforamtion about customers. I need to get all data for a particular customer from all the 100 tables meaning I can have 2 rows from table1, 4 rows from table2, 8 rows from table 3, and so on. Can I put all the data into One table and finally use DTS to create one file. Anybody has any Ideas? Since the table schemas are varying I am unable to put all the data into one file
July 14, 2005 at 10:54 pm
Need more info. are you attempting a database backup or exporting the data to a file such as excel or csv?
July 15, 2005 at 7:25 am
Exporting the data from amny tables to one single file
July 15, 2005 at 9:58 pm
Yes, what you are doing is rite. The easiest way is to use a sp to create a temp table(depending on the conditions and filters) and then use a DTS package to transfer it to file..Other techniques like bcp, BULK insert can also do the trick for u and wanna go more advanced then you can use sp_OACreateObject from SQL sort of COM Automation from SQL.
July 18, 2005 at 10:25 pm
Here's a good example for you to get started:
Using DTS to generate an excel them email
http://www.sqlservercentral.com/columnists/jsack/capturingtheerrordescriptioninastoredprocedure.asp
July 19, 2005 at 7:27 am
But can DTS tranform multiple tables(with varying schema) to one single file. i.e does it append the result of transformation of multiple tables to one file.
July 19, 2005 at 8:37 pm
I'm sorry If i don't understand your problem completely. I think since your need basically is to retreive data from tables with varying schemas, you can write a hand-made SQL statement which populates the data in a temp table and then use this temp table as your final source to transfer it to the destination file.
July 20, 2005 at 7:31 am
If I do as you mentioned ie create temp tables and then use DTS. but in my product we have like 185 tables. hence I will have to create 185 temp table and finally 185 files. Is there anyway I can put all the data in 1 file.
July 20, 2005 at 8:50 am
Sapna,
I'm not saying u create 185 temp tables, u make a temp table which is dynamic like e.g. u write a select which joins multiple tables and gets the final result and put the results in temp table e.g.
select * into #temp
from tableA inner join tableB
on tableA.id=tableB.id
this is just a generic example, in ur situation either you write a procedure or write SQL script that will combine all the resultsets from whatever tables you want into a single temp table and then use this temp table as your final source to export to file..
HTH
Regards,
Dilip
July 20, 2005 at 9:03 am
do u have any script where in u have implemented this?
July 20, 2005 at 9:52 am
I am unable to write one select statement to join all the tables. so what do u think?
July 20, 2005 at 7:03 pm
I can help you with the script if u put down here some 3-4 table schemas with some sample recs and the way u want the output to come
All the best
July 20, 2005 at 7:03 pm
I can help you with the script if u put down here some 3-4 table schemas with some sample recs and the way u want the output to come
All the best
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply