Solved

sql equivelent of Insert Ignore

Posted on 2010-11-18
3
827 Views
Last Modified: 2012-05-10
sorry, I saw angellll response to this question but I guess I need further clarification.  Using MSSQL 2005, I want to execute the following command and have it ignore duplicate key values:

Insert Into "Sample Database".dbo."customer notes"
("Customer ID", "Note Date", Text)
Select "Customer ID", "Noted Date", Text
From "Test Database".dbo."customer notes"

The customer ID has duplicate values, but I want to ignore this.  What specifically is the syntax of the SQL statement?
0
Comment
Question by:medpipes
3 Comments
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 34164724
try

Insert Into "Sample Database".dbo."customer notes"
("Customer ID", "Note Date", Text)
Select "Customer ID", "Noted Date", Text
From "Test Database".dbo."customer notes"
where "Customer ID" not in (select "Customer ID" from "Sample Database".dbo."customer notes")
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 34164765
It depends how you want to handle it.
Your select statement that provides the data could do a group by...
e.g.

Insert Into "Sample Database".dbo."customer notes"
("Customer ID", "Note Date", Text)
Select "Customer ID",  Max("Noted Date"), Max(Text)
From "Test Database".dbo."customer notes"

However, the date and text may come from different records so:

Insert Into "Sample Database".dbo."customer notes"
("Customer ID", "Note Date", Text)
Select "Customer ID", "Noted Date", Text
FROM
(
 Select "Customer ID", "Noted Date", Text,
RowNumber() OVER (PARTITION BY "Customer ID" ORDER BY "Noted Date" DESC) as rn
 From "Test Database".dbo."customer notes"
) ilv
where rn=1

This returns the date and text for the most recent customer note for each customer

0
 

Author Closing Comment

by:medpipes
ID: 34165764
This worked and was exactly what I needed - thank you!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now