I have a scenario where I have created a function which is returning a table variable. I want to use this table variable inside a insert statement. Below is the query
INSERT INTO DNVPCDCS..d_CMRequest
(PRIORITY, RETRIES,ACTIONCD,FILENAME,TAXONOMY,NOTES,GUID,TOKEN,STATUS,ISDBUSER)
VALUES
@PRIORITY,0,'INS',@FILENAME,[dbo].[udf_GetTaxonomy](@IMAGEID,@HISTORYNO,@NEW_GUID),@NOTES,@NEW_GUID,@TOKEN,'N',NULL
I am not sure about the syntax and am getting error on this.
You can use INSERT...SELECT syntax like this:
INSERT INTO DNVPCDCS..d_CMRequest(PRIORITY, RETRIES,ACTIONCD,FILENAME,TAXONOMY,NOTES,GUID,TOKEN,STATUS,ISDBUSER)
SELECT @PRIORITY,0,'INS',@FILENAME, ...
FROM [dbo].[udf_GetTaxonomy](@IMAGEID,@HISTORYNO,@NEW_GUID) t
"..." replaces column lists because I have no idea what columns returned by udf_GetTaxonomy.
See INSERT (Transact-SQL) for more details.
Replace VALUES with SELECT and the same will work. Your code will be:
INSERT INTO DNVPCDCS..d_CMRequest
(PRIORITY, RETRIES, ACTIONCD, FILENAME, TAXONOMY, NOTES, [GUID], TOKEN, [STATUS], ISDBUSER)
SELECT
@PRIORITY, 0, 'INS', @FILENAME, [dbo].[udf_GetTaxonomy](@IMAGEID, @HISTORYNO, @NEW_GUID), @NOTES, @NEW_GUID, @TOKEN, 'N', NULL
