Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ravensensei
Starting Member
13 Posts |
 Posted - 2006-06-07 : 13:30:39
|
| I'm pulling data from about 30 different Progress db's and I need to dump the data into one table so I can run SSRS(Sql Server Reporting Services) reports on the combined data.I'd like to insert the name of the facility that I am receiving the data from and set it up so that it runs either nightly or can be kicked off by a manual process.I have no problem running the DTS to bring over specific columns, but I'd like to be able to see who is sending me what info.Thanks!M@ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-07 : 13:55:24
|
| Import the data into a staging table. Move the data from this staging table into your actual table like this:INSERT INTO ActualTable (ConstantValueColumn, Column1, Column2, ...)SELECT <ConstantValue>, Column1, Column2, ...FROM StagingTableTara Kizeraka tduggan |
 |
|
|
ravensensei
Starting Member
13 Posts |
Posted - 2006-06-07 : 16:21:19
|
| I'd have to have 39 staging tables, not sure I really want to do that, but I'm starting to give up hope.I had also thought that I might bring them all together in a view and run the report off the view.Hmmm |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-07 : 16:26:08
|
| But in your first post you said that you want to dump the data into one table, so I don't understand why you would need 39 staging tables.Import data into staging table from DB1Move data into actual table Delete data from staging tableImport data into staging table from DB2Move data into actual table (different constant value used)Delete data from staging tableImport data into staging table from DB3Move data into actual table (different constant value used)Delete data from staging table...Tara Kizeraka tduggan |
|
|
|
ravensensei
Starting Member
13 Posts |
Posted - 2006-06-07 : 16:43:07
|
| Ah, I see. You delete it from the staging table before you bring in the next batch.The problem with that is automating it, right? I want to be able to set up things to run at midnight on the 15th of the month to dump into the table and allow the users to look at the report with the new data the next day.When you say Move data into actual table, are you talking about manually running the insert with the constant tacked on? I'd like to try to set this up and ultimately forget it. I'm an IT dept of one:)M@ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-07 : 16:46:45
|
| Well you would have lots of steps in your DTS package. You would hard code the various constants in each of the Execute SQL Tasks. These two can be combined into one step: Move data into actual table Delete data from staging tableFor each of the 39 source databases, you would have one import task to the staging table and one Execute SQL Task (each with a different constant). So there is quite a bit to setup in the DTS package, but once setup, it can be scheduled to run nightly.Tara Kizeraka tduggan |
|
|
|
|
|
|
|
|
Adding constants during export