April 27, 2006 at 8:56 pm
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?
April 28, 2006 at 6:30 am
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.
April 28, 2006 at 11:20 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy