Post reply

Table load

  • March 13, 2006 at 3:40 pm

    #168626

    I need help!!! Despirately! I am going out of my mind trying to figure out how to load data from 3 tables in different databases into one database and table. There is a key in common - Site no. and when I attempt to load using a union-all transformation it will load the data from each of the tables sequentially one after the other not merged. I was looking at the merge join transformation and can't get it to work. I was wondering what I am doing wrong and what I need to do to get this done? I really appreciate your help and look forward to hearing any suggestions as I am somewhat new to SQL!! Thanks again!!!

  • March 13, 2006 at 10:04 pm

    #626415

    Have u tried Union instead of Union All

  • March 14, 2006 at 6:43 am

    #626475

    Union all should work fine. Union vs. union all is really not the answer, since if one doesn't merge, the other wouldn't merge either.

    Check out your SQL code. I would bet that the SQL syntax that you are using in your UNION ALL operation is incorrect.

    If you wish, post the query here so we could take a look.

    Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.

  • March 14, 2006 at 8:49 am

    #626555

    It sounds to me that you do not want a union?  If you "union" three tables together, you will get what you have described:  All three tables sequentially.  If that is not what you want, then explain what it is that you do want.

    Are you concerned with the order of the rows?

     

     

  • March 14, 2006 at 9:00 am

    #626561

    Thanks for your answers!!! I am using the union all transformation. No code just the SSIS tool. I want to combine selected columns from the three tables into one table. Each table has the same key, site no., and I was thinking I could draw matching columns based on this key out of each table. I hope this clarifies things. Please let me know if you need further info. Thanks again for your assistance!

  • March 14, 2006 at 9:24 am

    #626573

    Sorry, but I don't have SQL2005 available in front of me at the moment.

    So you want to get some columns from 3 different tables in the same row where the key in each of the tables matches?  That's a "join", not a "union".   I can't describe how to proceed without seeing it, but I hope that bit of nomenclature will point you in the right direction

    jg

     

  • March 14, 2006 at 9:42 am

    #626582

    jg.. Thanks for your reponse! I was wondering if I should use a merge transformation with a right or left join, but it only allows you to combine 2 tables. How do I combine 3? And what type of join? How can I use SSIS to complete this task? It's neat tool and I am just not sure how to get the best out of it. Do I have to sort the data first? I just need an example of how to do it so I can get started. I just didn't know if there was someone out there who has done this task in SSIS before. Thanks again for all your help!

  • March 14, 2006 at 9:53 am

    #626589

    I'm blind without the SQL server in front of me!  One thing you could try would be to design a view that gives you the results that you want.  Use the visual studio to design a view, then use SSIS to store the results of the view into your new table.

    The type of join you use depends on the data that you have and the results that you want. 

  • March 14, 2006 at 9:56 am

    #626593

    Great idea! I will try it!

    Thanks again!

  • March 14, 2006 at 10:38 am

    #626602

    Also when this project goes live this data will be coming from flat files. How is the best way to combine it and put it in a single table from 3 files?

  • March 14, 2006 at 12:01 pm

    #626617

    I don't really know the "best way".  There may be some cool tricks in SSIS that I haven't used.  We're not implementing SQL2005 for a few months yet, so I only get to play around at home.  (so sad....)

    In general, I would suggest solving the problem by creating a staging "environment".  This could be a separate staging database or just three tables somewhere in your production database. 

    When you need to import the set of 3 files, your package would truncate the 3 tables, load each from the flat files, and then use either a query or view as a data source for a data pump task into your live system.

    There may be other ways to do this, such as using a data provider that makes a file "look" like a table.

    I find that it is easier to diagnose problems if there is a copy of the import data sitting in a table to look at, so I prefer to load all of the data first, then push it where it needs to go.

    Again, I'm not going to tell you this is the best way for SQL2005.  It was for SQL2000, however.

    jg

     

     

  • March 14, 2006 at 12:16 pm

    #626621

    Great! Thanks for your reponse! I will look into it!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply