SQL Query - sql 2005/2008 - data migration

Posted on 2011-10-13
Last Modified: 2012-05-12

I have the following tables:

User   (1:M)          USerProduct    (M:1)                                   looukuptable_Product

User_ID (pk)           UserProd_Autoid (pk)                                   P_Id (PK)  identity
                                 P_Id (fk)                                                      product_ID

I also have:

Lookuptable_X      (1:M)           UserProduct
X_autoID                                   UserProd_Autoid (pk)
X_Value                                    P_Id (fk)

I need to migrate data to table USERProduct. The tables User, lookuptable_product andlookuptable_X are all already populated.

The source table is in this format:

user_ID, X_value, product_ID,.....

I need to write an insert into select * from statement to insert all values from sourcetable to userproduct. however I am not sure how to extract the X_ID from X_Value of source table 'while' I am inserting data....

Let me know if you need more clarifications.
Many thanks in advance
Question by:shmz
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    I don't know if I understood well but the solution should be something like match source columns to target columns:
    INSERT INTO UserProduct (User_ID, P_Id, [rest of columns here ....])
    SELECT user_ID, product_ID, [rest of columns here ....]
    FROM SourceTable

    Open in new window

    Good luck
    LVL 20

    Expert Comment

    by:Marten Rune
    Im not sure whats the problem either, but if it has to do with identity fields thats automatically calculated, then you start your transaction with:

    Set Indentity insert ON

    Now you can populate identity fields i e: ID Int Identity(1,1)

    Regards Marten
    LVL 37

    Accepted Solution

    Not sure, but I think what you're referring to is called a "lookup" in ETL terms.  SSIS (Integration Services, a component of SQL Server) has got a Lookup transformation which can be used for that purpose.

    From what you've described, I think you're probably not using SSIS.  But I do think that it would be interesting for you to look into that, if you've got it available on your server it's really worth using it for any data extraction process.

    You can use the Import/Export Wizard from the Management Studio to create your basic package, then open it up in the Business Intelligence Development Studio (ships with SQL Server) to look what has been created by the wizard, and to customize to your needs.

    If SSIS is totally new to you, you may find it a bit difficult at first. But if you play around with it for a while, you'll start to see how useful it can be :)

    More info:

    Author Closing Comment

    LVL 37

    Expert Comment

    Why C grade??  Please ask more questions to follow up to your original one instead of giving a C without clarification...

    See also

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now