Why does @Table not work for me?

Question

Can anybody tell why is this query not working?

DECLARE @unwantedRows TABLE 
( 
    ProductId INT, 
    ProductName VARCHAR(50),
    Description VARCHAR(50),
    Category VARCHAR(50),
    Repetitions VARCHAR(50)

);

Select *
INTO @unwantedRows From
(
Select a.*,Row_Number() Over(Partition By ProductId Order By ProductId)  As [Repetitons]  from tblProduct a
) As A

Where A.Repetitons > 1

Error i get is

`Msg 102, Level 15, State 1, Line 12 Incorrect syntax near '@unwantedRows'. Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'As'.

Edit :

Now it's giving with Repetitions :-

INSERT
INTO @unwantedRows 
Select a.*,Row_Number() Over(Partition By ProductId Order By ProductId)  As [Repetitons]  from tblProduct a
Where a.Repetitons > 1

`

Invalid column name 'Repetitons'.

Answer 1

Because select into creates a table, and you want insert into @unwantedRows select * from ...

---

EDIT

And then, you're not allowed to use a window function (such as Partition) in a Where clause. If you must do it, wrap your select into another select:

select * from (select * from ...) as a where a.Partition > 1

Answer 2

One error i found is its not select into its select .. insert into statement

Following query working fine without errors i.e syntax error

DECLARE @unwantedRows TABLE 
( 
    ProductId INT, 
    ProductName VARCHAR(50),
    Description VARCHAR(50),
    Category VARCHAR(50),
    Repetitions VARCHAR(50)

);

insert INTO @unwantedRows 
Select a.*,Row_Number() Over(Partition By ProductId Order By ProductId)  As [Repetitons]  from tblProduct 
Where A.Repetitons > 1

insetead of above query you can also try this

insert INTO @unwantedRows 
select * from (
Select a.*,Row_Number() Over(Partition By ProductId Order By ProductId)  As [Repetitons]  from tblProduct ) d
Where d.Repetitons > 1

Answer 3

You need to remove the SELECT INTO and you need to specify your columns.

INSERT @unwantedRows 
SELECT a.ProductID, a.ProductName, a.Description, a.Category, a.Repetitions
FROM (
    Select *, Row_Number() Over (Partition By ProductId Order By ProductId) As [Repetitons]  
    from tblProduct) As A
Where A.Repetitons > 1;

Answer 4

insert into @unwantedRows 
Select * from
(
Select a.*,Row_Number() Over(Partition By ProductId Order By ProductId)  As [Repetitons]  from tblProduct a
) As A
Where A.Repetitons > 1