Use SQL scripts to import data to different databases to avoid duplication.

Use SQL scripts to import data to different databases to avoid duplication.

Preface

I believe everyone has some experience. No matter what language, once you see repetitive code in the code, you want to encapsulate it for reuse. This is also true for SQL, if we do not have an interface to maintain and frequently perform operations, we will write a script to avoid re-writing it again next time, but this involves a problem, I didn't care too much at the beginning. At some point, the boss smiled and asked me if your script could be executed repeatedly, obviously, no. If this problem cannot be avoided, such as the insert operation, other colleagues may insert duplicate data when executing the script next time. If it is online, it will be silly, so the boss gave me another lesson. Since then, every time I write a script, I add logical judgment. Yes, that is, it can be executed repeatedly.

Next, let's talk about the scenarios encountered in recent projects.

Topic Introduction

In the physical fitness test of a national adult, the adult age and the physical age are 20-59 years old. If the current date does not exceed the adult birth date, the age is reduced by one. Otherwise, for example, if the birth date of an adult is, the adult's physical age is 25 years old. If it is, the physical age is 26 years old. This is the first step. The second step is grouping. People of each age stage have different project tests, that is, they need to be grouped by age. In the body-side documents, they are divided into three groups, one of which is male 20-39 years old, one group is Women 20-39, and the last group is 40-59 for both men and women. Finally, we need to group data based on different age stages and genders.

Create the following table.

We can see from the above that there are men aged 20-39 and women aged 20-39. There are also 40-59-year-old men. The key is how we use SQL to calculate the actual age of the above adults according to the National Adult Physical Examination document.

SELECT Id ,  DATEDIFF(YEAR, Birthday, GETDATE()) AS age ,  NameFROM dbo.t1

The preceding query is obviously incorrect. The age of the person before the date of birth is not reduced by 1. Now we can use the DATEPART function. The first parameter is set to dayofyear, this parameter indicates the number of days from the specified date to the day of the year. For example, we want to obtain the number of days that have passed.

SELECT DATEPART(dayofyear, GETDATE())

SELECT DATEPART(dayofyear, '2017-12-31')

We use the number of days of birth and the number of days of the current date. If the number of days is greater than the current date, it indicates that the birthday has not arrived. Otherwise, we will lose 1.

SELECT Id ,  DATEDIFF(YEAR, Birthday, GETDATE())  - CASE WHEN DATEPART(DAYOFYEAR, Birthday) > DATEPART(DAYOFYEAR,                GETDATE()) THEN 1    ELSE 0   END Age ,  NameFROM dbo.t1

At this point, we have completed the distinction of age. Next we will insert it into another database, test2. Here I can think of two ways, if there are other welcome to add.

Left join... IS NULL

We use the left join to insert data. If there are duplicates, the primary key of the table to be inserted must not be NULL. Therefore, we can add NULL to judge whether repeated inserts can be removed.

INSERT INTO test2.dbo.t2  ( UserId ,   Birthday ,   Gender ,   Name  )  SELECT t1.Id ,    t1.Birthday ,    t1.Gender ,    t1.Name  FROM dbo.t1 AS t1    LEFT JOIN test2.dbo.t2 AS t2 ON t1.Id = t2.UserId  WHERE t2.Id IS NULL

When the execution is performed again, the number of affected rows is returned as 0.

Not exists/NOT IN

INSERT INTO test2.dbo.t2  ( UserId ,   Birthday ,   Gender ,   Name  )  SELECT t1.Id ,    t1.Birthday ,    t1.Gender ,    t1.Name  FROM dbo.t1 AS t1   WHERE NOT EXISTS(SELECT t2.UserId     FROM test2.dbo.t2 t2     WHERE t2.UserId = t1.Id)

Summary

This section describes how to use SQL scripts to avoid repeated inserts and how to query the actual age based on the date of birth.

Well, the above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.