The Heap™ – Consultancy ©®

McNets

8:24 AM

Morning

John K. N.

8:44 AM

Morning

John K. N.

9:04 AM

The new design announcement New post summary designs on site home pages and greatest hits now; everywhere else eventually is now at -532

Vérace - get VACCINATED NOW

9:58 AM

A chairde - morning all!

Vérace - get VACCINATED NOW

10:24 AM

I looked at this question - which was, in fairness, abysmally worded initially! I've revised it considerably and come up with a couple of solutions for PostgreSQL - I'll take a stab at a SQL Server answer if it's reopened - appealing for votes! :-)

-5

Q: Select INTs from start of string separated by a character (pipe). Update other columns using these INTs

There is (other) sample data available on a fiddle here. It differs from the graphic above because some data was added to try and cover edge cases. CREATE TABLE t ( a TEXT UNIQUE NOT NULL ); Sample data: INSERT INTO t VALUES ('|3|3| some stuff here'), ('|SE + 18.5D some other stuff'), ('|5...

It has some interesting (dare I say "fun") aspects to it - many ways to f**k up! :-)

Paul White

11:13 AM

@Vérace-getVACCINATEDNOW That's a brutal edit. Are you quite certain it represents the intent of the original author? One piece of valuable information (the response to a comment question) you deleted completely. You've also decided it's not about SQL Server any more, and is about regular expressions.

You should ask and answer your own question rather than completely changing someone else's question like this. Especially on a question only two days old.

If it turns out you were correct, we can always make the original a duplicate.

ypercubeᵀᴹ

11:27 AM

yeah, why was tag v.2008 replaced with v.2008-R2? Why regex was added?

Paul White

Oh right the new set of tags do include R2. But the top-level sql-server tag was removed.

It's also not a database-design question.

All in all I'm going to roll that edit back. It goes too far.

I'm all in favour of assisting new users, especially where their English may not be fluent, but there are limits.

Vérace - get VACCINATED NOW

12:04 PM

@ypercubeᵀᴹ Rats! I thought that the latest version of whatever server was OK. I mean, PostgreSQL and PostgreSQL 11.3 - the first is redundant, no? I was going to touch on database design in my answer - how you should involve all stakeholders - data is not only about IT - it's about gathering, training... &c... And the string manipulation/string-splitting bits as tags are valid AFAICS!

i.e. urge the guy to get proper data collection procedures into place?

ypercubeᵀᴹ

you can add whatever you think is relevant in an answer. no need to edit tags.

Vérace - get VACCINATED NOW

But I can't answer unless it's reopened?

ypercubeᵀᴹ

yes

removing the ssms and adding string-manipulation tag would be ok in my opinion.

Vérace - get VACCINATED NOW

It was nothing to do with ssms!

And, IMHO, it's a tricky example of both string-manipulation and string-splitting! I thought an expansive answer - which I had planned - might have been of use! Obviously not!

ypercubeᵀᴹ

you just have to wait for it to be reopened

Vérace - get VACCINATED NOW

12:12 PM

Just for a poor cretin like me - ssms is SQL Server's version of MySQL Workbench or pgAdmin? It's just a GUI tool?

ypercubeᵀᴹ

yes, it is

SQL Server Management Studio

Vérace - get VACCINATED NOW

Or SQLDeveloper - if you're an Oracle fan!

ypercubeᵀᴹ

fun fact: cretin and crouton are the same words, if you are omitting vowels

Paul White

17

Q: Should we tag with all terms in a tree?

I've noticed a couple of questions that have all of the tags: oracle oracle-11g oracle-11g-r2 Ideally, the tagging would support hierarchies, and know that 'oracle' is a broader term of 'oracle-11g-r2', so we didn't have to put on the redundant tags, but I don't think it currently does. So, ...

discussion tagging

Vérace - get VACCINATED NOW

So are cnt and cn't!

Paul White

12:16 PM

@Vérace-getVACCINATEDNOW Like I said, ask and answer your own question

@Vérace-getVACCINATEDNOW Tags are for the topic of the question, not whatever might be in answers (with super-rare exceptions)

5

Q: Adding tags based on my answer

This question came to me when answering this question. The question asks how to handle BCP errors. My answer was to call it from Powershell and use the error handling there. The question didn't mention Powershell, so it didn't have a Powershell tag. If my answer is accepted would it make sense ...

discussion tagging edits

Vérace - get VACCINATED NOW

