Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2005-04-04
6
Medium Priority
?
962 Views
Last Modified: 2012-08-13
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,
0
Comment
Question by:sqlagent007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 18

Accepted Solution

by:
Dave Ford earned 2000 total points
ID: 13699782
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13700277
INsert table2
Select *
from table1 as one left outer join table2 as two
on one.id = two.id
where two.id is null
0
 
LVL 3

Expert Comment

by:r_a_j_e_s_h
ID: 13705962
u can use DTS to trnsfer the data, which will be very useful and gives knowledge in DTS
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13706870
You can do this with or without DTS.  Its not really needed.
0
 
LVL 32

Expert Comment

by:awking00
ID: 13708221
Using DaveSlash example,

insert into employee2
select * from employee
minus
select * from employee2;
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13708391

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

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question