CODEWITHSUNDEEP
sqlsql-server
idnarbjm
idnarbjm

Reputation: 13

UPDATE INTO from an INSERT INTO

I have the query below that works great for copying data from a table on one database (origin table) into a table in another database (copy to table). Is there a way to change this to an update so as the data changes on the origin table, I can update the data on the copy to table?

INSERT INTO
    [ROCKFTINFO].[ShopDatabase].[dbo].[ref.Card]
    (
        REF.[Card_ID]
        , REF.[FirstName]
        , REF.[LastName]
    )
SELECT
    CONVERT(nvarchar, DecryptByKey(CRH.FirstName)) AS [FirstName],
    CONVERT(nvarchar, DecryptByKey(CRH.LastName)) AS [LastName],
    LTRIM(RTRIM(CRD.CardNumber)) AS [Card_ID]
FROM 
    CardHolder AS CRH INNER JOIN
    Card AS CRD ON CRH.RecordID = CRD.CardHolderID
WHERE 
    (CRD.Deleted = 0) AND (CRH.Deleted = 0) AND (CRD.CardNumber > 0)

Upvotes: 0

Views: 333

Answers (1)

Stu
Stu

Reputation: 32619

An Update would look like the following

update r set
  r.FirstName = CONVERT(nvarchar(50), DecryptByKey(CRH.FirstName)),
   r.LastName = CONVERT(nvarchar(50), DecryptByKey(CRH.LastName))
from CardHolder crh 
join Card crd on crd.CardHolderID = CRH.RecordID 
join ROCKFTIFO.ShopDatabase.dbo.[ref.Card] r on r.Card_Id = crd.Card_Id
where <condition>

Notes

  • You should always specifiy a length for (n)varchar.
  • Assumes the join criteria is using Card_Id - I have left off the trim functions here - if you require it then use it however to effectively join the data needs to match - using a function on the data makes it unsargable and will force a table/index scan, you should if necessary be sanitising the source data.
  • ref.card including a name-separator in a column name is a poor choice and confusing for anyone maintaining the system, plus forces you to always have to delimit it. What's wrong with an underscore like used for Card_Id?

Upvotes: 1

Related Questions