September 22, 2009 at 5:55 pm
Hi All,
I have a question on inserting data to a table where some data already exists and I don't want to add them again. here is the scenario,
I have two tables.. Source and Destination( where I want to insert data). Both tables have same 5 columns. There is no Unique Constraint, Primary Key, Composite PK etc and I can't add anything. Source Table has 200 records and Destination table has 90 records. I want to insert those 110 records from source table to Destination table which do not exists in destination table now.
Also, in both the tables there are duplicate records in all the columns. Each column has duplicate records. but at any time one particular ROW(Records) is not Duplicate with any other ROW(Record).
How to insert this data? Thanks in advance...
September 22, 2009 at 6:23 pm
Your sentence
Also, in both the tables there are duplicate records in all the columns. Each column has duplicate records. but at any time one particular ROW(Records) is not Duplicate with any other ROW(Record).
Is a little unclear, let me see if I can clarify. There will be duplicated field data, but there will not be a combination of all 5 fields duplicated.
Also, you can't know that there will be 110 records that will be inserted, it could be more depending on how many of the 90 are also in the 200, you can only get 110 if you KNOW already that those 90 are all in the 200.
You can try:
INSERT dbo.Destination
( Field1,
Field2,
Field3,
Field4,
Field5 )
SELECT s.Field1,
s.Field2,
s.Field3,
s.Field4,
s.Field5
FROM dbo.Source s
WHERE NOT EXISTS ( SELECT 'X'
FROM dbo.Destination d
WHERE d.Field1 = s.Field1
AND d.Field2 = s.Field2
AND d.Field3 = s.Field3
AND d.Field4 = s.Field4
AND d.Field5 = s.Field5 )
GO
CEWII
September 23, 2009 at 4:28 pm
Yes that works.. Thanks
September 23, 2009 at 8:15 pm
Great, I thought it would..
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply