Move Record from one Table to another Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Move Record from one Table to another Table

Author

Topic

Deon Smit
Starting Member

47 Posts

Posted - 2009-01-19 : 01:56:10

Hi. I have got example a table A and B.

Table A.

Item QTY
-----------
ABC 0
DEF 2
GHI 0
ZYX 1

I need to move all records with QTY=0 to Table B

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-19 : 02:01:32

insert into tableb select item,qty from tablea where qty = 0

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-19 : 02:01:36

SELECT * INTO newtable FROM oldtable WHERE Qty = 0

Jai Krishna

Deon Smit
Starting Member

47 Posts

Posted - 2009-01-19 : 02:22:23

Thank You.

How can I Delete it then after it moved in the same script?

Deon Smit
Starting Member

47 Posts

Posted - 2009-01-19 : 02:23:15

What is the difference between Incert into and Select Into?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 02:24:39

quote:
Originally posted by Jai Krishna

SELECT * INTO newtable FROM oldtable WHERE Qty = 0

Jai Krishna

this will create a new table and copy records which is not want OP wants. he wants to copy to already existing table

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-19 : 02:25:14

if u use select into it will create the newtable default and if u use insert into u have to create the tableb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 02:25:37

quote:
Originally posted by Deon Smit

What is the difference between Incert into and Select Into?

insert into inserts records to already existing table while select into creates a new table and populates it with the selected records

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-19 : 02:26:15

quote:
Originally posted by Deon Smit

Thank You.

How can I Delete it then after it moved in the same script?

delete tablename where qty = 0

Deon Smit
Starting Member

47 Posts

Posted - 2009-01-19 : 03:16:36

Thank You Both very much.

The reason why I want to delete it directly after move is...

I move 2 records

A new record gets created after I move. Now I do the delete. And delete 3 records. I loose 1 record?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 03:22:14

quote:
Originally posted by Deon Smit

Thank You Both very much.

The reason why I want to delete it directly after move is...

I move 2 records

A new record gets created after I move. Now I do the delete. And delete 3 records. I loose 1 record?

yup. you'll loose that 1 record if you simply fire a delete. actually what all do you want to remove? moved 2 or newly created 1?

Deon Smit
Starting Member

47 Posts

Posted - 2009-01-19 : 03:28:06

Like this.

I have 2 records that can be moved. I move them. After I move them another one gets created. Now I have 3 records and I only moved 2. Now when I delete I delete all 3.

Kan I do the following.

Delete Record from TableA where record Exists in TableB

This should just delete then the ones already moved correct? How would I put that is a script.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-19 : 03:30:12

try this
delete tablea where item exists(select item from tableb)
delete tablea where item in (select item from tableb)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 03:34:21

quote:
Originally posted by bklr

try this
delete tablea where item exists(select item from tableb)
delete tablea where item in (select item from tableb)

the first one is not syntactically correct. it should be

delete a from tablea a where exists(select 1 from tableb where item=a.item)

Deon Smit
Starting Member

47 Posts

Posted - 2009-01-19 : 03:42:48

I will Test this. Thanks for the great response

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 03:56:54

[code]DELETE a
OUTPUT deleted.Item,
deleted.Qty
INTO TableB
FROM TableA AS a
WHERE Qty = 0[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources