Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Don't insert duplicates
Thu, May 6 2021 10:20 AMPermanent Link

Heiko Knuettel

Hi there,

a common scenario to me is when I need to copy data from one to another table, but not data that is already there. I usually deal with this like so:

insert into table1 (col1, col2, col3)
select col1, col2, col3 from table2
where cast(col1 as varchar(500))+'-'+cast(col2 as varchar(500))+'-'+cast(col3 as varchar(500)) not in
(select cast(col1 as varchar(500))+'-'+cast(col2 as varchar(500))+'-'+cast(col3 as varchar(500)) from table1)

Anybody can think of a more elegant solution?

Cheers,

Heiko
Fri, May 7 2021 1:19 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Heiko


Not much more elegant but what about creating computed columns in each table for cast(col1 as varchar(500))+'-'+cast(col2 as varchar(500))+'-'+cast(col3 as varchar(500)) then your code would become

insert into table1 (col1, col2, col3)
select col1, col2, col3 from table2
where computedcolumn not in
(select computedcolumn from table1)

The other solution would depend on knowing the maximum scale for each column and multiplying and adding eg

(col1 * 100000)+(col2 * 1000) + col3

again not much if any more elegant but might be faster / use less memory (without testing I'm only guessing)



Roy Lambert
Fri, May 7 2021 4:39 AMPermanent Link

Heiko Knuettel

Hi Roy, thanks for your reply.

I thought about using computed columns, maybe with some kind of hash, but in my (long time ago) experience these columns introduce trouble when used for stuff like indexing, ordering, filtering, anything other than plain viewing.

But this may have changed. I think I'll give it a try.

Heiko
Fri, May 7 2021 8:17 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Heiko


That sounds a bit like me and referential integrity Smiley

I use them in a few places and they seem fine. The other option - generated - may be of more interest but it uses physical space.

Roy Lambert
Fri, May 7 2021 9:11 AMPermanent Link

Raul

Team Elevate Team Elevate

On 5/6/2021 10:20 AM, Heiko Knuettel wrote:
> Hi there,
>
> a common scenario to me is when I need to copy data from one to another table, but not data that is already there. I usually deal with this like so:
>
> Anybody can think of a more elegant solution?
>

Depends on use case and a the cost of some complexity

- as Roy suggested having col1+col2+col3 as computed or even an actual
column in both (and auto populated)

- have a "modified" column in original table to know if it should be
copied and use trigger to update that in original and clear after copy

- or use trigger to update 2nd table right away

- or use trigger to build change log into 3rd change table you can then
apply (this is basically what edb publishing does but that works between
databases so not really usable here i think)

Raul
Fri, May 7 2021 9:50 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Raul


Triggers are a good idea, depending on why the insert is carried out and when it should be done.

I have a fair few triggers but my biggest problem with some of them is I've forgotten they're there and wonder just what the hell is going on with my database. Age increasing and memory decreasing to blame.

Roy Lambert
Image