Copy data from one table to another table

Question

Post reply

Copy data from one table to another table

cmrhema

SSCommitted

Points: 1558
More actions
May 20, 2008 at 1:53 am

#72045

Hi,
I have two tables emp and empcheck1 in the same database
emp consits 50 records
and empcheck1 consists of 25 records
Both the tables have the same design, here empid is the unique key
I want to insert all the datas from emp1 to empcheck1 in such a way that it should not rewrite or duplicate the original 25 records
Whatever 25 records are there in empcheck1 already exists in emp
So i want the rest of the 25 odd records to be copied to empcheck1 from emp
Can anyone let me know how to do that
i tried out
insert into empcheck1 select * from emp where emp.empid<>empcheck1.empid
But it gave me an error

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Zahran SSCommitted Points: 1724 More actions · Answer 1

Try this

insert into empcheck1

select * from emp

where emp.empid not in(select empid from empcheck1)

- Zahran -

cmrhema SSCommitted Points: 1558 More actions · Answer 2

cmrhema

SSCommitted

Points: 1558

May 20, 2008 at 3:06 am

#817648

thank you, works

Sam-638661 Say Hey Kid Points: 701 More actions · Answer 3

This is another way of the same workaround with better performance:

insert into empcheck1

select emp.*

from emp

left join empcheck1

on emp.empid=empcheck1.empid

where empcheck1.empid is null

cmrhema SSCommitted Points: 1558 More actions · Answer 4

cmrhema

SSCommitted

Points: 1558

May 20, 2008 at 7:19 am

#817847

thanks samanta

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

samata.putumbaka (5/20/2008)
This is another way of the same workaround with better performance:

Interestingly, on SQL 2005, the first query is marginally faster.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

Same goes in 2000... in fact, our standards recommend trying the WHERE NOT IN before trying the OUTER JOIN fore exclusions of this nature.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110206 More actions · Answer 7

What about NOT EXISTS?

_____________
Code for TallyGenerator

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Sergiy (5/20/2008)
What about NOT EXISTS?

Exactly the same as NOT IN for this case in SQL 2005. In general that is what I usually see for SQL 2005.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 9

If I remember correctly, NOT EXISTS can actually be a bit faster in the presence of the correct index. Still, it uses correlation which is hidden RBAR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110206 More actions · Answer 10

Jeff Moden (5/20/2008)
If I remember correctly, NOT EXISTS can actually be a bit faster in the presence of the correct index. Still, it uses correlation which is hidden RBAR.

These 2 queries:

select * from Tally

where N NOT IN (select N from Tally)

select * from Tally T1

where NOT exists (select 1 from Tally T2

where T1.N = T2.N)

generate identical execution plans.

So, there is no any kind of hidden RBAR.

From my experience NOT EXISTS beats NOT IN when:

1) list IN is too big (does not fit into one page);

2) you need to match values in more than 1 column.

_____________
Code for TallyGenerator

starazam SSC Journeyman Points: 96 More actions · Answer 11

Hi,

I have same scenario, but with some addional option.

I have data in "temptable" and data in "originaltable".

What I want to do, I will insert all the new rows from "temptable" to "originaltable" which "originaltable" don't have. I can manager this by solution you provided.

BUT

addionally I want to update existing rows in "originaltable" table which "temptable" also have. update values with the values of "temptable". How can I achieve this part.

at the end, I will delete all from "temptable". So next time I can fill it again, and start new insert/update process.

Let me know, if you people need more details.

Waiting for response.

Regards,

Azam.