CODEWITHSUNDEEP
sqlsql-server
Simran
Simran

Reputation: 569

select and insert in one statement

INSERT INTO #Temp2 (RowNumber, ValFromFunc, FuncWeight, percentage)
   SELECT 
       RowNumber, PercentMatch, @constVal, PercentMatch * @constVal
   FROM 
       dbo.MatchDate(@Date)

dbo.MatchDate(@date) is an inline TVF which looks like this :

CREATE FUNCTION [dbo].[MatchDate] (@DateFromUser nvarchar(20))
    RETURNS TABLE AS RETURN
    SELECT 
        p.Rowid AS RowNumber , 
        CAST(100 AS INT) AS PercentMatch 
    FROM 
        dbo.Patients p
    WHERE 
        datename(day, p.DOB) = @DateFromUser

GO

I have this query where I am selecting and inserting in one query. Now I have an extended requirement which needs me to check ModifiedAt for rows returned by dbo.MatchDate(@Date)and insert in #Temp2 only if ModifiedAt for rows returned is greater than some time T.

How can I do this in above query ?

Upvotes: 1

Views: 173

Answers (2)

G B
G B

Reputation: 1462

You could Add Modified date to your function BUT ONLY if it is appropriate to do so, don't add it if you are only doing it for convenience.

If you choose that route then your query should just be 

INSERT INTO #Temp2 (RowNumber, ValFromFunc, FuncWeight, percentage)
   SELECT 
       RowNumber, PercentMatch, @constVal, PercentMatch * @constVal
   FROM 
       dbo.MatchDate(@Date)
   WHERE 
       ModifiedAt > @YourComparisonDateTime

However, you wont necessarily need to add ModifiedAt because you can still perform joins on a table valued function.

INSERT INTO #Temp2 (RowNumber, ValFromFunc, FuncWeight, percentage)
   SELECT 
       f.RowNumber, f.PercentMatch, @constVal, f.PercentMatch * @constVal
   FROM 
       dbo.MatchDate(@Date) f
       inner join YourSourceTable t
          on f.RowNumber = t.RowNumber --t.RowNumber being the identity or PK column that matches f.RowNumber
          -- and any other required join conditions
   WHERE
       t.ModifiedAt > @YourComparisonDateTime

I have made a lot of assumptions here so you will need to fill in the blanks or provide additional information.

Hope that helps you out.

EDIT

INSERT INTO #Temp2 (RowNumber, ValFromFunc, FuncWeight, percentage)
SELECT 
    f.RowNumber, f.PercentMatch, @constVal, f.PercentMatch * @constVal
FROM 
    dbo.MatchDate(@Date) f
    inner join Patients p
        on f.RowNumber = p.RowID
    inner join ResultsStored rs
        on rs.RowID = f.RowNumber
WHERE
    p.ModifiedAt > rs.ModifiedAt

Upvotes: 2

wonderbell
wonderbell

Reputation: 1126

Just put a where clause in select statement (provided you table valued function returns a column named ModifiedAt) 

INSERT INTO #Temp2 (RowNumber, ValFromFunc, FuncWeight, percentage)
   SELECT 
       RowNumber, PercentMatch, @constVal, PercentMatch * @constVal
   FROM 
       dbo.MatchDate(@Date) where ModifiedAt > @T

Upvotes: 1

Related Questions