Update Query?

  • I've got a query that I run on server A.  I need to insert the data from that query into a table on server A that has fields that are not found in the query.  After the import/insert, I'll run a sql script against that table to fill in the empty fields with data from a different query.

    This will allow me to export that table into Server B so I can actually use the data.  Help?

  • Hi Bob,

    If I understand you correctly, you want to insert data from a query into a table but not populate all the fields in the table, in which case something like this would work:

    CREATE TABLE dbo.test

    (

    CustomerID int NOT NULL,

    Firstname varchar(20) NULL,

    Lastname varchar(20) NULL,

    Address1 varchar(50) NULL,

    Address2 varchar(50) NULL,

    Postcode varchar(7) NULL

    )

    INSERT INTO dbo.test(CustomerID, Firstname, lastName)

    SELECT CUSTOMER_ID, FIRST_NAME, LAST_NAME

    FROM Customer

    Assuming the table you are inserting into is called test...

    The insert statement needs to include the columns you are inserting into as part of the Insert, then these tie in with the columns in the select statement.

    Obviously if any of the columns you aren't inserting into are set to NOT NULL and don't have a default, this would fail!

    In this example your second update would pull together the address details for the customer, using the CustomerID to join them.

    UPDATE dbo.Test

    SET Address1 = A.ADDRESS_LINE_1,

    address2 = A.ADDRESS_LINE_2,

    PostCode = A.POST_CODE

    FROM Address A

    JOIN Test T

    ON A.CustomerID = T.CustomerID

     

    I hope this helps,

     

    Rodney.

  • I actually worked it out with a DTS package that would delete the data in the table then append the new results but thanks much.  I'll be going over your solution to learn more about the insert process.

Viewing 3 posts - 1 through 3 (of 3 total)

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