Compare two columns before insert

Question

Compare two columns before insert

komal145

SSCrazy Eights

Points: 9915
More actions
October 24, 2016 at 2:14 pm

#331283

hi,
I have a source column value "abcd" , i will be updating the destination column with the source column value only if there is a change.
Example: destination is also "abcd" no insert if there is cahnge in source column " efgh" then insert.
How can i compare columns before insert?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Please post DDL, sample data and expected results. If you don't know how to do it, read the links in my signature.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246965 More actions · Answer 2

komal145 (10/24/2016)
hi,
I have a source column value "abcd" , i will be updating the destination column with the source column value only if there is a change.
Example: destination is also "abcd" no insert if there is cahnge in source column " efgh" then insert.
How can i compare columns before insert?

You have mixed INSERT and UPDATE. Which is it? Are you adding rows, or updating them?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pietlinden SSC Guru Points: 63503 More actions · Answer 3

If you want to add the non-matching records and update the existing ones, read this thread... Paul White knows his stuff.

komal145 SSCrazy Eights Points: 9915 More actions · Answer 4

TABLE ATABLEB

STATUS STATUS

ABCD ABCD If the value are same No insert

BCDG ABCD No equals then Insert new record

I am caomapring values , if changed only then insert.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

komal145 (10/24/2016)
TABLE ATABLEB
STATUS STATUS
ABCD ABCD If the value are same No insert
BCDG ABCD No equals then Insert new record
I am caomapring values , if changed only then insert.

So, read the link provided in the post just above the one you just posted. The answers are there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63503 More actions · Answer 6

So it's simple.

do an outer join to get the non-matching records, then wrap that in an insert statement.

INSERT INTO DestinationTable(col1, col2, col3)

SELECT colA, colB, colC

FROM SourceTableA a

LEFT JOIN SourceTableB b

ON a.KeyField = b.KeyField

WHERE b.KeyField IS NULL

ng_vg Valued Member Points: 69 More actions · Answer 7

you can user MERGE statement.

compare the column in both the tables , if doesnt exist then insert else update

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

ng_vg (10/26/2016)
you can user MERGE statement.
compare the column in both the tables , if doesnt exist then insert else update

MERGE has had some huge problems in the past. I don't know if it's any better in 2012 but that's mostly because I didn't use it in the past (because of the problems it had) and haven't taken the time to make it a habit.

Doing a separate INSERT/UPDATE/DELETE really doesn't take any longer to write or perform.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe Celko SSCertifiable Points: 5749 More actions · Answer 9

I have a source column value "abcd" , i will be updating the destination column with the source column value only if there is a change.

I think you are missing the point about how RDBMS works. SQL is a set oriented language, so when I do an update. I do it in an entire subset of rows (not columns! The unit of work is the row). The official model in ANSI/ISO standard SQL is that the subset of old rows is deleted as a unit of work, and the new set of rows is inserted as a single unit of work. The fact that many of the columns in the new rows might have the old values makes no difference at all.

You seem to still think that a row is scanned left to right, just like a record was with punch cards and magnetic tape files. This would be a procedural approach to programming, not a set oriented approach. In fact, since SQL is a declarative language unless you use cursors and other row by agonizing row coding, you get to see how the optimizer decides to do the job!

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL