Curtis Long
asked on
update data from one SQL database to another
I have two identical databases. All except, of course, the name of the data base itself.
These data bases are called
Company_2011
and
Grower_2011
Each of the tables in the databases are identical.
In the company_2011 there is a table called plant_city. In this table there are several computed columns.
I need to be able to update the data from the table plant_city in company_2011 to plant_city in grower_2011.
I do this now with a query that looks like this:
BEGIN TRANSACTION
DELETE GROWER_2011.DBO.PLANT_CITY
INSERT INTO GROWER_2011.DBO.PLANT_CITY (THIS_COLUMN, THAT_COLUMN, THE_OTHER_COLUMN)
SELECT THIS_COLUMN, THAT_COLUMN, THE_OTHER_COLUMN
FROM COMPANY_2011.DBO.PLANT_CIT Y
WHERE (THAT_COLUMN = 'GROWER_NAME')
COMMIT
The reason I delete the table first is I have found if I do not then the query fails and tells me that the table already exists.
All I really need to do is update the data in this table every day and have the computed columns stay in tact.
What would be a better way to accomplish what I need to do??
Thanks!!
These data bases are called
Company_2011
and
Grower_2011
Each of the tables in the databases are identical.
In the company_2011 there is a table called plant_city. In this table there are several computed columns.
I need to be able to update the data from the table plant_city in company_2011 to plant_city in grower_2011.
I do this now with a query that looks like this:
BEGIN TRANSACTION
DELETE GROWER_2011.DBO.PLANT_CITY
INSERT INTO GROWER_2011.DBO.PLANT_CITY
SELECT THIS_COLUMN, THAT_COLUMN, THE_OTHER_COLUMN
FROM COMPANY_2011.DBO.PLANT_CIT
WHERE (THAT_COLUMN = 'GROWER_NAME')
COMMIT
The reason I delete the table first is I have found if I do not then the query fails and tells me that the table already exists.
All I really need to do is update the data in this table every day and have the computed columns stay in tact.
What would be a better way to accomplish what I need to do??
Thanks!!
Probably your error is caused by a command like:
SELECT ... INTO GROWER_2011.DBO.PLANT_CITY
FROM ...
SELECT ... INTO GROWER_2011.DBO.PLANT_CITY
FROM ...
ASKER
Thanks!!
What does the DB1 stand for?? What does the ... do?? Does it mean I need to add each of the other columns??
How does the INTER JOIN function work??
Thanks!!
What does the DB1 stand for?? What does the ... do?? Does it mean I need to add each of the other columns??
How does the INTER JOIN function work??
Thanks!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SET THIS_COLUMN = DB1.THIS_COLUMN...
FROM GROWER_2011.DBO.PLANT_CITY
INNER JOIN COMPANY_2011.DBO.PLANT_CIT
ON D1.THE_OTHER_COLUMN = D2.THE_OTHER_COLUMN