Take a look at (the sadly missed) Jack Douglas' answer to your point - it can help! I think a tag about strings - be it manipulation and/or -splitting is not only beneficial, but essential for this question - because that's what the question is about!!!

It has damn-all to do with smss - if I was using stone tablets to access SQL Sever, the issue would be the same - it's about SQL - command line also applies!

Paul White

I was referring to the regex tag

21 mins ago, by ypercubeᵀᴹ

removing the ssms and adding string-manipulation tag would be ok in my opinion.

Agree

Vérace - get VACCINATED NOW

Regex is the best way to solve this! Would help those looking for solutions?

Paul White

That's not what tags are for

Vérace - get VACCINATED NOW

Please, NO, in the name of all that is sacred, don't revise my edit to the title - the original title is gibberish - my edition was a contribution!

Paul White

12:30 PM

I think the answer should still be no. Google can help other find the question and answer. "Powershell" is in the text of the page (that includes question and answer). — ypercubeᵀᴹ Nov 25 '16 at 16:39

@Vérace-getVACCINATEDNOW I haven't revised anything --- I rejected your edit in its entirety --- a rollback

If you want to edit just the title and tags as discussed, you are welcome to

Vérace - get VACCINATED NOW

Have reedited title!

Paul White

idk this all seems like a lot of effort for a terrible question that is unanswerable in its current form, quite likely to remain closed, and deleted after a while

a lot less time would have been required to ask a clear version of the question and answer it yourself

but hey what would I know about optimization

Vérace - get VACCINATED NOW

But it's not unanswerable! It's clear (despite OP's poor English) - if the string starts with pipe, then INT, then pipe, then INT, then pipe, put these INTs in other fields!

I also notice, with some not inconsiderable rancour, that my suggestion in comments that the OP check out regular expressions has been deleted!

The question had already been closed by the time I wrote that (IIRC)!

Paul White

@Vérace-getVACCINATEDNOW No the question was closed half an hour later (by a binding mod vote)

Vérace - get VACCINATED NOW

OK - fine, but the suggestion was still valid! Are we not allowed hint anymore?

Anyway, I'm tired of this - I learnt a shitload about regexes answering this in PostgreSQL - and learning stuff is what I'm here for - I'll see if it's reopened on the basis of my vote to reopen and my new comment stating that AFAICS, the OP's intent (although poorly phrased initially) is clear!

Paul White

12:46 PM

@Vérace-getVACCINATEDNOW The question author has visited the page since you commented, and had an inbox notification with the full text of your message. SQL Server does not directly support regex in any version so nothing of value has been lost

@Vérace-getVACCINATEDNOW You are not the only one whose patience is running thin my friend

> You want to check out regular expressions - not sure if SQL Server 2008 supports these? –
Vérace - get VACCINATED NOW
yesterday

🤷‍♂️

It's also a bit pointless answering for a database system the question author isn't using

Hence my suggestion to ask and answer a question of your own

Specifically targeted to Postgres if you so desire

Vérace - get VACCINATED NOW

Thinking of asking a question of my own! I'll put my own PostgreSQL answer up there - I'd be fairly sure that Oracle's would be similar? But, out of a sort of philosophical curiosity, why does SQL Server not support them? I've read that there are .NET functions and whatnot that one can use? I know that they can be (very) inefficient - but they can be a big help?

Paul White

1:02 PM

@Vérace-getVACCINATEDNOW You can do pretty much anything with CLR, but it's not enabled as standard, not available at all in some cloud versions of the product, and many DBAs are unwilling to enable it, for a whole host of reasons, not all of them good. I don't know why there isn't native regex support.

There is native support for string splitting in modern versions though.

Vérace - get VACCINATED NOW

Yes - split_part (or similar) ? That works in PostgreSQL also - however, it's not enough. If there are other pipes + INTs + pipes further down the string, then the string will be split by those - you have to anchor the splits - tricky without regexes?

Paul White

splitting the string with ordinals ought to be enough?

Vérace - get VACCINATED NOW

Nope!

Paul White

ah you mean extra pipes at the start, not 'further down' the string?

Vérace - get VACCINATED NOW

You'll get the first pipe, INT, pipe by some muppet who put those into the middle of the string!

Sample here - dbfiddle.uk/…

Paul White

1:11 PM

Is the question you are thinking of asking: Extract the first two pipe-delimited integers from an arbitrary string?

Vérace - get VACCINATED NOW

Hence my not eliminating the db-design part of the question - very complex ways probably can be found around any regex - never underestimate the ability of the average Seán Citizen to get around any code you may have written!

That would be a very succinct way of putting it - congrats! Put that as the title!

Paul White

Well an answer might involve database design, but the question doesn't directly ask about it

Vérace - get VACCINATED NOW

It was in the tags!

Paul White

So was SSMS

Vérace - get VACCINATED NOW

SSMS was incorrect - db-design was not!

Paul White

1:13 PM

Nonsense. The question asks nothing about database design.

If the question had said something about how wise it is to have a pipe-delimited attribute in a database design...

Vérace - get VACCINATED NOW

Design is fundamental to any question - any answer (where relevant) which doesn't address it, is flawed! SSMS is a tool like pgAdmin. If it can be done on the command line, then it's not an SSMS question!

Are we here to give a man a fish or teach him how to fish?

Paul White

It's not an SSMS question if the question is not about SSMS. That's how tags work

Vérace - get VACCINATED NOW

Does not compute...

Paul White

Sorry, missing "not"

If you are merely using SSMS to run a query, it's not a good tag. If you have a question about SSMS, it benefits from that tag.

Vérace - get VACCINATED NOW

I'm not saying that every answer should be a lecture course on correct database design, but a word or two about any gross flaws in the OP's data model are more than justified?

Of course, there will be valid SSMS questions - this is not one of them!

Paul White

1:19 PM

I'm not arguing about answers addressing design, that is fine of course.

That does not make the question about design, so the tag would not be appropriate there.

Vérace - get VACCINATED NOW

OMFG - we agree - this thread should terminate immediately!

Paul White

MDCCL has some very fine database design answers to questions on that topic. He was very keen that the tag not be abused. It makes it hard for people who want to answer questions about database design to find them. People subscribe to tags and get questions in that tag emailed to them.

Vérace - get VACCINATED NOW

Maybe you're correct on this one? The question was not fundamentally about db-design!

Answer the string-manipulation/splitting - talk about how crap the data-collection is after!

Paul White

Sure.

But it is still a terrible question. -6 and deservedly closed as unclear. Pretty clear the community does not want it in its current form.

Your question sounds much more on-topic.

Not that it will be of much help to a user stuck on 2008 or 2008 R2.

Vérace - get VACCINATED NOW

Maybe there's a case for asking a question (I'll be the volunteer who'll go before the peleton d'exécution) - Extract the first n integers (themselves delimited by an arbitrary character or string) from a longer arbitrary string?. It's difficult to formulate very clearly in a concise fashion - maybe you can turn your succintness skills to it?

