Insert from Select to remove Duplicate rows

15 Dec 2005 6:35 PM

Larry Bird

I'm want to remove duplicate rows from a table by reading from a temp table
and inserting the records into another table. However, I continue to get the
followingerror:
   Server: Msg 170, Level 15, State 1, Line 4
   Line 4: Incorrect syntax near 'bkrdiscoverdetail'.

My SQL statement is as follows:

Insert into bkrdiscoverdetail ([Account Number], [Stmnt Close], [TX Date],
[TX Description], [TX Amount], [create_date])
       (Select [Account Number], [Stmnt Close], [TX Date], [TX Description],
[TX Amount] [create_date]
              From bkrdiscoverdetailtempdup
              where [Account Number] not in bkrdiscoverdetail and
            [Stmnt Close] not in bkrdiscoverdetail and
            [TX Date] not in bkrdiscoverdetail and
                    [TX Description] not in bkrdiscoverdetail and
                    [TX Amount] not in bkrdiscoverdetail and
                    [create_date] not in bkrdiscoverdetail)
go

Does anyone see what is wrong with my SQL statement?

Thanks

15 Dec 2005 6:43 PM

JI

yes. Do the following.

Insert into bkrdiscoverdetail ([Account Number], [Stmnt Close], [TX Date],
[TX Description], [TX Amount], [create_date])
(Select
[Account Number], [Stmnt Close], [TX Date], [TX Description],
[TX Amount], [create_date]
From bkrdiscoverdetailtempdup
group by
[Account Number], [Stmnt Close], [TX Date], [TX Description],
[TX Amount], [create_date]
)

Show quote

"Larry Bird" <LarryB***@discussions.microsoft.com> wrote in message
news:2561CE1E-37A0-42E9-B9B0-C0C8D8127E24@microsoft.com...
> I'm want to remove duplicate rows from a table by reading from a temp
> table
> and inserting the records into another table. However, I continue to get
> the
> followingerror:
>   Server: Msg 170, Level 15, State 1, Line 4
>   Line 4: Incorrect syntax near 'bkrdiscoverdetail'.
>
> My SQL statement is as follows:
>
> Insert into bkrdiscoverdetail ([Account Number], [Stmnt Close], [TX Date],
> [TX Description], [TX Amount], [create_date])
>       (Select [Account Number], [Stmnt Close], [TX Date], [TX
> Description],
> [TX Amount] [create_date]
>              From bkrdiscoverdetailtempdup
>              where [Account Number] not in bkrdiscoverdetail and
>     [Stmnt Close] not in bkrdiscoverdetail and
>     [TX Date] not in bkrdiscoverdetail and
>                    [TX Description] not in bkrdiscoverdetail and
>                    [TX Amount] not in bkrdiscoverdetail and
>                    [create_date] not in bkrdiscoverdetail)
> go
>
> Does anyone see what is wrong with my SQL statement?
>
> Thanks
>

15 Dec 2005 6:47 PM

David Portas

Larry Bird wrote:
Show quote

> I'm want to remove duplicate rows from a table by reading from a temp table
> and inserting the records into another table. However, I continue to get the
> followingerror:
>    Server: Msg 170, Level 15, State 1, Line 4
>    Line 4: Incorrect syntax near 'bkrdiscoverdetail'.
>
> My SQL statement is as follows:
>
> Insert into bkrdiscoverdetail ([Account Number], [Stmnt Close], [TX Date],
> [TX Description], [TX Amount], [create_date])
>        (Select [Account Number], [Stmnt Close], [TX Date], [TX Description],
> [TX Amount] [create_date]
>               From bkrdiscoverdetailtempdup
>               where [Account Number] not in bkrdiscoverdetail and
>             [Stmnt Close] not in bkrdiscoverdetail and
>             [TX Date] not in bkrdiscoverdetail and
>                     [TX Description] not in bkrdiscoverdetail and
>                     [TX Amount] not in bkrdiscoverdetail and
>                     [create_date] not in bkrdiscoverdetail)
> go
>
> Does anyone see what is wrong with my SQL statement?
>
> Thanks

NOT IN requires a subquery or value list as its second argument.

I think this may be what you intended:

INSERT INTO bkrdiscoverdetail
([Account Number], [Stmnt Close], [TX Date], [TX Description],
[TX Amount], [create_date])
SELECT DISTINCT
[Account Number], [Stmnt Close], [TX Date], [TX Description],
[TX Amount], [create_date]
FROM bkrdiscoverdetailtempdup AS A
WHERE NOT EXISTS
(SELECT *
    FROM bkrdiscoverdetail AS B
    WHERE B.[Account Number] = A.[Account Number]
     AND B.[Stmnt Close] = A.[Stmnt Close]
     AND B.[TX Date] = A.[TX Date]
     AND B.[TX Description] = A.[TX Description]
     AND B.[TX Amount] = A.[TX Amount]
     AND B.[create_date] = A.[create_date]) ;

--
David Portas
SQL Server MVP
--