SQL – How To Return Ref of Inserted Row

It is extremely useful to return the URN, or any other field, of a newly inserted row for a given table in a single query. The OUTPUT clause makes this possible.

insert into MY_TABLE (Field_1, Field_2, Field_3)
OUTPUT Inerted.REF
values (‘Foo’,’Bar’,’Example’)

The above example returns the REF field, which contains auto-incremented urn in MY_TABLE.

SQL Server SSMS Saving changes is not permitted

You may receive this dialogue if you’re using a fresh install of SSMS, or after an upgrade. It simply means your environment hasn’t been configured to allow certain changes which require the dropping, and re-creation, of a table.

Such actions include:

  • Adding a new column anywhere other than the end of a table
  • Deleting a column
  • Updating column configuration, such as Allow Nulls or the Data Type
  • Changing the column order

You can modify your environmental settings to allow for these actions form the Tools menu. Click Options, expand Designers, and then click Table and Database Designers. Un-tick Prevent saving changes that require the table to be re-created and click OK.

You may now commit your table changes.