none
Create a table valued function to get dates from a table and run a query

    Question

  • Hi All,


    Here is my requirement,

    I need to create a table valued function which basically runs the following query for the current date

    SELECT * FROM OPENQUERY([abc],
    'select * from aaa.fnData(''11-18-2013'',''11-18-2013'',3)')

    and insert it into a table which i can basically do like this

    INSERT Test
      SELECT * FROM
    (
    SELECT * FROM OPENQUERY([abc],
    'select * from aaa.fnData(GETDATE(),GETDATE(),3)')
    ) AS X     ;



    Its fine until here, but here is where i am stuck at,So i have a table which has only one column named date and some dates in it which will be like lets say

    11-17-2013
    11-16-2013

    as the values of the column , now i must get these dates from the table and run the above open query and insert the data into my test table for each of the dates in that table.


    Can someone please help me with any ideas?

    If i am not clear please let me know.


    Thanks

    Tuesday, November 19, 2013 9:08 PM

Answers

  • I have recreated this based on my environment:

    In Remote Server created a function

    CREATE Function [dbo].[FNC_TEST]
    (@Date date)
    Returns Table
    return(Select Convert(varchar,@Date,103) Dates)

    Then used cursor

    Declare @Test table (DatePick Date)
    Insert Into @Test
    Values('01-16-2013')
          ,('01-17-2013')
    	  ,('11-18-2013')
    
    Declare @vDate Date
    Declare @vQuery nvarchar(200)
    
    Declare vCur Cursor 
    for
    Select DatePick
    From @Test
    Open vCur
    Fetch next from vCur into @vDate
    While @@FETCH_STATUS=0
    Begin
         Set @vQuery='SELECT * FROM OPENQUERY([LINKED SERVER],
                      ''select * from REMOTESERVER.dbo.FNC_TEST('''''+CONVERT(VARCHAR(10),@vDate,110)+''''')'')'
    
         Exec	  sp_executesql   @stmt = @vQuery
    	 Fetch next from vCur into @vDate
    End
    close vCur
    deallocate vCur
    

    You can use the above as guideline.

    Change the second part with your appropriate statement, linked server and function. Also add date parameters as required.

    Hope that helps

    • Marked as answer by SqlDev12 Thursday, November 21, 2013 2:33 PM
    Wednesday, November 20, 2013 3:38 PM

All replies

  • Hi Taherul,

    That post was by me but here i am trying to achieve something different, If you can take a look at it i am trying to get the date from a sql table and insert into another table.

    Can you please look at it and let me know if we have any ideas?

    Thanks

    Tuesday, November 19, 2013 9:17 PM
  • Firstly I would suggest you to review the function aaa.fnData so that we can directly join the date-Table inside the function aaa.fnData.

    If the above case is not possible then use CURSOR to loop through the dates in the date-Table and inserting the resultset into the Test table.


    Regards, RSingh

    Wednesday, November 20, 2013 3:22 AM
  • Hi Singh,

    Thanks for your reply, Actually i cannot look into that function as it is on a different server which we dont have access to. Can you please help me with how to use the cursors? Actually i have never used one so can you explain me how to achieve my task with the cursors?

    Thanks

    Wednesday, November 20, 2013 2:24 PM
  • I have recreated this based on my environment:

    In Remote Server created a function

    CREATE Function [dbo].[FNC_TEST]
    (@Date date)
    Returns Table
    return(Select Convert(varchar,@Date,103) Dates)

    Then used cursor

    Declare @Test table (DatePick Date)
    Insert Into @Test
    Values('01-16-2013')
          ,('01-17-2013')
    	  ,('11-18-2013')
    
    Declare @vDate Date
    Declare @vQuery nvarchar(200)
    
    Declare vCur Cursor 
    for
    Select DatePick
    From @Test
    Open vCur
    Fetch next from vCur into @vDate
    While @@FETCH_STATUS=0
    Begin
         Set @vQuery='SELECT * FROM OPENQUERY([LINKED SERVER],
                      ''select * from REMOTESERVER.dbo.FNC_TEST('''''+CONVERT(VARCHAR(10),@vDate,110)+''''')'')'
    
         Exec	  sp_executesql   @stmt = @vQuery
    	 Fetch next from vCur into @vDate
    End
    close vCur
    deallocate vCur
    

    You can use the above as guideline.

    Change the second part with your appropriate statement, linked server and function. Also add date parameters as required.

    Hope that helps

    • Marked as answer by SqlDev12 Thursday, November 21, 2013 2:33 PM
    Wednesday, November 20, 2013 3:38 PM
  • Also in the previous post Conversion failed when converting date and/or time from character string" while executing a table valued function , Allen Li - MSFT already suggested you about dynamic SQL.

               
    Avatar of Allen Li - MSFT                                                   

    Allen Li - MSFT

    Pactera

    MSFT CSG

    Wednesday, November 20, 2013 3:45 PM
  • Hi SqlDev12,

    With the current question, there is only a little difference from the original one, I suggest reading my previous reply first. Regarding to the current question, do you mean that you want to pass "11-17-2013" and "11-16-2013" into the following codes just like ''11-18-2013'' does?

    INSERT Test
       SELECT * FROM 
     (
     SELECT * FROM OPENQUERY([abc],
     'select * from aaa.fnData(''11-18-2013'',''11-18-2013'',3)')
     ) AS X     ;

    If so, we don’t need to use cursor since it may affect the query performance here. I still suggest changing the aaa.fnData() to its definition codes, for example:

    declare @sql nvarchar(max);
     set @sql= 'insert into Test(col1,col2) select t1.col1,t1.col2 from [LinkedServerName].[DBName].[SchemaName].[TBName1] t1
     inner join [LinkedServerName].[DBName].[SchemaName].[DateTB] t2 on t1.[DateCol]=t2.[DateCol]';
     exec sp_executesql @sql;

    Please there is still anything unclear, please post the definition codes of aaa.fnData() function and the related tables here for analysis.

    Best Regards,
    Allen Li

    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support

    Thursday, November 21, 2013 1:54 AM
  • Hi Taherul,

    I was able to make an insert into my final table  by modifying this code.

    Thanks

    Thursday, November 21, 2013 2:33 PM