Monday, July 25, 2016

Access UPDATE query to SQL Server

How do I convert an Access UPDATE query to SQL Server?


Access SQL




  • UPDATE Books INNER JOIN UpdateList
       ON Books.ISBN = UpdateList.ISBN
       SET Books.Price = [Updatelist].[price]


SQL Server
  • UPDATE Books
        SET Books.Price = [Updatelist].[price]
       
        FROM Books INNER JOIN UpdateList
           ON Books.ISBN = UpdateList.ISBN




    Tuesday, July 19, 2016

    Access InStr() function to SQL Server equivalent


    What is the SQL Server equivalent to Access InStr() function?

    TargetField: String expression being searched
    SearchValue: String expression being sought
    StartPosition: Starting position for each search (optional)
    • InStr(TargetField, SearchValue)
    • InStr(StartPosition, TargetField, SearchValue)
    SQL Server
    • CHARINDEX(SearchValue, TargetField)
    • CHARINDEX(SearchValue, TargetField, StartPosition )

    Wednesday, July 13, 2016

    Access IIf() function to SQL Server equivalent

    What is the SQL Server equivalent to Access IIf() function?


    Access SQL
    • IIf([ResultValue]>=1, [Result Value], Null)


    SQL Server
    •  CASE WHEN [Result Value]>=1
    •    THEN [Result Value]
    •    ELSE NULL 
    •  END 

    Thursday, July 7, 2016

    Access NZ() function to SQL Server equivalent


    What is the SQL Server equivalent to Access NZ() function?


    Access SQL
    • NZ([Event Timestamp])


    SQL Server Equivalent
    • COALESCE([Event Timestamp],0)
    • ISNULL([Event Timestamp],0)
      • do not confuse this with the Access isnull() function.

    Tuesday, July 5, 2016

    Access CDate() function to SQL Server equivalent


    What is the SQL Server equivalent to Access CDate() function?


    Access SQL
    • CDate([Event Timestamp])


    SQL Server Equivalent
    • cast([Event Timestamp] as datetime)
    • convert(datetime, [Event Timestamp])

    Friday, July 1, 2016

    Access DateValue() function to SQL Server equivalent

    What is the SQL Server equivalent to Access DateValue() function?


    Access SQL
    • DateValue([Event Timestamp])


    SQL Server Equivalent
    • cast([Event Timestamp] as date)
    • convert(date, [Event Timestamp])