Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Don't insert duplicates |
Thu, May 6 2021 10:20 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Heiko
That sounds a bit like me and referential integrity 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 AM | Permanent Link |
Raul 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |