How to insert data in SQL database without duplicates

Question

I have a SQL Server database with:

database 1	database 2
A	A
B	B
C	C

This will be inserted in a new db (database 2 here) for backup reasons. After the insert my database1 will collect new data but also keeps the old data for some days. So the next hour the program runs another insert. I don't want to have ABC stored again in database 2. This time D E needs to be added:

database 1	database 2
A	A
B	B
C	C
D	D
E	E

This is an example how it not should be:

database 1	database 2
A	A
B	B
C	C
D	A
E	B
	C
	D
	E

What is the best way to do this and also the fastest way ? I'm using SQL database with ADO.NET in C# at the moment.

Using the SQL creator in visual Studio which is connected to a SQL Server Express:

CREATE TABLE [dbo].[MBR] 
(
    [Id]          INT IDENTITY (1, 1) NOT NULL,
    [TimeStamp]   DATETIME     NOT NULL,
    [TAG]         VARCHAR(80)  NOT NULL,
    [Value]       VARCHAR(45)  NOT NULL,
    [Description] VARCHAR(128) NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Which database engine are you using, Sql Server? All relational databases have column constrains, "unique" is one of them. Can you provide your table definition? — gunr2171, Mar 08 '22 at 14:03
As @gunr2171 said. Use a UNIQUE constraint. The only way to ensure the data follows some format in the database is to make the constraint on the database level. — Brian Karabinchak, Mar 08 '22 at 14:05
Are you trying to push data to two different copies of the same database each time you manipulate data, one "live" and one "backup"? — gunr2171, Mar 08 '22 at 14:08
The "Sql creator in visual studio" connects to _something_, whether it be an installed Sql Server instance, or Sql Express. What's the connection string you're using in code? — gunr2171, Mar 08 '22 at 14:09

score 0 · Accepted Answer · answered Mar 08 '22 at 14:20

If I understand you correctly then you have database 1 (the used database for your API or something) and database 2 (backup database). And when you have back upped a record you do not want to backup it again.

How do you do the clean up and copying? If this is a program you can think about adding an extra field in database 1 named something like "IsBackedUp". In this you can store a boolean value if you have already made a backup of this record and just toggle it to the correct status.

If you let the database do the backups through a query. Then you could write a subquery in your where statement that checks if the record does not already exist.

Depending on the size of the project I would recommend making a clean up program for cleaning up the data and backups. This is in my opinion a good feature to have for software that will work with larger amounts of data and gives you more control on what to backup and what not. Same for cleaning.

How to insert data in SQL database without duplicates

1 Answers1