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:

Using ROW_NUMBER() to Get the Last Event for Each Category

Josh,

So the scenario is you have a table with a list of events like this:

CREATE TABLE [dbo].[Events](
   [EventDate] [datetime] NULL,
   [Category] [varchar](10) NULL,
   [EventDescription] [varchar](60) NULL
)  

You only want the latest record for each Category.  One way to accomplish this is the ROW_NUMBER() function...