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)
 stored procedure

Author  Topic 

gary_gill
Starting Member

4 Posts

Posted - 2005-05-12 : 17:01:06
Hi
I want to create a stored procedure.
I have 2 tables called orders and orderdetail.Now i want to move orders
from these tables in to new tables called orders2 and
orderdetail2.Perticularly those orders which r there more than 60 day(means
those orders which r 60 day old ,,to new tables) and removing them from
older tables(first tables) that is orders and orderdetails and the condition of moving is that I can only move when transaction_status is approved.Please help me
this is urgency.here r the tables

orders tables look like this

column name type
GENNUMBER decimal 17 1
ORDERKEY varchar 10 0
STORERKEY varchar 15 0
EXTERNORDERKEY varchar 30 0
ORDERDATE datetime 8 0
DELIVERYDATE datetime 8 0
PRIORITY varchar 10 0
CONSIGNEEKEY varchar 15 0
C_CONTACT1 varchar 30 1
C_CONTACT2 varchar 30 1
C_COMPANY varchar 45 1
C_ADDRESS1 varchar 45 1
C_ADDRESS2 varchar 45 1
C_ADDRESS3 varchar 45 1
C_ADDRESS4 varchar 45 1
C_CITY varchar 45 1
C_STATE varchar 2 1
C_ZIP varchar 18 1
C_COUNTRY varchar 30 1
C_ISOCNTRYCODE varchar 10 1
C_PHONE1 varchar 18 1
C_PHONE2 varchar 18 1
C_FAX1 varchar 18 1
C_FAX2 varchar 18 1
C_VAT varchar 18 1
BUYERPO varchar 20 1
BILLTOKEY varchar 15 0
B_CONTACT1 varchar 30 1
B_CONTACT2 varchar 30 1
B_COMPANY varchar 45 1
B_ADDRESS1 varchar 45 1
B_ADDRESS2 varchar 45 1
B_ADDRESS3 varchar 45 1
B_ADDRESS4 varchar 45 1
B_CITY varchar 45 1
B_STATE varchar 2 1
B_ZIP varchar 18 1
B_COUNTRY varchar 30 1
B_ISOCNTRYCODE varchar 10 1
B_PHONE1 varchar 18 1
B_PHONE2 varchar 18 1
B_FAX1 varchar 18 1
B_FAX2 varchar 18 1
B_VAT varchar 18 1
INCOTERM varchar 10 1
PMTTERM varchar 10 1
DOOR varchar 10 0
ROUTE varchar 10 0
STOP varchar 10 0
OPENQTY decimal 17 1
STATUS varchar 10 0
DISCHARGEPLACE varchar 30 1
DELIVERYPLACE varchar 30 1
INTERMODALVEHICLE varchar 30 0
COUNTRYOFORIGIN varchar 30 1
COUNTRYDESTINATION varchar 30 1
UPDATESOURCE varchar 10 0
TYPE varchar 10 0
ORDERGROUP varchar 20 0
NOTES varchar 2000 1
EFFECTIVEDATE datetime 8 0
ADDDATE datetime 8 0
ADDWHO varchar 30 0
EDITDATE datetime 8 0
EDITWHO varchar 30 0
FORTE_FLAG varchar 6 0
TRAFFICCOP varchar 1 1
ARCHIVECOP varchar 1 1
ITEM_NUMBER decimal 9 1
CONTAINERTYPE varchar 20 1
CONTAINERQTY decimal 17 1
BILLEDCONTAINERQTY decimal 17 1
CHEPPALLETINDICATOR varchar 10 1
C_EMAIL1 varchar 55 1
C_EMAIL2 varchar 55 1
TRANSPORTATIONMODE varchar 30 1
TRANSPORTATIONSERVICE varchar 30 1
EXTERNALORDERKEY2 varchar 30 1
SUSR1 varchar 30 1
SUSR2 varchar 30 1
SUSR3 varchar 30 1
SUSR4 varchar 30 1
SUSR5 varchar 30 1
NOTES2 varchar 2000 1
OHTYPE varchar 10 1
LOADID varchar 20 1
SUB_TOTAL decimal 9 1
HANDLING_TOTAL decimal 9 1
TAX_TOTAL decimal 9 1
DELIVERYFEE decimal 9 1
DISCOUNT decimal 9 1
ORDER_TOTAL decimal 9 1
C_SUBURB varchar 32 1
C_REGION varchar 2 1
B_SUBURB varchar 32 1
DELIVERYLOCID decimal 9 1
NEWDELIVERYLOCFLAG varchar 1 1
C_TITLE varchar 5 1
C_MIDDLEINITIAL varchar 2 1
C_LASTNAME varchar 32 1
C_MIDDLEINITIAL1 varchar 2 1
C_LASTNAME1 varchar 32 1
C_TITLE1 varchar 5 1
B_MIDDLEINITIAL varchar 2 1
B_LASTNAME varchar 32 1
B_MIDDLEINITIAL1 varchar 2 1
B_LASTNAME1 varchar 32 1
PICKINGINSTRUCTIONS varchar 255 1
DELIVERYINSTRUCTIONS varchar 255 1
CUSTOMERMESSAGE varchar 255 1
CUSTOMERID varchar 15 1
FACILITY_ID varchar 10 1
DESTINATION_ID varchar 20 1
SHIPMETHOD varchar 20 1
DELIVERYDATE_END datetime 8 1
TOTE_ESTIMATE decimal 9 1
CANCEL_DATE datetime 8 1
CANCEL_DESCR varchar 255 1
CANCEL_CODE varchar 20 1
EXTERNSTATUS varchar 10 1
PICK_LIST_STATUS int 4 1
ONICE_EMAIL_ID varchar 20 1
MAJOR1 varchar 35 1
MAJOR2 varchar 35 1
CUSTACCOUNTTYPE varchar 4 1
DELFROMTIME datetime 8 1
DELTOTIME datetime 8 1
SHIFTID int 4 1
TRANSACTIONSTATUS varchar 50 1
TRANSACTIONMESSAGE varchar 255 1
TRANSACTIONCONTENTS varchar 4000 1
AUTH_ORDERNUM decimal 9 1
CUSTID decimal 9 1
PickedBy int 4 1
segment_number int 4 1
lastorder datetime 8 1
CustomerStatusType varchar 100 1
CUSTSTATUS varchar 60 1
CUST_TOTAL_ORDERS decimal 9 1
KIDSFUTURES varchar 50 1
LONGOS_DISCOUNT varchar 50 1
TOMAX_INSERT_RJT bit 1 1
TOMAX_CLOSE_RJT bit 1 1
TOMAX_FTP bit 1 1
TOMAX_TIE bit 1 1

