Solved

sql equivelent of Insert Ignore

Posted on 2010-11-18
3
836 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 52

Accepted Solution

by:
Huseyin KAHRAMAN 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

697 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