stored procedure info

[crystalized]

I am just starting to use stored procedures and I am hoping to do a series of steps with a stored procedure.&nbsp;&nbsp;<br><br>What I want to do:<br>1. Select data from a table<br>2. Insert that data into another table with identical fields<br><br>Does anyone have any suggestions on the most efficient way to accomplish this task.&nbsp;&nbsp;I have a few ideas based on what I have seen for examples and what I have tried on the basic stored procedures I have already created.&nbsp;&nbsp;I do not think it is likely that any of my ideas are the best way to go about this process.<br><br>So if anyone can offer a suggestion about where to start with this I would greatly appreciate it. <p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>

 

[clawton]

To insert data into an identical table that already exists, try<br><br>INSERT INTO {Destination Table} SELECT FROM {Source Table} WHERE xxxxxx<br><br>statement.&nbsp;&nbsp;Note that if any single record causes a failure (eg. through duplicate key), the entire transaction will be rolled back, and no data will be inserted.<br><br>If your destination table does not exist prior to the statement and you wish to create it, try<br><br>SELECT * INTO {Destination Table} FROM {Source Table} WHERE xxxxx<br><br>Should you need to make changes to some of your records between the source and destination tables, the best way would be to use a cursor with a loop to read each record from source, process the data, and insert the results into the destination.&nbsp;&nbsp;Books Online has loads of examples of cursors.&nbsp;&nbsp;Any probs, let us know.<br><br>Hope it helps.<br><br>Regards<br><br>Chris.

 

[crystalized]

Thanks a bunch.<br><br>I kind of figured I might need to use the cursor method (to be honest I was kind of hoping to avoid it).&nbsp;&nbsp;I will see if I can find the examples in the books online.<br><br>thanks again. <p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>