14 years old? Yeah - why bother - but it has raised more fundamental stuff!

Paul White

1:28 PM

That description sounds p good to me

Together with some examples, it should be clear.

Vérace - get VACCINATED NOW

Shite - I've a backlog of crap I've got to answer here! :-(

Paul White

Someone will no doubt point out that such data has no place in a respectable RDBMS

Vérace - get VACCINATED NOW

Which, mon cher ami, was my point about db-design in the first place! :-)

Paul White

Yep perfectly valid as an observation as part of a broader answer

In an ideal world, delimited data would be dealt with in the import process and stored in a proper relational design

This may not be an ideal world

I still think it's a fairly trivial problem. Split by the delimiter, number the splits, and pick the first two that parse as an integer.

Vérace - get VACCINATED NOW

@PaulWhite No - it's not! You have to anchor the separator - that's the rub! They can be in the middle of the string!

Paul White

1:35 PM

@Vérace-getVACCINATEDNOW The first two that parse as an integer

Vérace - get VACCINATED NOW

No, in this case, it's first char (pipe), then an INT, then a pipe - rather than being stuck in the middle of the string? With the data collection procedures which are obviously prevalent where the OP is from, one has to cover all the bases!

Paul White

@Vérace-getVACCINATEDNOW Can you give a brief example where my algorithm wouldn't work?

Oh never mind I see

It's like the quoted CSV problem

Though it's not clear which delimiters one should be ignored and when

At least CSV uses "double,quotes" so you can see which commas should be skipped

Vérace - get VACCINATED NOW

It's a tricky problem - hence my attempt to reopen!

Paul White

No there's none of that complexity in the closed question

Your question does though

Vérace - get VACCINATED NOW

Arrgghh... it leads on to it!

Hence my question!

