• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

SQL Query - sql 2005/2008 - data migration


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
1 Solution
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Marten RuneCommented:
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
ValentinoVBI ConsultantCommented:
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: http://msdn.microsoft.com/en-us/library/ms141209.aspx
shmzAuthor Commented:
ValentinoVBI ConsultantCommented:
Why C grade??  Please ask more questions to follow up to your original one instead of giving a C without clarification...

See also http://www.experts-exchange.com/help.jsp#hs=29&hi=403

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now