How to avoid duplicate SQL data while executing the INSERT queries without source database

Question

I have got the 2 bulk data by insert the data into SQL Server database by SQL Server queries from multiple different source (multiple .sdf files (SQL CE database)) from the clients. But I have the queries of with more then 1000's records with multiple .sdf files.

When I execute the 2nd SQL queries scripts file (ex. data 2nd .sdf file) then I have got the duplicate data error occur.

How can I protect the data from duplicate records while executing the data. I cannot amend the queries like join or select manually by copy paste as more than 1000's of record with multiple table.

Sample Data:

INSERT INTO [Patients] ([PatientGuid],[FirstName],[LastName],[PatientID],[DateOfBirth],[Gender],[Comment],[HighLimit],[LowLimit],[HighLimitNova],[LowLimitNova],[Disabled],[PhysicianGuid],[CF1],[CF2],[CF3],[CF4],[CF5],[ShowLimits],[ShowLimitsNova]) VALUES (
'9192596b-8fbb-4f6f-8412-0082fa2af40d',N'Sonia Isabel',N'Pereira Nunes Santos',N'174269',{ts '1976-01-25 00:00:00.000'},1,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
GO

INSERT INTO [Patients] ([PatientGuid],[FirstName],[LastName],[PatientID],[DateOfBirth],[Gender],[Comment],[HighLimit],[LowLimit],[HighLimitNova],[LowLimitNova],[Disabled],[PhysicianGuid],[CF1],[CF2],[CF3],[CF4],[CF5],[ShowLimits],[ShowLimitsNova]) VALUES (
'd3f7fa12-6ba4-4fcb-9847-00b26bbff040',N'Antonio José',N'Maia Machado',N'31145',{ts '1957-03-15 00:00:00.000'},2,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
GO

INSERT INTO [Patients] ([PatientGuid],[FirstName],[LastName],[PatientID],[DateOfBirth],[Gender],[Comment],[HighLimit],[LowLimit],[HighLimitNova],[LowLimitNova],[Disabled],[PhysicianGuid],[CF1],[CF2],[CF3],[CF4],[CF5],[ShowLimits],[ShowLimitsNova]) VALUES (
'f716ea62-0eb6-427e-836d-00e6f82ac01e',N'Hans Rudolf',N'Küng',N'205934',{ts '1944-04-03 00:00:00.000'},2,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
GO

Load the data and remove the duplicates after the data is in the database. — Gordon Linoff
– Gordon Linoff, Commented Oct 12, 2018 at 11:39
The typical way to import external data is to store it into staging tables first, clean it up and load the cleaned data into the production tables. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 12, 2018 at 11:40
HI I can load the bulk data by 1st script (from 1st .sdf file) into SQL Server database. When I load the data from 2nd sql scripts then I face the duplicate errors. I only have the sql scripts by the customer vendor. I can not copy individuals GUIDs from the source scripts because another table have multiple GUIDs. — skt
– skt, Commented Oct 12, 2018 at 11:59

Marta B · Accepted Answer · 2018-10-12 11:49:18Z

1

Only execute INSERT if not exists on table

IF not exists(select * from [Patients] where [PatienGuid] = '9192596b-8fbb-4f6f-8412-0082fa2af40d')
    INSERT INTO [Patients] ([PatienGuid],[FirstName],[LastName],[PatientID],[DateOfBirth],[Gender],[Comment],[HighLimit],[LowLimit],[HighLimitNova],[LowLimitNova],[Disabled],[PhysicianGuid],[CF1],[CF2],[CF3],[CF4],[CF5],[ShowLimits],[ShowLimitsNova]) 
        VALUES ('9192596b-8fbb-4f6f-8412-0082fa2af40d',N'Sonia Isabel',N'Pereira Nunes Santos',N'174269',{ts '1976-01-25 00:00:00.000'},1,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

answered Oct 12, 2018 at 11:49

Marta B

4482 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ErikEJ · Accepted Answer · 2018-10-13 09:27:55Z

0

You can use my SQL Server Compact Bulk Copy library, SqlCeBulkCopy, it has an option to ignore duplicate primary key errors. It is available on NuGet.org

answered Oct 13, 2018 at 9:27

ErikEJ

42.1k5 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

How to avoid duplicate SQL data while executing the INSERT queries without source database

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related