如何根据语句状态编写插入查询？ [英] How do I write the insert query based on the statement status?

问题描述

 NEW DATABASE NAME：NEWSTUDENT_1 
 TABLENAME：STUDENT1 
 
 ID NAME BRANCHCODE NUMBER COUNTRYCODE COURSECODE 
 012 Emily 01 123 0101 20 
 104 Alex 02 124 021 23 
 109 Toms 02 245 015 54 
 145 Michael 06 458 053 26 
 
 
新数据库名称：NEWSTUDENT_2 
 TABLENAME：STUDENT2 
 
 ID NAME BRANCHCODE NUMBER COUNTRYCODE COURSECODE 
 002 Hill 036 1225 0101 20 
 104 Mary 026 1294 0217 29 
 109 Toms 02 245 0217 29 
 
新数据库名称：NEWSTUDENT_2 
 TABLENAME：STUDENTINFORMATION 
 
国家代码课程COURSENAME COURSEADDRESS 
 0101 20 ASD 4.Ave B.APT 
 0217 29 TYA 5.Ave T.APT 
 
 除了结果返回外，我想要做除外。 
 
 **我想除了并插入表结果示例：** 
 
 
 
数据库名称：NEWSTUDENT_1 
 
 TABLENAME：STUDENTINFORMATIONCHANGE 
 
 
 COUNTRYCODE COURSECODE COURSENAME COURSEADDRESS状态
 0101 20 ASD 4.Ave B.APT 0 
 0217 29 TYA 5.Ave T.APT 0 

**流程图**



1.学生2 （ID，COUNTRYCODE，COURSECODE）除了student1（ID，COUNTRYCODE，COURSECODE）==>结果3行（学生2价值行）



2.在这里，我们将看到与学生2不同的记录。



3.如果学生信息表中有记录（COUNTRYCODE，COURSECODE，COURSENAME，COURSEADDRESS），这些记录位于



4。我们将这些接收的值添加到studentInformationchange表中。



5.如果只完成这些操作，我们将给出状态值0.（STATUS 0）





怎么写除了插入查询？



我尝试过：



尝试查询：

从NEWSTUDENT_1.tblStudent1中选择ID，COUNTRYCODE，COURSECODE 
 EXCEPT 
从NEWSTUDENT_2.tblStudent2中选择ID，COUNTRYCODE，COURSECODE 
 
 INSERT INTO 
（SELECT STUDENTINFORMATIONCHANGE 
 COUNTRYCODE，COURSECODE，COURSENAME，STUDURINFORMATION的课程，0 
 .... //之后不要TE。 
 

解决方案

  INSERT   INTO  STUDENTINFORMATION 
（
 COUNTRYCODE 
，COURSECODE 
，状态
）
 
 选择 
 COUNTRYCODE 
，COURSECODE 
， 0   AS 状态
  FROM  [NEWSTUDENT_1] .DBO.tblStudent1  AS  TS1 
 其中 
  NOT   EXISTS  
（
  SELECT   1   FROM  [NEWSTUDENT_2] .DBO.tblStudent2  AS  TS2 
  WHERE  
 TS1.ID = TS2.ID 
  AND  TS21.COUNTRYCODE = TS2.COUNTRYCODE 
  AND  TS1.COURSECODE = TS2.COURSECODE 
）; 

NEW DATABASE NAME: NEWSTUDENT_1 
 TABLENAME : STUDENT1     

   ID    NAME   BRANCHCODE   NUMBER    COUNTRYCODE    COURSECODE
   012   Emily    01          123         0101              20
   104   Alex     02          124          021              23
   109   Toms     02          245          015              54
   145   Michael  06          458          053              26

    
 NEW DATABASE NAME: NEWSTUDENT_2 
 TABLENAME : STUDENT2
    
    ID    NAME    BRANCHCODE    NUMBER    COUNTRYCODE    COURSECODE
    002   Hill        036        1225         0101           20
    104   Mary        026        1294         0217           29
    109   Toms        02         245          0217           29
    
NEW DATABASE NAME:  NEWSTUDENT_2 
TABLENAME : STUDENTINFORMATION
    
    COUNTRYCODE  COURSECODE    COURSENAME    COURSEADDRESS
     0101            20           ASD         4.Ave B.APT
     0217            29           TYA         5.Ave T.APT
    
I want except to do except if the result returns.
    
   **I WANT TO EXCEPT AND INSERT TABLE RESULT EXAMPLE:**

  

     DATABASE NAME: NEWSTUDENT_1
    
     TABLENAME : STUDENTINFORMATIONCHANGE
        
        
        COUNTRYCODE   COURSECODE    COURSENAME   COURSEADDRESS    STATUS
          0101           20            ASD       4.Ave B.APT       0
          0217           29            TYA       5.Ave T.APT       0

**Flow chart**

1. student 2 (ID,COUNTRYCODE,COURSECODE) except student1 (ID,COUNTRYCODE,COURSECODE) ==> result 3 rows (student 2 value row)

2. Here we will see the records that are not 1 with student 2.

3. If there are records (COUNTRYCODE,COURSECODE,COURSENAME,COURSEADDRESS) from the student information table where these records are located

4. And we'll add these received values to the studentInformationchange table.

5. If only these operations are done, we will give the status value 0. (STATUS 0)


How to write except and insert query?

What I have tried:

TRY QUERY:

Select ID,COUNTRYCODE,COURSECODE FROM NEWSTUDENT_1.tblStudent1
EXCEPT
Select ID,COUNTRYCODE,COURSECODE FROM NEWSTUDENT_2.tblStudent2

INSERT INTO
(SELECT STUDENTINFORMATIONCHANGE
COUNTRYCODE,COURSECODE,COURSENAME,COURSEADDRESS  from STUDENTINFORMATION) ,0
 .... //After dont write.

解决方案

INSERT INTO STUDENTINFORMATION
(
 COUNTRYCODE
,COURSECODE
,STATUS
)

Select 
   COUNTRYCODE
  ,COURSECODE
  ,0 AS Status
FROM [NEWSTUDENT_1].DBO.tblStudent1 AS TS1
  where 
      NOT EXISTS
    (
   SELECT 1 FROM [NEWSTUDENT_2].DBO.tblStudent2 AS TS2
         WHERE 
               TS1.ID=TS2.ID 
           AND TS21.COUNTRYCODE=TS2.COUNTRYCODE 
           AND TS1.COURSECODE=TS2.COURSECODE
    );

这篇关于如何根据语句状态编写插入查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！