Hello everyone. I am working with tables that need an identity column. I currently have a view in which I cast the different columns to the data type that I want. Now I want the result of this view to be inserted or merged into a table. The schema of the view can be reused, but an identity column (Start at 1, increment by 1) should be added.
However, the only option that I see, is that I define the schema of the target table somewhere (and thus write it down). I don't want this, as I would need to manage the schema in two locations (in the view where I do the casts, and the schema of the target table, although the latter script should only be executed once).
Is there a possibility to take the schema of the view, add the identity column and use this as the schema for a new table?
I already tried to create a table from the view (which worked), but if I then altered the table and added the identity column, I got an error. It seems that the identity column can only be added during the creation of a table.
I got the following error:
