?
Solved

sql equivelent of Insert Ignore

Posted on 2010-11-18
3
Medium Priority
?
843 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 58

Accepted Solution

by:
HainKurt earned 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…
Suggested Courses

762 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