Select Into Creates Identity Column on New Table

Josh,

Say you have a table like this:

CREATE TABLE MyOldTable (MyId INT IDENTITY(1,1),Name VARCHAR(100)
)

Then, you need to create another table from this for simplicity sake:

SELECT
     MyId,
     Name,
     row_number() OVER(ORDER BY Name DESC) AS MyNewIdINTO
     MyNewTableFROM
     MyOldTable

Looking at the "MyNewTable", you'll notice the MyId column is defined as an INT IDENTITY(1,1)!  Most likely you didn't intend for this to happen.  The fix is pretty simple:

Just use the CONVERT() function and you'll end up with an INT:

SELECT
     CONVERT(INT, MyId) AS MyId,
     Name,
     row_number() OVER(ORDER BY Name DESC) AS MyNewIdINTO
     MyNewTableFROM
     MyOldTable