Stored Procedures - copy data from one table to another

Stored Procedures - copy data from one table to another

Post by Jeff » Wed, 14 Jan 2004 22:49:52



I have an business application that takes an order from an order table and
will place it into a fee table and would like to do it within a stored
procedure. I don't know how to get a "recordset" and copy it. What I need is
just like the INSERTED and DELETED tables in DELETE / INSERT triggers.

VB example of what I am trying to do...

getRs.Open("select * from feeOrderTbl where receiptID = 555", db....)
setRs.Open("feeTbl")

while not getRs.EOF
    setRs("field_1") = getRs("field_1")
    setRs("field_?") = getRs("field_?")
    '... and so on
    getRs.moveNext()
wend

getRs.close
setRs.close

Jeff

 
 
 

Stored Procedures - copy data from one table to another

Post by David Porta » Wed, 14 Jan 2004 23:54:24


INSERT INTO FeeTbl (col1, col2, col3, ...)
    SELECT col1, col2, col3, ...
        FROM FeeOrderTbl
        WHERE receiptid = 555

You should try to keep data duplication to a minimum. Ideally each item of
data should be recorded once only in your database. Depending on your
requirements you could instead insert the primary key (Receiptid?) from
FeeOrderTbl into FeeTbl and retrieve the other columns later with a join
when you need them.

Don't use SELECT * in program code either client-side or server-side.
Listing the required columns is safer and easier to maintain.

--
David Portas
------------
Please reply only to the newsgroup
--

 
 
 

Stored Procedures - copy data from one table to another

Post by Norma » Thu, 15 Jan 2004 00:01:08


Jeff,

SQL is a set based langauge, not procedural.

INSERT feeTbl ( col1, col2 )
select col1, col2 from feeOrderTbl where receiptID = 555

Norman

 
 
 

Stored Procedures - copy data from one table to another

Post by Joe Celk » Thu, 15 Jan 2004 01:12:01


If the two tables are identical in structure (i.e. represent the SAME
entities in the data mdoel) and you are simulating a set of paperwork
desk baskets, then you ought to add a status code column instead.  

If they are totally different kinds of things, as they shoudl be, then
you can use an "INSERT INTO ... SELECT .. FROM.. WHERE.." statement and
add the different columns in the SELECT list.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Stored Procedures - copy data from one table to another

Post by Jeff » Thu, 15 Jan 2004 02:22:35


There is a lot of fields in the feeOrderTbl that aren't in the feeTbl, like
delivery notes, warehouse location....

I will be using the unanimous suggestion of...

"INSERT INTO ... SELECT .. FROM.. WHERE.."

as it does exactly what I need, and works like a charm!

Thanks all,

Jeff

 
 
 

Stored Procedures - copy data from one table to another

Post by Joe Celk » Thu, 15 Jan 2004 19:13:22


Quote:>> There is a lot of fields [sic] in the feeOrderTbl that aren't in the

feeTbl, like delivery notes, warehouse location.... <<

You might want to drop those silly "-tbl" postfixes and start using
proper data element names as per ISO-11179; this is SQL and not BASIC.
Columns are not fields, rows are not records and tables are not files --
big differences.  

If a fee order is a sub-species of Fees, then you should consider a
table that contains the extra attributes and relate it to the Fees table
with DRI actions.  This is one of the many reasons a table is not a file
and a column is not a field.  You do not design one table at a time,
like you did with files.  You design an entire schema at once, as a
complete model.  And the goal is to remove redundancy in the data
(normalization is only one tool for doing this).  

You are storing the same data in two places, thus subverting the reason
we moved from file systems DBMS.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!