Post reply

copying a system table

  • July 1, 2010 at 2:12 pm

    #226402

    I need to add user data types to the sys.types table in my database by getting all the user datatypes in another database that do not currently exist in sys.types. I could write syntax to select into from one to the other, but that is a rather ugly approach. I wonder if there is a simpler, cleaner approach to doing this merge. (SQL Server 2005)

  • July 1, 2010 at 2:41 pm

    #1187914

    You can't insert into sys.types. Modifications to the system tables aren't allowed on SQL 2005+. Besides, sys.types is a view, not a table.

    I would suggest select both into a user table and then from that generate the CREATE and DROP statements to sync the two sets of types,

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 2, 2010 at 2:39 am

    #1188068

    You could just use the Generate Scripts wizard in SSMS.

    Make sure you set the "Include If NOT EXISTS" option to true, then select "user defined data types", then run on the database you want to create the data types on.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply