complex stored procedure

[jimberger]

Hi,

I need to write a stored procedure that will add all the fields of one table (say table 1) to another table (table 2) and then delete the fields from table 1 that have just been added. can i do all this in a stored procedure? i think i need to use a join but unuure how

Thanks for your help

 

[fredericofonseca]

Yes you can

insert
delete

no join

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[lespaul]

do you mean records? Or do you want to create a new table 2 with the same field names?

INSERT INTO Table2 (SELECT * FROM Table1)

then

DELETE FROM Table1

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[DaOtH]

I guess you want to delete from the first table what you just copied to the second table... use a join.

Something like this

INSERT INTO Table2(idRecord, vchRecordData)
SELECT idRecord, vchRecordData
FROM Table1

DELETE Table1 FROM Table1
JOIN Table2 ON Table1.idRecord = Table2.idRecord
WHERE Table2.idRecord is Not Null



Just make sure you have a field that you can use to check what data to delete, a field that is identical in both tables. (So do not try to use 2 identity/autonumber columns, the can and probably will become different over time)




"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963