0

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

Thanks

ST

3N1GM4
  • 3,372
  • 3
  • 19
  • 40
skt
  • 449
  • 14
  • 32
  • The sample has no duplicates – Panagiotis Kanavos Oct 12 '18 at 11:38
  • 2
    Load the data and remove the duplicates after the data is in the database. – Gordon Linoff Oct 12 '18 at 11:39
  • 5
    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 Oct 12 '18 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 Oct 12 '18 at 11:59

2 Answers2

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);
Marta B
  • 438
  • 2
  • 9
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

ErikEJ
  • 40,951
  • 5
  • 75
  • 115