Paul White

1:43 PM

Yep you have a follow-up or related question

Vérace - get VACCINATED NOW

Do you not have a bed to go to? Or which of the 5 Nestors am I speaking to?

:-)

Paul White

I'm on US east coast time quite often

Or at least I try to overlap their mornings

Vérace - get VACCINATED NOW

2:06 PM

Round about now! Well, enjoy! I presume you'll be off to the leaba soon! (leaba = bed)!

Paul White

Depends

Currently just after 9am US East

Vérace - get VACCINATED NOW

Sitting here on UTC - be off to my leaba in ~ 7.5/.8 hours!

14:15!

How do you find remote gigs? I'm thinking of looking for something within Europe - I suppose being on the western part, I'll have to wake up early if anything - but only an hour or two - and I could remain up late for the Americas...

John K. N.

3:26 PM

Did I miss anything?

ducks

I'll remove my comment if the author edits their question.

Vérace - get VACCINATED NOW

3:50 PM

@JohnK.N. Comment to what, exactly?

Paul White

The question in question

@Vérace-getVACCINATEDNOW Would you consider |||123||||||456||| to be something that should return 123 and 456?

Vérace - get VACCINATED NOW

If that's what the OP asked for, then yes! Thanks for getting my juices flowing (not like Charlene though... :-) ) - that's an excellent one for a regex! Arbitrary number of pipes followed by arbitrary int followed by arbitrary no. of... I suppose it's about all about saying "enough is enough" (declining return on investment)...

Pareto and all that!

Paul White

4:06 PM

I think the original question is strictly |num1|num2| but it's hard to be sure

I was asking about the question you had in mind for your regex solution

I'm still trying to understand the anchor thing

And what |This is more text |11|111|22222||| and stuff ||||||| should return

Sounds like it should be 11 and 111 but I'm not certain

Vérace - get VACCINATED NOW

Oh yes, you're correct - it WAS about |INT|INT|...whatever... - but it's just expanded. The anchor thing means that you can't pick up an arbitrary |INT|INT|...whatever... in the middle of the string! It's not trivial!

Take a look here.

Paul White

@Vérace-getVACCINATEDNOW Yes I saw that earlier. It still seems trivial to me to parse |num1|num2| without regex

Vérace - get VACCINATED NOW

Simple, straightforward SQL - picks up 11 and 111 from This is more text |11|111|22222||| and stuff |||||!

OK, so do it! How do you anchor the first INT?

And what about more complex patterns?

Like pipe (1st char) followed by text, followed by pipe, INT, then pipe... Unless I'm losing it, it's not a trivial problem!

Paul White

So what should |text|123|456| return?

I'm guessing two nulls?

Because the two numbers don't follow the required pattern

Vérace - get VACCINATED NOW

In the case of the question - you're bang on! There comes a point of diminishing returns - you have to involve all stakeholders - have proper data entry!

Hence my banging on about db-design - which is more than just tables!

Paul White

4:16 PM

I'm trying to understand why you think it is complex

Should that string return two nulls - or 123 and 456 - or null and 123?

Vérace - get VACCINATED NOW

The actual question isn't that complex (I have an elegant and relatively simple PostgreSQL solution) but the general problem is more complex and interesting!

Paul White

So you keep saying. But can you give me an example complex string and the results required for that string?

Vérace - get VACCINATED NOW

It should return two NULLs - because the first |text|... invalidates everything else!

Paul White

@Vérace-getVACCINATEDNOW Like this? dbfiddle.uk/…

As I say, it doesn't seem terribly complicated to split a string on a delimiter, decide if each element is an integer or not, then return those integers only if they're in positions 1 and 2.

I thought I must be missing something that requires regex and an 'anchor'

Vérace - get VACCINATED NOW

Very nice - you SQL Server guys have the TRY_... CASE/PARSE... - really cool. I'd like to get that into PostgreSQL. However, (minor critique) - that looks a hell of a lot more complicated than a regex - and in PG, I can also index by an expression!

Paul White

4:22 PM

It's just simple SQL. I didn't particularly aim for compactness.

Vérace - get VACCINATED NOW

Positions 1 & 2! But, what if if positions 1 & 2 start 15... 20 chars into the field?

And you want pipe (character)+ 1 INT (for however many chars)?

That's what the caret is for!

Paul White

@Vérace-getVACCINATEDNOW Can you give an example?

When I say "positions 1 and 2" I mean they are the first and second elements from the split on |

