sql equivelent of Insert Ignore
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked and was exactly what I needed - thank you!
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