pietlinden (1/26/2014)
It's a health care company, so they do a lot of data importing, I assume.The client stated that the candidate must understand Identity in SQL. They need to know what it is and how to retrieve it after the data is inserted into a table. They’re answer can’t be, “I run a select after inserting the data”
@@IDENTITY.
Was almost a stupid question. Well, I guess it shows how little I know, because I had to read the documentation. (Man, they cram a LOT of info under one little key on my keyboard!!! Are the rest of the keys as full of info as F1?)
(You can turn SET IDENTITY INSERT ON/OFF to allow inserting values into an IDENTITY column... so I assume they're importing parent/child records into existing tables.)
I guess now I have to see if I can figure out how to use it with BULK INSERT and all those . I would assume they're either using SSIS (which I know you hate), or BULKINSERT... I saw a few articles, maybe I'll have to read up here and maybe watch a video or two...
To me, it just seemed weird that that was their only question... Seems like they must do a fair amount of SSIS or something and call BULKINSERT or whatever inside their for each loop or something.
OH, and then there are the related commands: SCOPE_IDENTITY() and IDENT_CURRENT().... hmm... won't be getting an interview for that job!
I was going to say that responding with @@IDENTITY probably won't get you the job because of the huge problems it caused when used against a table that has triggers on it. SCOPE_IDENTITY() would be the preferred method for a singleton insert and the OUTPUT clause in the INSERT or the use of the INSERTED table in a trigger would likely be the best for multi-row set inserts.
BWAAA-HAAA... shifting gears a bit and just to clear the air on certain subjects, I don't actually hate SSIS. Neither do I hate Cursors, While Loops, Recursive CTEs, Triangular Joins, Covering Indexes, SQLCLR, Powershell, Functions, Views, or dozens of other things in SQL Server (although I will always have a deep rooted and devoted hatred for XML :-D). What I do hate is what many people (not ALL but a seeming majority) have done with those things because they don't actually know enough about T-SQL/SQL Server and they end up using those other tools inappropriately. Even then, they sometimes end up botching the job with the tool they know and have chosen to use and that makes it even worse, in my eyes.
--Jeff Moden
Change is inevitable... Change for the better is not.