Vérace - get VACCINATED NOW

And I respectfully beg to differ, but your SQL is NOT simple - with regexes it's a 1/2-liner! As for efficiency - not sure, but I can index on a regex!

Yes - that's what I mean - split on those!

50 lines is not trivial SQL!

Paul White

It's 50 lines of trivial SQL

The syntax and scoping makes it look more complex than it is

Vérace - get VACCINATED NOW

But you have to ensure that the first pipe is the 1st character of the string!

Paul White

4:26 PM

@Vérace-getVACCINATEDNOW What?

Vérace - get VACCINATED NOW

And also after that INT, there's IMMEDIATELY a pipe and then IMMEDIATELY an INT and then another pipe - and also validate the INTs!

Paul White

Nope don't have to do any of that. Split on the pipe. The resulting elements either parse as an integer or they don't.

If the first split element parses as an integer, return it. If the second split element also parses as an integer, return that too (but only if the first one did).

Vérace - get VACCINATED NOW

Not in most systems - you have the joys of TRY_CAST... - the beauty of regexes is that if the search fails, it's a NULL!

Paul White

Well TRY_PARSE is slightly different from TRY_CAST/TRY_CONVERT but ok.

I can only answer for SQL Server, since that is the only database I know well

Vérace - get VACCINATED NOW

Don't even know what Try_Parse does - sounds good though! :-)

Paul White

4:32 PM

Tries to parse a string as a named type, possibly given a particular culture. Succeeds and returns that type, or null otherwise.

Josh Darnell

Nothing should return null. It is a plague.

2

Also it's not a value.

Paul White

Your mom is a plague.

Josh Darnell

:o

Vérace - get VACCINATED NOW

Yeah - PostgreSQL (wife) (and possibly Oracle (mistress) - with a bit of SQL Server (occasional girlfriend on the side)) are what interest me!

Josh Darnell

> my feelings

> my mom's feelings

SEWR--

Paul White

4:33 PM

@Vérace-getVACCINATEDNOW But anyway a string consisting of nothing but 0-9 ought to be sufficient if you trim spaces from the front and back

Vérace - get VACCINATED NOW

But is **00 00 53 ** an INT?

Paul White

Unless we're claiming that 000123 is not an integer

@Vérace-getVACCINATEDNOW Does it look like an integer to you

Do you write numbers that way

Vérace - get VACCINATED NOW

I dont' think regexes have a pattern for zero-filled integers?

Paul White

@JoshDarnell Yeah well this place has gone downhill what can I say

Vérace - get VACCINATED NOW

@JoshDarnell That's what COALESCE is for!

Paul White

4:36 PM

SELECT TRY_PARSE('00 00 53 ' AS integer) returns null

COALESCE is CASE sugar

I prefer the behaviour of ISNULL

Josh Darnell

@PaulWhite Can't imagine how that happened.

Paul White

I wonder if the home page redesign is an Agile process

Vérace - get VACCINATED NOW

We'll find out in 30 mins! :-)

Josh Darnell

@Vérace-getVACCINATEDNOW COALESCE just enables problematic users to continue their harmful behaviors :P

Paul White

I know nothing about it, but it seems like delivering something awful, then correcting in gradually based on angry feedback seems agile somehow.

Josh Darnell

4:42 PM

It really does.

Vérace - get VACCINATED NOW

@JoshDarnell If your definition of user is data-entry people who haven't been trained and are allowed access to systems that don't enforce proper data integrity, then you're correct! Problemo majoro: Paul White eariler.

As long as it pays the bills!

Josh Darnell

Agile has a concept of retrospective meetings, where you get together regularly to review the previous sprint or release and discuss what went well, what didn't, etc.

ypercubeᵀᴹ

@Vérace-getVACCINATEDNOW beware of the NULL bill ;)

Josh Darnell

I wonder how those retros go at SO.

Or maybe they skip 'em. Like most agile shops.

Paul White

I'm sure they're all v encouraging of each other

Vérace - get VACCINATED NOW

4:45 PM

@ypercubeᵀᴹ In my part of the EU, there's no €NULL bill - I'd love to be able to hand a few over...

ypercubeᵀᴹ

@Vérace-getVACCINATEDNOW your part? what makes your part so special?

Vérace - get VACCINATED NOW

Sounds like Woodstock... ahh... (think Homer Simpson contemplating a beer)...

Bill - another word for banknote?

