Date: Thu, 12 Aug 2010 16:51:59 -0600
Reply-To: JD <jdiebal@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: JD <jdiebal@GMAIL.COM>
Subject: Re: Renaming SQL Tables using proc sql
In-Reply-To: <16FD64291482A34F995D2AF14A5C932C09457E2B@MAIL002.prod.ds.russell.com>
Content-Type: text/plain; charset=ISO-8859-1
Thank you. I tried the sp_rename and it seems to just what I need on the
sample data. I'll try it in 'real life' when I get back next week.
Thanks again.
On Thu, Aug 12, 2010 at 4:15 PM, Terjeson, Mark <Mterjeson@russell.com>wrote:
> Hi JD,
>
> Essentially, you are looking for the rename commands
> that are available on the platform environment you are
> working on. e.g. SQLServer, Oracle, DB2, etc., they will
> all have their own utility calls for doing administration
> type tasks like that. So while not built into SQL itself,
> you can use the same pass-thru capabilities in SAS to
> execute these platform-dependent tasks.
>
> There are a couple of glitches that you can run up
> against such as file-locking issues and transactional
> buffering overlap, etc.
>
> As many people call it, "flip-n-copy", when
> technically it is more copy-n-flip or build-n-flip
> as far as order of occurance. (never have figured
> out how it got to be called "flip-n-copy")
>
> If indeed you are targeting a SQL Server platform
> you could invoke the builtin sp_rename with a SAS
> EXECUTE() by sqlsvr. [for those that don't know,
> the
> from connection to sqlsvr
> (
> )
> found in a SAS PROC SQL allows a pass-thru query
> to be sent over to the SQL Server box and run there.
> The FROM CONNECTION TO SQLSVR expects a resultant
> data table to be sent back. Conversely, if you do
> not need/expect any data back, you can use, in your
> PROC SQL, the EXECUTE() BY SQLSVR instead, to pass
> a command over to the SQL Server box to execute
> while not expecting any data returned.] The SQLSVR
> is just a token and may me spelled different for you.
> Just check your connection string at the beginning
> of the PROC SQL and you just have to match tokens.
>
>
> To just rename, you could do in your PROC SQL where you
> have the connection string initiated:
>
> EXECUTE(exec sp_rename 'My_Datamart.dbo.tmpMyTable','MyTable') BY
> SQLSVR
>
>
>
> This may be all you need. But if your multi-user,
> multi-process environment needs to sync any 'writes'
> or file-locking situations, you can build a stored
> procedure in your SQL Server, such as the one below,
> and then just exec your stored procedure with the
> SAS EXECUTE() or in Windows Scheduler or at the command
> prompt.
>
> Note: the first time you create your stored procedure
> you will need to change the word ALTER to CREATE. Some
> of the Microsoft tools will change the word CREATE to
> ALTER once built. If you modify the procedure and it
> already exists you can just leave it as ALTER.
>
>
> The code for such as stored procedure to do a flip-n-copy
> is found below and includes the coding to alleviate the
> glitches. This approach also maintains data structure
> elements and permissions so no other manual follow-up is
> required.
>
>
> The SAS-L list server is going to wrap these lines.
> If anyone has trouble getting the line wraps untangled
> and wants an attachment copy intact just email me and
> I will be glad to send you a copy.
>
>
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
>
>
> /***********************************************************************
> ***
> *
> *
> * Object Name: sp_BuildMyTable
> *
> *
> *
> * Programmer : Mark Terjeson
> *
> *
> *
> * Date : 09/19/2008
> *
> *
> *
> * Purpose : Build MyTable as desired.
> *
> * Utilizes a flip-n-copy to minimize downtime.
> *
> *
> *
> ************************************************************************
> ***
> *
> *
> * Scheduling : To schedule via a Windows Scheduler or at a DOS prompt
> *
> * use the following command line:
> *
> *
> *
> * OSQL -S SERVERnameHERE -E -d My_Datamart
> *
> * -Q "exec My_Datamart.dbo.sp_BuildMyTable"
> *
> *
> *
> ************************************************************************
> ***
> *
> *
> * Maintenance: 01/29/2010 Mark Terjeson
> *
> * Add set transaction isolation level to
> *
> * read uncommitted. i.e. r/o
> *
> *
> *
> * __/__/____ <your name here>
> *
> * <description>
> *
> *
> *
> ************************************************************************
> **/
>
>
> ALTER procedure dbo.sp_BuildMyTable
> as
> set nocount on
>
> --do this so we don't have to contend with existing locks
> set transaction isolation level read uncommitted
> --Specifies that statements can read rows that have been
> --modified by other transactions but not yet committed.
>
> --build my new table here
> select
> *,
> getdate() as LastUpdate
> into #tmpacp
> from My_Datamart.dbo.TableC
>
>
> if object_id('My_Datamart.dbo.tmpMyTable') is not null
> drop table My_Datamart.dbo.tmpMyTable;
>
> select *
> into My_Datamart.dbo.tmpMyTable
> from #tmpacp
>
> drop table #tmpacp
>
> --flip-n-copy (keeps table active during the time it takes
> to build it)
> if object_id('My_Datamart.dbo.oldMyTable') is not null
> drop table My_Datamart.dbo.oldMyTable
>
> if object_id('My_Datamart.dbo.MyTable') is not null
> exec sp_rename 'My_Datamart.dbo.MyTable','oldMyTable'
>
> if object_id('My_Datamart.dbo.tmpMyTable') is not null
> and object_id('My_Datamart.dbo.MyTable') is null
> exec sp_rename 'My_Datamart.dbo.tmpMyTable','MyTable'
>
> if object_id('My_Datamart.dbo.oldMyTable') is not null
> and object_id('My_Datamart.dbo.MyTable') is not null
> drop table My_Datamart.dbo.oldMyTable
>
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
>
>
>
>
>
>
> Hope this is helpful.
>
>
> Mark Terjeson
> Investment Business Intelligence
> Investment Management & Research
> Russell Investments
> 253-439-2367
>
>
> Russell
> Global Leaders in Multi-Manager Investing
>
>
>
>
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of JD
> Sent: Thursday, August 12, 2010 2:30 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Renaming SQL Tables using proc sql
>
> Hi. Is there a way to rename tables in an SQL database?
>
> I need to write out some large tables as temp tables so it won't impact
> the
> users while the data is being written. Then I'd like to drop the
> original
> and rename the temp to the original tables name.
>
> This is the way I'm creating indexes for what it's worth.
>
> proc sql;
> connect to odbc(dsn=&datamart);
> execute(CREATE NONCLUSTERED INDEX indexTest1
> ON APP.xxx (shift, level1, date)) by odbc;
> disconnect from odbc; quit;
>
> Thanks in advance.
>
| 
