Click here to Skip to main content
13,764,280 members
Rate this:
 
Please Sign up or sign in to vote.
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 3 days ago
EmelK.356
Updated 2 days ago
v3

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05-2016 | 2.8.181113.4 | Last Updated 12 Nov 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100