Paul White

create table eu (currency not null)

ypercubeᵀᴹ

I've no idea what you are talking about sometimes

Josh Darnell

@PaulWhite Self congratulatory back patting is the other option for sure.

Vérace - get VACCINATED NOW

4:48 PM

Dublin slang sometimes... a "bill" depending on what you're paying for can be a €20, €50 or €100. I won't tell you how I know this!

Paul White

@JoshDarnell Awesome collaboration, team! We learned heaps!

Many learnings were learned

ypercubeᵀᴹ

heap, heap, hurray

Vérace - get VACCINATED NOW

What I learnt was that learning about learning is learning!

It's kinda like meta-meta-... learning...

Paul White

Sounds like something only possible with a regex

Vérace - get VACCINATED NOW

SELECT REGEXP_REPLACE(euro_note, '20', '500', 'g') FROM wallet;

Paul White

5:00 PM

I worked out how to do that integer split thing in a single line

I moved all the code to a function or stored procedure

Bit like hiding 10M LOC behind regex functions

Vérace - get VACCINATED NOW

5:17 PM

Great stuff - let's see it! All code is an abstraction! "Hiding" behind code is what we do! But, of course, in PostgreSQL (and Oracle), I could do it in a 1-liner. What's really important is: is engine X really efficient or not? I'm happy to accept (and interested in finding out more about) whether engine a, b or c is better and/or worse and why? Please post the code and we'll take a look and, in the best traditions of The Heap, we can discuss them!

Paul White

5:35 PM

@Vérace-getVACCINATEDNOW dbfiddle.uk/…

SELECT *
FROM t
CROSS APPLY dbo.ExtractTwoIntegers (t.a) AS R
ORDER BY t.rn;

Vérace - get VACCINATED NOW

Crude metric - lines of code. Seems that you're at at least 40+. With PostgreSQL, I can do it in 4/5 depending on formatting - and the table definition comes into play also. See here. I know that it's not always SQL which will or should win out - but I think that, in this case, regexes seem to win out. It would require a > 1M benchmark to find out. I'll see do if I have time tomorrow.

Not knocking SQL Server BTW, - it's a v. good product in many ways - it would appear that in this case, PG has the upper hand?

Josh Darnell

5:51 PM

I think lines of code, as a metric, is too crude to be useful.

You can think about whether some solution is "good" or not in a lot of different ways.

One could make that argument that writing a longer relational query is better because more DBAs would understand it (compared with those that know SQL and RegEx).

Thus it's more "maintainable" in some way.

Vérace - get VACCINATED NOW

@JoshDarnell Fair point - code is about many things. 1 is efficiency - that's why I talked about > 1M benchmark. Also, ease of comprehension - 4/5 lines is easier than 40/50? What's going on behind the scenes? That's why F/LOSS is good - you can, with sufficient expertise, go and find out - not with Oracle/SQL Server!

@JoshDarnell I think that at least a rudimentary knowledge of regexes should be de rigeur for DBAs?

Maybe I'm writing crude/simple regexes which are killing the server?

And, I'm not disagreeing/arguing - merely exploring - which really is the better approach? Which server is more efficient? I find this stuff fascinating and am interested in discussing it, and even more, being shown how and/or where I may be wrong!

ypercubeᵀᴹ

@Vérace-getVACCINATEDNOW no, not really

Vérace - get VACCINATED NOW

@ypercubeᵀᴹ - even for basic sysadmin functions? On *nix boxen? V. handy!

ypercubeᵀᴹ

I have worked as developer/dba/sysadmin/whatever for 20+ years. I still have to double check even the simplest regex expressions I come across.

my mind refuses to build the necessary paths to parse them ;)

Vérace - get VACCINATED NOW

6:06 PM

How would you solve the question?

@ypercubeᵀᴹ And I have considerable respect for your expertise! I know that regexes are a PITA, but just like amputation maybe, for a surgeon, they're the best solution?

Josh Darnell

6:18 PM

@ypercubeᵀᴹ I have had the same experience with RegEx.

peacedog

9:03 PM

Hi! Am I understanding MSSqlServer (2019)/T-SQL correctly such when OUTPUTTING on an insert, I can only reference inserted columns (at least without jumping through some hoops)?

Cade Roux

9:17 PM

@peacedog Yes, I use the MERGE technique to work around it

peacedog

Yeah I've read up on that, but I have concerns with using merge on the table I'm inserting into (at large clients anyway).

