Import data into different databases with SQL scripts avoid three ways to import repeatedly

Objective

In any language, once you see repetitive code in your code, you think about encapsulation, and in SQL, if we don't have an interface to maintain and we need to do it often, we'll write a script to avoid having to write it again again, but there's a problem, and I don't really care about it at first. Until some time, the eldest brother saw me write a script after the smile asked a sentence, your script can be repeated execution, I am confused, obviously not, if you can not avoid this situation, such as inserting operations, when the next time other colleagues to execute their script may insert duplicate data, if it is online that is silly, So the boss gave me another lesson, from then on every script to add a logical judgment, yes, that is, repeatable execution. Next, let's talk about the scenarios that were encountered in the recent project.

Topic Introduction

In the national adult physique test needs to calculate the adult age and the body side age of 20-59 years, if the current date does not exceed the age of the adult is reduced by one, or not, for example, an adult is born in 1991-11-01, when the adult's body side age is 25 years old, This is the first step in 1991-10-01, when the body-side age is 26 years. The second step is grouping, each age phase of the project is different, that needs to be grouped according to age, in the body side of the document is divided into 3 groups, a group of men 20-39 years old, a group of women 20-39, the last group of men and women 40-59. The last thing we need to do is group according to different age stages and gender. We set up the following table.

From the above we can see that there are 20-39-year-old men, also 20-39-year-old women. There are also 40-59-year-olds, and the key is how we use SQL to calculate the actual age of the adults according to the National Adult physique test document.

SELECT   Id,        DATEDIFF(yearGETDATE  as age,        Name  from    dbo.t1

See the above query is obviously not correct, the age of the person not to the date of birth has not lost 1, at this time we can use the DatePart function, the first parameter is specified as DayOfYear, this parameter indicates the specified date to the number of days in the year, for example, we want to get the current number of days elapsed.

SELECT DATEPART GETDATE ())

SELECT DATEPART ' 2017-12-31 ')

We pass the date of birth and the number of days of the current date, if the number is greater than the current date, the birthday is not yet, otherwise minus 1.

SELECTId,DATEDIFF( Year, Birthday,GETDATE())        -  Case  when DATEPART(DayOfYear, Birthday)> DATEPART(DayOfYear,GETDATE()) Then 1               ELSE0ENDAge , Name fromDbo.t1

At this point we complete the age-level distinction, and then we insert it into another database test2. What I can think of here are two ways, if there are other welcome additions.

Left JOIN .... Is NULL

We use LEFT JOIN to insert, and if we repeat, the additional table primary key that needs to be inserted must not be null, so we can remove the duplicate insertion problem with null judgment.

INSERT   into Test2.dbo.t2        (UserId,          Birthday,          Gender,          Name        )        SELECT  t1. Id,                t1. Birthday,                t1. Gender,                t1. Name        from as      T1                leftJOINas  on=  T2. UserId        WHEREis   NULL  

Returns the number of affected rows as 0 when executing again

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) 
 

INSERT   intotest2.dbo.t2 (UserId, Birthday, Gender, Name)SELECTt1. Id, T1. Birthday, T1. Gender, T1. Name fromDbo.t1 asT1WHERET1. Id not inch(SELECTT2. UserId fromtest2.dbo.t2 T2WHERET2. Userid=T1. ID)

Summarize

In this section, we describe the use of write-SQL scripts to avoid repetitive insertions, and how to query the actual age based on the date of birth.

Import data into different databases with SQL scripts avoid three ways to import repeatedly