Shulou(Shulou.com)05/31 Report--

This article is about how to use SQL scripts to import data into different databases to avoid duplication. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Preface

I believe you all know that no matter what kind of language, once you see repetitive code in the code, you want to encapsulate it and reuse it. The same is true in SQL. If we don't have an interface to maintain and need to do frequently, we will write scripts to avoid having to write it again next time, but this involves a problem that I didn't care much about at first, until at some point. When the boss saw the script I wrote, he smiled and asked whether your script could be executed repeatedly. I was confused, obviously not. If this situation could not be avoided, such as inserting, the next time another colleague came to execute the script, duplicate data might be inserted, and it would be stupid if it was online, so the boss taught me another lesson. From then on, every time I wrote the script, I added logical judgment, yes. That is, repeatable.

Let's talk about the scenarios encountered in the recent project.

Topic introduction

In the national adult physique test, the adult age needs to be calculated and the lateral age is 20-59 years old. if the current date does not exceed the adult birth date, the age will be reduced by one, otherwise it will not be reduced, for example, the adult birth date is 1991-11-01. At this time, the adult's lateral age is 25 years old, and if it is 1991-10-01, the lateral age is 26 years old, which is the first step. The second step is grouping. People of each age group have different test items, that is, they need to be grouped according to their age. In the body-side document, they are divided into three groups: male 20-39 years old, female 20-39 years old, and the last group 40-59 regardless of male and female. The last thing we need to do is to group according to different ages and genders.

We set up the following table.

From the above, we can see 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-mentioned adults according to the national adult fitness test documents.

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

Seeing that the above query is obviously incorrect, and the age of people who have not reached the birth date has not been reduced by 1, we can use the DATEPART function. The first parameter is specified as dayofyear, which indicates the number of days from the specified date to the year. For example, we want to get how many days have elapsed.

SELECT DATEPART (dayofyear, GETDATE ())

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

We pass the number of days of the date of birth and the number of days of the current date, if it is greater than the number of days of the current date, it means that the birthday has not yet arrived, otherwise subtract 1.

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

Now that we have completed the distinction of age, let's insert it into another database, test2. There are two ways I can think of here, and if there is anything else, you are welcome to add.

LEFT JOIN.... IS NULL

We use the left join to insert, and if we repeat, the primary key of the table that needs to be inserted must not be NULL, so we can remove the problem of repeated insertion by adding NULL judgment.

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 executed again, the number of affected rows is 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) Thank you for reading! On "how to use SQL scripts to import data into different databases to avoid repetition" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.