sql server programming: Syntax for Table with Get Date? -- sql server programming

Syntax for Table with Get Date?

Steve
10/15/2004 10:39:01 PM

sql server programming: Hi,
Can someone please help me with the following
I have to create a table & it should have get date() with it. For example
Table Nmae: CustomersOct16.
I am building a DTS package. Due to large volume of data I am partitoning
the Table on weekly basis. Data load comes in each week therefore when I run
the package automatically
a partitioned table should be created with get date. Can any one please
show me with example/ Syntax how to create such table. Or is there any other
way to create automated partitoned tables. An example with syntax will be
very helpful

Thanks

Steve

RE: Syntax for Table with Get Date?

Nazeer Oasis
10/16/2004 1:23:04 AM

Steve,
Hope this pattern will work

declare @tbName as nvarchar(50)
declare @Qry as nvarchar(2000)
set @tbName = rtrim(replace('Customer' + convert(char(20),getdate(),105) ,
'-',''))
set @Qry = N'If object_id(''' + @tbName + ''') is null ' +
N' CREATE TABLE ' + @tbName + ' (yourFieldName char(5) ) '
exec sp_executesql @Qry
go

-------------------------
Nazeer
Dimensions India
-------------------------

[quoted text, click to view]

Re: Syntax for Table with Get Date?

Joe Celko
10/16/2004 7:20:55 AM

[quoted text, click to view]
example Table Name: CustomersOct16.<<

1) getdate() is the old Sybase proprietary syntax; you can now use the
Standard SQL CURRENT_TIMESTAMP.

2) This design flaw is called attribute splitting or orthogonal design
(look up Chris Date on that one) in the literature. Partitioning is
done under the covers by the system, not by ad hoc programming.

Splitting on a temporal attribute is usually a sign of a magnetic tape
file system being replaced by SQL, but having its ghost live on in the
code. The IBM convention for tape labels was "yy-ddd" and yes, we had
problems when Y2K hit.
Congratulations! you have re-discovered fifty year technology!

3) You can use CURRENT_TIMESTAMP as a DEFAULT and then build a clustered
index on it. A VIEW on the last week's work and a plan to archive stuff
at a certain age should do it.

4) ... Unless your machine is very small or your database is in the
terabyte/petabyte size range, I doubt that you are having size issues.
People with SQL Server do not run VLDB apps. But they do write bad code
and poor indexes.

--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. Sample data is also a good idea, along with clear
specifications.

*** Sent via Developersdex http://www.developersdex.com ***

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.