>>>I have a table [FCA_Batch] that will have a new batch row every time I process a feed that comes in as CSV and I insert into a SQL Table [FCA_staging]
>>>
>>>Every row inserted into FCA_Staging will get the FCA_Batch_id of the row craeted to store metadata about that process.
>>>
>>>I want to create the new row in FCA_Batch with Execute SQL Task. My SQL is
>>>
>>>INSERT INTO [CA_BackFeed].[dbo].[FCA_Batch]
>>> ([Process_datetime])
>>> VALUES
>>> (getdate())
>>>GO
>>>
>>>SELECT ident_current('FCA_Batch')
>>>
>>>I have ResultSet = Single row and on the result set tab
>>>
>>>Resultname = NewID var name = User::BatchID
>>>
>>>But when I execute I am getting
>>>
>>>Error: No result rowset is associated with the execution of this query.
>>>[Execute SQL Task] Error: An error occurred while assigning a value to variable "BatchID": "Exception from HRESULT: 0xC0015005".
>>>
>>>Obviously I'm missing something. I'm sure this matter of creating a "header" row before creating the "child" rows is not uncommon so any suggestion on how to fix this or for an alternative best-practice implementation greatly appreciated.
>>>
>>>TIA
>>
>>I suggest to change the above to
>>
>>INSERT INTO [CA_BackFeed].[dbo].[FCA_Batch]
>>([Process_datetime])
>>OUTPUT Inserted.BatchID
>>SELECT getdate()
>>GO
>
>
>Wow. Thank you . That did it but now I need to know why?
>
>The OUTPUT Inserted.(fieldname) is new to me, though I think I have seen the Insert used it in some of Denis's triggers.
>
>So this means a lot of places I used IDENT_CURRENT or something similar I could have used this to return the new PK from an SP?
>
>Does this return the PK, the column with the name specified or what?
>And why is it before the SELECT getdate()?
>
>And why use SELECT for the getddate()?
>
>If this is basic stuff I should know just point me in whatever direction in BOL
>
>But in the meantime, thanks a lot for giving me a trick that will come in very handy

If it was basic stuff I didn't know it, either ;-)

This is the kind of thread I had in mind a few weeks ago when one of her regular tormenters was giving her a hard time for no good reason. I said she had quite advanced knowledge of SQL Server and it's getting more advanced all the time.

My SQL chops are getting a workout in my new job. Part of the challenge is that we are heavily dependent on a commercial health care app whose published technical specs are limited, to say the least. I feel like I am wearing my detective's hat for most of the work week. Which is good. I have a limited capacity for repetition, and now my days sure aren't repetitious.

The good is still good. I am ramping up on ASP, HTML, CSS, JavaScript, and VBScript. (You can't really learn one of them without learning the rest, which is part of the web development learning curve). We are also using a very cool freeware tool called AutoHotkey. In a reasonably straightforward way it allows you to automate both desktop apps and web apps with scripts that execute keyboard presses and mouse clicks. A lot of the standard reports, uploads, and downloads where I work are the same every day, week, month, whatever. A number of machines do nothing all day but execute automated scripts so users don't have to spend the time doing so and put up with the tedium. Recommended -- http://www.autohotkey.com/ .