I have the following query that has been working so far. I modified
it slightly to work from another source table (new_products). Now,
SQL Server complains that the identity is inherited already:

"Cannot add identity column, using the SELECT INTO statement, to table
'dbo.my_products', which already has column 'id' that inherits the
identity property."

Any suggestions for a work aorund?

This is the query:
SELECT IDENTITY(INT,1,1) as seq_number, prod_number, prod_name
INTO my_products
FROM ( SELECT prod_number, prod_name
FROM new_products
WHERE ...
ORDER BY ...) sub_table

Only one IDENTITY column is allowed per table and apparently your
New_Products table already has an IDENTITY column. In a SELECT INTO
statement you can avoid copying the IDENTITY property from a column by
turning it into an expression. For example, if the prod_number is
IDENTITY:

SELECT IDENTITY(INT,1,1) AS seq_number,
prod_number+0 AS prod_number,
prod_name
INTO my_products ...

Don't use ORDER BY in the INSERT. ORDER BY isn't permitted in a derived
table unless you use TOP. ORDER BY achieves nothing useful in an INSERT
statement anyway. The IDENTITY won't necessarily follow the same
sequence as the ORDER BY.

--
David Portas
SQL Server MVP
--