| Author |
Topic |
|
benstewart84
Starting Member
6 Posts |
 Posted - 2010-02-18 : 10:47:52
|
| hi guys... I've googled this but can't find anything that helps me really.I have a large amount of tables that I want to interogate and pull information from.I will be using the same SQL code;SELECT * FROM [TABLENAME]Is there a way I can setup a loop to pull the table names from a RecordSet and pull the information into seperate tables?I also have the issue in that they will have different columns so I'm not sure if that will scupper the plans? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
benstewart84
Starting Member
6 Posts |
Posted - 2010-02-18 : 10:56:28
|
| merci sir... will give it a go |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-18 : 10:58:30
|
| good luck------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
benstewart84
Starting Member
6 Posts |
Posted - 2010-02-18 : 11:10:59
|
| Upon reading that, I'm a bit confused as to how I can make the destination of each of the loops change through each iteration?In that example it is all going into the one table? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-18 : 11:16:43
|
quote:
Originally posted by benstewart84
Upon reading that, I'm a bit confused as to how I can make the destination of each of the loops change through each iteration?In that example it is all going into the one table?
you basically form a list inside the For loop giving table names. then add a variable to pull back table names from list.Then inside loop put a sql task and in dynamic mapping tab of task use the variable and form sql like'SELECT * FROM ' + @[User::TABLENAME]@[User::TABLENAME] is the variable------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
benstewart84
Starting Member
6 Posts |
Posted - 2010-02-18 : 11:17:54
|
| Am I going to have to pre-define all the different tables? Since they will have different columns in them? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-18 : 11:32:25
|
quote:
Originally posted by benstewart84
Am I going to have to pre-define all the different tables? Since they will have different columns in them?
do you mean to say that table structures vary?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
benstewart84
Starting Member
6 Posts |
Posted - 2010-02-18 : 11:35:47
|
| yes.thats the main issue I have with trying to figure this out. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-18 : 11:37:46
|
quote:
Originally posted by benstewart84
yes.thats the main issue I have with trying to figure this out.
you cant change ssis metadata at run time. You can try with script task though------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
benstewart84
Starting Member
6 Posts |
Posted - 2010-02-18 : 11:38:33
|
| I'm just being lazy really.It's only about 15 different tables so wouldn't take that long, just for ease of use to be honest. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-18 : 11:48:46
|
quote:
Originally posted by benstewart84
I'm just being lazy really.It's only about 15 different tables so wouldn't take that long, just for ease of use to be honest.
then better to group them into ones with same structure together------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
loop through tables?