I want to insert data from table1 into table2, where both contain the same fields, and table1 contains many duplicates. (This table is cleared and re-populated daily). I want to insert records from table1 into table2 and not allow a duplicate ID in table2. For the purpose of this question, the fields in the tables are ID (int), FName varchar(20), LName varchar(20), State char(2), RecordID (int). The query that I am trying to use is:
INSERT INTO [dbo].[table2] ([ID], [FName], [LName], [RecordID]) SELECT [ID], [FName], [LName], [RecordID] FROM [dbo].[table1] WHERE [ID] NOT EXISTS (SELECT [ID] FROM [dbo].[table2])
The result records in table2 contain duplicate ID values.
Any assistance would be appreciated.