How do I write the insert query based on the statement status?

Question

0.00/5 (No votes)

See more:

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.

Posted 10-Nov-18 11:08am

StudentSC

Updated 11-Nov-18 18:01pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Answer 1 · 2018-11-11T18:01:00

SQL

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
    );