orderdetails table
GENNUMBER decimal 17 1
ORDERKEY varchar 10 0
ORDERLINENUMBER varchar 5 0
ORDERDETAILSYSID decimal 17 1
EXTERNORDERKEY varchar 20 0
EXTERNLINENO varchar 10 0
SKU varchar 20 0
TARIFFKEY varchar 10 1
STORERKEY varchar 15 0
MANUFACTURERSKU varchar 20 0
RETAILSKU varchar 20 0
ALTSKU varchar 20 0
ORIGINALQTY decimal 17 0
OPENQTY decimal 17 0
SHIPPEDQTY decimal 17 0
ADJUSTEDQTY decimal 17 0
QTYPREALLOCATED decimal 17 0
QTYALLOCATED decimal 17 0
QTYPICKED decimal 17 0
UOM varchar 10 0
PACKKEY varchar 50 0
PICKCODE varchar 10 0
CARTONGROUP varchar 10 0
LOT varchar 10 0
ID varchar 18 0
FACILITY varchar 5 0
STATUS varchar 10 0
UNITPRICE decimal 17 0
TAX01 decimal 17 0
TAX02 decimal 17 0
EXTENDEDPRICE decimal 17 0
UPDATESOURCE varchar 10 0
LOTTABLE01 varchar 18 0
LOTTABLE02 varchar 18 0
LOTTABLE03 varchar 18 0
LOTTABLE04 datetime 8 1
LOTTABLE05 datetime 8 1
LOTTABLE06 varchar 18 0
LOTTABLE07 varchar 18 0
LOTTABLE08 varchar 18 0
LOTTABLE09 varchar 18 0
LOTTABLE10 varchar 18 0
EFFECTIVEDATE datetime 8 0
ADDDATE datetime 8 0
ADDWHO varchar 30 0
EDITDATE datetime 8 0
EDITWHO varchar 30 0
FORTE_FLAG varchar 6 0
TRAFFICCOP varchar 1 1
ARCHIVECOP varchar 1 1
SUSR1 varchar 30 1
SUSR2 varchar 30 1
SUSR3 varchar 30 1
SUSR4 varchar 30 1
SUSR5 varchar 30 1
NOTES varchar 2000 1
WORKORDERKEY varchar 10 1
EXTERNSUBLINENO varchar 10 1
DESCR varchar 10 1
PACKAGEFLAG varchar 1 1
PACKAGETYPE varchar 10 1
CATCHWEIGHT decimal 9 1
SUBSTITUTIONFLAG varchar 1 1
PROMOTIONKEY varchar 10 1
CUTCODE varchar 10 1
SUBSTITUTEDLINENO decimal 17 1
PACKCOMPLETEDDATE datetime 8 1
PRICE decimal 9 1
CATCHPRICE decimal 9 1
[ZONE] varchar 10 1
MIRROREDZONE decimal 5 0
GDES varchar 70 1
PKGSH varchar 25 1
RW int 4 1
TOMAXUPC varchar 20 1


guru vb.net

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-05-12 : 19:22:21
try this

create Procedure proc1 as

INSERT INTO orders2
SELECT * FROM Orders WHERE datediff(dd,ORDERDATE,getdate())>60

INSERT INTO orderdetail2
SELECT orderdetail.* FROM orderdetail
INNER JOIN orders
ON orders.ORDERKEY=orderdetail.ORDERKEY
WHERE datediff(dd,orders.ORDERDATE,getdate())>60


mk_garg
Go to Top of Page
   

- Advertisement -