[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 936
  • Last Modified:

OVERWRITE RECORDS ON TABLE FROM ANOTHER TABLE

I need to replace a number of records from database1.table to database2.table , I dont want to copy them from table to table because I dont want duplicates, I want to replace records.

The ideas is restore information from a backup database to the current database, what would be the best way to write the query?
0
gabepcsolutions
Asked:
gabepcsolutions
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Something like this should be what you want.  Replacing the column and table names as appropriate.  ID was used to represent the link between the tables to establish same records.  If there are more fields involved in the link just add to the ON statement and add WHERE clause as needed.

If you only want to update from t1 if a valid value is there, you can use:

t2.colname = isnull(t1.colname, t2.colname)

This will leave value as is if column is null in table1.
UPDATE t2
SET t2.col1 = t1.col1
, t2.col2 = t1.col2
, t2.col3 = t1.col3
, t2.col4 = t1.col4
, t2.col5 = t1.col5
FROM Database2.dbo.Table t2 
INNER JOIN Database1.dbo.Table t1
ON t2.ID = t1.ID

Open in new window

0
 
gabepcsolutionsAuthor Commented:
Actually, what I need is to select all records on CLIENT LIST table that match a CODEID and replace the entire record.

For example, I did this in the past to replace the entire table.

SELECT *
INTO [PIProDev].[dbo].[CLIENT LIST]
FROM [PIPro].[dbo].[CLIENT LIST]
0
 
Kevin CrossChief Technology OfficerCommented:
You can insert NEW records like this:

INSERT INTO PIProDev.dbo.[Client List]
SELECT * FROM PIPro.dbo.[Client List]
WHERE /* some condition is met */

However, keeps sounding like to me you have an EXISTING record and want to update.  You will either have to do what I suggested and hand write out each column value to replace.  Or alternatively, you will have to use the criteria to find and delete those records from the PIProDev version of table and use INSERT INTO statement above as if these were new records.

Hope that helps and I apologize for and in advance as I am out of pocket a bit this week and so my responses may be a bit delayed.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now