copy table1 and create table2, query table1 for changes and add them to table2

I am copying data from table1 to create table2. I would like to create table2, do testing then append current data from table1 into table2.

Can anyone help me write a query that takes all data from table1 that is not in table2 and inserts the data in table2?

Thanks,
LVL 1
sqlagent007Asked:
Who is Participating?
 
Dave FordConnect With a Mentor Software Developer / Database AdministratorCommented:
The following "simple-case" works for me:

create table  employee (                
  employeename varchar(50),
)

create table  employee2 (                
  employeename varchar(50),
)

insert into employee
values ('Greg Graffin')    

insert into employee
values ('Dino Cazares')  

insert into employee
values ('Lee Ving')      

insert into employee2
select * from employee

insert into employee
values ('Devin Townsend')  

At this point, the employee table had one more row than employee2.

The following statement inserts that extra row into employee2.

insert into employee2                              
select                                                    
   e.employeename                                        
from                                                      
   employee e exception join                        
   employee2 e2 on e.employeename  = e2.employeename

HTH,
DaveSlash
0
 
Kevin3NFCommented:
INsert table2
Select *
from table1 as one left outer join table2 as two
on one.id = two.id
where two.id is null
0
 
r_a_j_e_s_hCommented:
u can use DTS to trnsfer the data, which will be very useful and gives knowledge in DTS
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Kevin3NFCommented:
You can do this with or without DTS.  Its not really needed.
0
 
awking00Commented:
Using DaveSlash example,

insert into employee2
select * from employee
minus
select * from employee2;
0
 
Kevin3NFCommented:

insert into employee2
select * from employee
minus
select * from employee2;


That won't work if this is SQL Server...not sure what platform this is.....MINUS does not exist in T-SQL
0
All Courses

From novice to tech pro — start learning today.