User defined Table Types RRS feed

  • Question

  • Hi,

    Could anyone please tell me on what would be the best way to model oracle's user defined table types in SQL Server 2008 ?




    Tuesday, July 6, 2010 8:01 PM


  • If I understand Oracle's user defined table types, you should be able to do the same thing in SQL Server 2008. See the CREATE TYPE reference here: http://msdn.microsoft.com/en-us/library/ms175007.aspx. The example at the end of this topic is as follows:

    /* Create a user-defined table type */
    CREATE TYPE LocationTableType AS TABLE
        ( LocationName VARCHAR(50)
        , CostRate INT )

    Then you could create instances of the LocationTableType and insert rows into those instances.

    declare @testtable as LocationTableType
    insert @testtable (LocationName, CostRate) values ('test', 10)
    select * from @testtable

    Jason Roth

    Friday, July 9, 2010 5:13 PM