is this a thing in the other big RDBMS or is it just Microsoft?

ypercubeᵀᴹ

@peacedog this differs among DBMS.

peacedog

not surprised I guess

I'm sure there's a reason they chose not to allow it, whatever the case may be

ypercubeᵀᴹ

the OUTPUT is not standard SQL anyway and I don't think it is used in other dbms.

peacedog

ahh

ypercubeᵀᴹ

9:33 PM

@CadeRoux I thought that OUPUT can provide the values of an identity column, can it not?

peacedog

it can

ypercubeᵀᴹ

so what is the limitation?

peacedog

however, in my case my select is the product of several different things joined together, and I can't access the fields not being inserted outside of the identity

ypercubeᵀᴹ

ah, ok

peacedog

basically, one of my sources has another value I need to pair with my brand new Identity values

so I can reference it all again just a bit later

ypercubeᵀᴹ

9:34 PM

I see, yes

peacedog

But you can work around it with a merge, as @CadeRoux noted. I just have to think about if that's a good idea or not.

Cade Roux

Right, Often there is a source identity or natural key you aren't putting directly that table, but you want it in an external mapping table or audit log.

It is definitely a pain.

peacedog

I would fire off a strongly worded letter to my contact at Microsoft except that I don't have a contact at Microsoft.

Cade Roux

Kind of an unnecessary pain - clearly the MERGE can do it.

peacedog

yeah

ypercubeᵀᴹ

9:37 PM

Yeah, I can understand how it makes thing hard.

Cade Roux

I've never really dug into the execution plans to see if the MERGE is that bad, but I don't know any other good workarounds.

ypercubeᵀᴹ

In Postgres, a way around this would be to chain CTEs

Cade Roux

I typically use a CTE at the top:

WITH NewItem AS (
)
MERGE INTO Item USING NewItem ON 0 = 1
WHEN NOT MATCHED THEN
INSERT () VALUES ();
OUTPUT INSERTED.ID, NewItem.SourceID
INTO whatever

ypercubeᵀᴹ

so this would do insert into 2 tables, right?

peacedog

output into some table variable, in my case

with the actual insert going into a physical table

ypercubeᵀᴹ

9:43 PM

right

Cade Roux

Yeah, sometimes I use a table variable, often times it's a regular audit table

In the case of my audit tables, there are a lot of columns I want to keep external to teh data mart.

    OUTPUT NewData.[SourceAudit.LogID]
        , NewData.[SourceAudit.DataMartVersion]
        , NewData.[SourceAudit.StudyID]
        , INSERTED.ObservationID
        , NewData.[SourceAudit.MapRowID]
        , NewData.[SourceAudit.RuleVersion]
        , NewData.[SourceAudit.CAStudyID]
        , NewData.[SourceAudit.Source]
        , NewData.[SourceAudit.CARows]
    INTO [Logging].[SourceAudit] (
        [LogID]
        , [DataMartVersion]
        , [StudyID]
        , [ObservationID]
        , [MapRowID]

Only 1 out of 9 items is in the INSERTED

peacedog

I've done that before as well. But this time it's just a table variable. I have to put some imported data into 3 separate places (the dependencies are that B depends on A, and C depends on B and A but there are still possibilities of repeating groups in the batches), and keeping it all straight without my imaginary made up ID for the imported data gets tricky.

Cade Roux

Yes, I am also doing that same thing right now.

Putting the IDs from the table variable back into a table that is keeping track of the data generation

UPDATE DataSampling.dbo.StudyGeneration
SET StudyID = (SELECT [si].StudyID FROM @StudyIdentities [si] WHERE [si].StudyGenerationID = u.StudyGenerationID)
FROM DataSampling.dbo.StudyGeneration u
;

So I can take a row from my sample, create a Patient, and Admission for the Patient, a Case for the Admission, a Study for the Case, a StudyInstance for the signed Study, and then all the Observations...

Not something I am normally touching, but this week is the week for it...

peacedog

that's more steps than mine but otherwise yep. I have Batches, Pledges, and Contributions. And a donor can have multiple pledges in the same batch, each containing a contributions, and the amounts can all be the same. The data in the actual import is not sanitary in that regard. So I attach Id's to the rows but I have no way to link inserted data back to the import data as is.

this only becomes a problem when it's time to insert the contributions. But a problem it is.

Transcript for

The Heap™ – Consultancy ©®