I have a database server with two databases : a live database and a test database. The test database is essentially an old version of the live database.
The live database has been updated with some live data on some records. Only one column has been updated.
I want to copy the new data into the test database, only updating the single updated field in the relevant rows.
I can't remember the syntax for this, nor can I find an answer on Google (I must be using the wrong term for this or something).
Any help?
Herbie
EDIT: I'm hoping for something like:
UPDATE [Test].Table set field = (select field from [Live].Table where [Live].Table.Field = [Test].Table.Field)
-
Follow
Oops, something didn't work.
Sign In to be begin to follow this thread
What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in. You need to be signed in to Channel 9 to use this feature.Getting "follow" information
Start following this thread
What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in and view them all on your notifications page.Removing your "follow"
Setting up your "follow"
Did you know you can
sign up for email notifications?
-
Dr Herbie
Horses for courses
-
NuTcAsE
नमस्तॆ
Dr Herbie wrote:I have a database server with two databases : a live database and a test database. The test database is essentially an old version of the live database.
The live database has been updated with some live data on some records. Only one column has been updated.
I want to copy the new data into the test database, only updating the single updated field in the relevant rows.
I can't remember the syntax for this, nor can I find an answer on Google (I must be using the wrong term for this or something).
Any help?
Herbie
EDIT: I'm hoping for something like:
UPDATE [Test].Table set field = (select field from [Live].Table where [Live].Table.Field = [Test].Table.Field)
UPDATE
[Test].dbo.[Table1]
SET
[Test].dbo.[Table1].field = P.field
FROM
[Production.dbo.[Table1] P
WHERE
[Test].dbo.[Table1].IDField = P.IDField AND
[Test].dbo.[Table1].field <> P.field
-
PerfectPhase
"This is not war, this is pest control!" - Dalek to Cyberman
As I'm a lazy sod at times* I just use http://www.sqldelta.com/ has a good data compare and update tool.
* Why write SQL when a tool will do it for you... -
Dr Herbie
Horses for courses
NuTcAsE wrote:
Dr Herbie wrote:
I have a database server with two databases : a live database and a test database. The test database is essentially an old version of the live database.
The live database has been updated with some live data on some records. Only one column has been updated.
I want to copy the new data into the test database, only updating the single updated field in the relevant rows.
I can't remember the syntax for this, nor can I find an answer on Google (I must be using the wrong term for this or something).
Any help?
Herbie
EDIT: I'm hoping for something like:
UPDATE [Test].Table set field = (select field from [Live].Table where [Live].Table.Field = [Test].Table.Field)
UPDATE
[Test].dbo.[Table1]
SET
[Test].dbo.[Table1].field = P.field
FROM
[Production.dbo.[Table1] P
WHERE
[Test].dbo.[Table1].IDField = P.IDField AND
[Test].dbo.[Table1].field <> P.field
Thanks! It was the dbo part of the fully qualified name I was missing!
Herbie. -
I'm interested in the thread I found regarding the following syntax adn edited almost completely, but not sure what the P means...can you advise?
The two databases are
every16_joom1 - database
jos_contest - table
id - fieldand
every16_web - datbase
news - table
article_id - field
UPDATE
[every16_joom1].dbo.[jos_contest]
SET
[every16_joom1].dbo.[jos_contest].id = P.article_id
FROM
[every16_web.dbo.[news.article_id] P
WHERE
[every16_joom1].dbo.[jos_contest].id= P.IDField AND
[every16_joom1].dbo.[jos_contest].field <> P.field -
PerfectPhase
"This is not war, this is pest control!" - Dalek to Cyberman
You can write it more verbose with an AS
FROM
[every16_web.dbo.[news.article_id] AS P
[AS] table_alias
Is an alias for table_source that can be used either for convenience or to distinguish a table or view in a self-join or subquery. An alias is frequently a shortened table name used to refer to specific columns of the tables in a join. If the same column name exists in more than one table in the join, SQL Server requires that the column name be qualified by a table name, view name, or alias. The table name cannot be used if an alias is defined. -
Matthew van Eerde
AKA Maurits
Another way to do it is to use an updateable VIEW:
CREATE VIEW
TestAndLive
AS
SELECT
L.ValueField AS "Live",
T.ValueField AS "Test",
L.KeyField
FROM
[Live]..Table AS L INNER JOIN
[Test]..Table AS T ON
L.KeyField = T.KeyField
Now you can do
UPDATE
TestAndLive
SET
Test = Live
or even
SELECT
*
FROM
TestAndLive
Thread Closed
This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.