Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

SQL Create a new table from a table with a unique combination of two colums

Hi

I have a table called "MARA_MBEW3" that has two columns called ValA and Article. I tried to add a primary key
that combines the two columns Article and  [ValA] using the SQL syntax below but got the error further down
I now want to create a new table that chops out any records that have a duplicate of these two records. How do I do this

ALTER TABLE MARA_MBEW3
ADD CONSTRAINT pk_MARA_MBEW3 PRIMARY KEY (Article, [ValA])
GO

Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'Siris1.MARA_MBEW3' and the index name 'pk_MARA_MBEW3'. The duplicate key value is (EI14528, C921).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
0
Murray Brown
Asked:
Murray Brown
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
DELETE 
FROM MARA_MBEW3 m
  JOIN (
      SELECT ValA, Article
      FROM YourTable
      GROUP BY ValA, Article
      HAVING COUNT(Article) > 1) dups ON m.ValA = dups.ValA AND m.Article = dups.Article

Open in new window

btw Might not be a bad idea to INSERT these into some kind of backup table, if for some reason you need them later.
0
 
chaauCommented:
To create a new table without duplicates you can use the following statement:
with cte as (SELECT *, ROW_NUMBER() OVER(PARTITION BY ValA, Article) AS rn from MARA_MBEW3
)
SELECT * INTO newTable FROM cte WHERE rn = 1

Open in new window

If you wish to store the duplicate records for later analysis use this statement:
with cte as (SELECT *, ROW_NUMBER() OVER(PARTITION BY ValA, Article) AS rn from MARA_MBEW3
)
SELECT * INTO dupsTable FROM cte WHERE rn > 1

Open in new window

0
 
alcindorCommented:
I may have misunderstood your question but would the following do the job ?

SELECT distinct ValA, Article
INTO NEWTABLE
from MARA_MBEW3
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks Jim
I tried the following and got the error below
DELETE
FROM MARA_MBEW3 m
  JOIN (
      SELECT ValA, Article
      FROM m
      GROUP BY ValA, Article
      HAVING COUNT(Article) > 1) dups ON m.ValA = dups.ValA AND m.Article = dups.Article


Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'm'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'dups'
0
 
chaauCommented:
I hope you are taking the database backup. If you run Jim's query (by inserting m after the DELETE keyword) you will end up whit a table without the duplicate records. However, there where you had 2 duplicate records will be none. I assume you want to keep at least one record for each duplicate entries
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now