Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

update data from one SQL database to another

Posted on 2012-03-29
4
Medium Priority
?
261 Views
Last Modified: 2012-04-04
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_CITY
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!!
0
Comment
Question by:HDM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Anuj
ID: 37782497
UPDATE GROWER_2011.DBO.PLANT_CITY
SET THIS_COLUMN = DB1.THIS_COLUMN...
FROM GROWER_2011.DBO.PLANT_CITY D2
INNER JOIN COMPANY_2011.DBO.PLANT_CITY D1
ON D1.THE_OTHER_COLUMN = D2.THE_OTHER_COLUMN
0
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37782603
Probably your error is caused by a command like:

SELECT ... INTO GROWER_2011.DBO.PLANT_CITY
FROM ...
0
 

Author Comment

by:HDM
ID: 37782664
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!!
0
 
LVL 15

Accepted Solution

by:
Anuj earned 2000 total points
ID: 37782722
DB1 and DB2 is just an alias name for the tables, later you can refer this alias name in the query.
Add the required columns that you wish to update.
JOINS are set operation used in TSQL, to return rows from two or more tables based on matching condition in ON Clause. check here for details
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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