INSERT INTO a different table[RESOLVED]

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

SQL Server 2000 Forums

Transact-SQL (2000)

INSERT INTO a different table[RESOLVED]

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-20 : 14:20:28

I am trying to something like this:


INSERT INTO tblFeePaid (CaseNumber, PartNumber, LastName) VALUES ('011-987654', 1, 'Lowe')
WHERE Status = 10 FROM tblCapRec

It doesn't work. I don't know how to do the WHERE clause though. Any ideas?

Also, I want to put this in a job. Is that the only coding I would put in or do you have to put something like GO? I have never done that before. Thanks for the help!

Brenda

If it weren't for you guys, where would I be?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 14:24:20

INSERT INTO tblFeePaid (CaseNumber, PartNumber, LastName)
select '011-987654', 1, 'Lowe'
FROM tblCapRec
WHERE Status = 10

but this will insert that many rows with same values as there are rows in tblCapRec with status 10. is that really what you want???

Go with the flow & have fun! Else fight the flow

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-20 : 14:32:09

Yes, anything that is a status 10, I need to transfer to a different table. Know how to do that? I just want it to happen every night. Thanks!

Brenda

If it weren't for you guys, where would I be?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-20 : 14:33:42

Oh, I don't want those specific values inserted. That was just practice data. I need whatever data is in one table transferred to a different table.

Brenda

If it weren't for you guys, where would I be?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-20 : 14:42:53

OK, this works now:


SELECT * FROM tblCapRec
WHERE Status = 10 
INSERT INTO tblFeePaid (CaseNumber, PartNumber, LastName) 
VALUES ('321-987654', 1, 'Lowe')

But I need to take the date from tblCapRec Table. How do I do that?

tblCapRec.CaseNumber, tblCapRec.PartNumber, tblCapRec.LastName

That doesn't work. How else do I do it?

Brenda

If it weren't for you guys, where would I be?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-20 : 14:54:38

OK, this works now:


SELECT * FROM tblCapRec
INSERT INTO tblFeePaid (CaseNumber, PartNumber, LastName) 
SELECT CaseNumber, PartNumber, LastName FROM tblCapRec
WHERE Status = 10

Now my question is can I move it or do i have to insert it and then delete it from the other table?

Brenda

If it weren't for you guys, where would I be?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 15:01:33

insert it and delete it.
there is no such thing as moving with data (as is with files). i believe that is what you meant.

if it weren't for us, you'd be burried in BOL = Books OnLine = SQL Server Help

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-20 : 15:55:09

be very careful when "moving" rows from one table to the next like this.

Why?

suppose you say this:

step 1:insert into TableA select ... from TableB where Status="10"
step 2:delete from TableB where Status="10"

what happens if another process changes the data in TableA AFTER step 1 is completed, but before step 2 begins, which alters or inserts more rows with status 10? the delete will remove rows that were never first copied to TableA, which means they will be lost!

I don't believe -- someone correct me if I am wrong -- wrapping those two steps in a transaction will lock the table in this manner but I am not 100% sure.

One way to ensure this never happens:

step 1: update a column in TableA with a flag; i.e., update TableA set ReadyToMove=1 where Status="10"
step 2: copy rows from TableA to TableB where ReadyToMove=1
step 3: delete from TableA where ReadyToMove=1

This ensures you will never delete more rows than you copy.

just some ideas ...

- Jeff

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-20 : 16:38:54

What is the T-SQL to see if a case number exists in both tables? And if it does, then delete it from one table?

Brenda

If it weren't for you guys, where would I be?

morleyhill
Starting Member

19 Posts

Posted - 2004-10-21 : 06:49:31

I think what you want is-

delete TableA
where CaseNumber in (select CaseNumber from TableB)

This will delete every record in TableA that has a CaseNumber matching a record in TableB.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources