This is a basic subsection of the schema I'm working with/concerned about. The main tables in question here are SSGinfo and ScholarshipGrant at the top of the picture. All the other tables I have populated the data from the Access database (really it is just one large table with all the data in it, entry by entry) using SSIS. ScholarshipGrant has only one type of scholarship in it right now (within SQL) and I am trying to pull the corresponding names from the Access database while pulling the one SGID in order to populate the SSGinfo table.
Yes. For the most part the Access database will also have stuff added to it because some administrators of other colleges prefer to scroll through hundreds of entries because SQL Server is too "fancy" and "complicated" lol. I truly appreciate your help Yosi .
I also agree with number 3. Is querying different from the stored procedure? I'm basically a non-SQL/programming person who has been beating my head on this side project for little over a year. I've learned a fair amount, but I know I got a ways to go. I appreciate the advice!
querying might just be a simple select SigName from AccessDbStaging, but stored procedure you can do a whole lot more things in it. more complex tsql code.
hi
Thanks a lot for this topic.
I do have a question.
How I can extract various kind of data ( with various Types ) from for example 7 ODBC sources, and then merge all these various types of data and use them in just one table as ODBC Source?
Let us not forget that there is no Key between these ODBC sources and I can not use Merge or Merge Join.
No. The main argument is that there are 7 different tables with different number & type of the columns which I want to extract approximately one or two different columns of each of them and finally merge them into just one table. Hence, it is necessary to merge different columns with different numbers and types.
Unfortunately, as there is no same key, I cannot solve the problem.
I would be appreciated if you could help me in this situation.
You really should have started a new conversation...but what you are asking to do is impossible. If you have no way to relate the data from each table - how can you put that data into a single row in the destination?
Now - if what you mean by merge is actually a union...then that is possible. For example:
Table1 has an ID and NAME column (1, 'Name')
Table2 has an MEMID and MEMNAME column ('M1', 'MemberName')
And the result you want in the destination is 2 rows:
1, 'Name'
'M1', 'MemberName'
Then the transformation you are looking for is a union and you can direct all 7 sources to the union transformation and output a single result to the destination.
Thanks a lot for your kind help dear.
I was able to find a solution, but I think it is not the best way. First of all, use the function of ROW_NUMBER() for each tables in order to make a Key relation. After that, use the Merge Join with the Key of ROW_NUMBER(). Then, merge first two tables, then merge with the third one, then merge with the forth one and so on.
But, I guess using the union could be useful and I`ll try it.
Anyway, thanks a lot again for your kind attention.
This really doesn't make sense - all you are doing is relating row 1 from a table with row 1 from another table where that row number is arbitrary from each table. This will result in a table that has unrelated values in each column...
How do you get any results from that table that make any type of sense?
The main argument is that I want to use one or two columns of each tables and insert their data into one table. It means, the DIMENSIONs are related to different tables with different columns which I want to merge them and insert their data into one table and make a FACT TABLE.
As I mentioned before, I solved the problem with the use of MERGEJOIN as a tool and ROW_NUMBER() function for making a relation between them. There is a picture of the process.
The main question is that is there any other more efficient solution?
. 
