CODEWITHSUNDEEP
sql-serverdatabase
Faraz
Faraz

Reputation: 113

How to copy data from table to another

How to copy data from table to another

  1. Insert all the instructors (except have the same ID with a student) into student table with tot_creds = 0, in the same department

  2. Now delete all the newly added "students" above (note: already existing students who have tot_creds = 0 should not get deleted)

Instructor table:

 ID      Name           dept_name    salary
10101   Srinivasan     Comp. Sci.   65000.00
12121   Wu             Finance      90000.00
15151   Mozart         Music        40000.00
22222   Einstein       Physics      95000.00
32343   El Said        History      60000.00
33456   Gold           Physics      87000.00
45565   Katz           Comp. Sci.   75000.00
58583   Califieri      History      62000.00
76543   Singh          Finance      80000.00

Student:

ID      Name      dept_name    tot_cred
00128   Zhang     Comp. Sci.    102
12345   Shankar   Comp. Sci.    32
19991   Brandt    History       80 
23121   Chavez    Finance       110
44553   Peltier   Physics       56
45678   Levy      Physics       46
70557   Snow      Physics       0

i have tried this for inserting but nothing happens 0 rows affected kindly guide me 

insert into student select ID, name, dept_name, 0 
 from instructor 
  where ID != instructor.ID

Upvotes: 1

Views: 430

Answers (4)

Murat Yıldız
Murat Yıldız

Reputation: 12022

You can use the following approach in order to copy data from one table to another table:

--Insert data from one table to another table (same or different dBs)
INSERT INTO DestinationDB.dbo.DestinationTable(ColumnX, ColumnY, ColumnZ, ColumnW, ColumnT)
--If the ID column of the DestinationTable is automatically created (isIdentity) do not insert value to the ID column.
SELECT ColumnA, ColumnB, ColumnC, 'Test', 1 
FROM SourceDB.dbo.SourceTable
WHERE < Search Conditions >

Upvotes: 0

DanielG
DanielG

Reputation: 1675

INSERT INTO Students
    (ID, Name, dept_name, salary)
SELECT 
    I.ID, I.Name, I.dept_name, I.salary 
FROM 
    Instructors I
LEFT JOIN  
    Students S ON S.ID = I.ID 
WHERE 
    S.ID IS NULL
    AND S.tot_cred <> 0


DELETE FROM  Instructors WHERE ID IN (SELECT ID FROM STUDENT)

Upvotes: 0

beercodebeer
beercodebeer

Reputation: 990

I would use a not exists clause to filter out existing records.

insert into Student (ID, name, dept_name, tot_cred)
select ID, name, dept_name, 0
from Instructor
where not exists (
select 1
from Student
where ID = Instructor.ID
)

Then remove them from the Instructors table (if I understand part 2 correctly).

delete
from Instructor
where exists (
select 1
from Student
where ID = Instructor.ID
)

This assumes, though, that ID is globally unique between both Student and Instructor tables.

Upvotes: 1

Tim
Tim

Reputation: 6060

I think you want this:

INSERT INTO student (id, name, dept_name, tot_cred)
   SELECT instructor.ID, instructor.name, instructor.dept_name, 0 
   FROM instructor LEFT JOIN student on instructor.ID = student.ID
   WHERE student.ID IS NULL
     AND instructor.tot_cred <> 0

this will insert instructors with non-zero total credits that don't match existing students. Your #2 is a little cryptic so I am guessing on what you intend with that part.

Upvotes: 0

Related Questions