Date:         Thu, 23 Oct 2003 17:26:23 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: help with Proc Sql -> Delete
Comments: To: Nina Harris <sas@MAILINATOR.COM>
Content-Type: text/plain; charset="iso-8859-1"

Nina:
SQL inserts, updates, and deletes in database management systems tend to
work very slowly relative to select statements. RDBMS check data integrity
constraints, update audit trails, modify indexes, etc. after each
insert/update/delete. Developers of database management systems envisioned
insert/update/delete transactions as happening one small set at a time.

For such a large task, you'll either have to suspend normal processing of
deletes or select the rows of data that you want to keep into a new table.
Sig

-----Original Message-----
From: Nina Harris
To: SAS-L@LISTSERV.UGA.EDU
Sent: 10/22/2003 7:55 PM
Subject: help with Proc Sql -> Delete

I have this little piece of code

trying to delete about 500,000 rows from a table

after that it should contain about 100,000 rows

server running MS SQL Server 2000
sas v9.0 is running on Windows 2003 Enterprise

code runs all day (7 hours now...) and never complains and never
completes

libname x odbc
  noprompt="driver={SQL Server};server=my_server;uid=user;pwd=user;
  database=my_database;";

proc sql; DELETE FROM x.my_table WHERE city ne 'nyc';
quit;run;