Create Table with current date as part of the table name

Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name?

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included?

Any help would be appreciated.

Thanks,

Chris

Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @.tableName varchar(8), @.query nvarchar(1000)

set @.tableName = convert(varchar(8), getdate(),112)

select @.query = 'select name into ' + quotename(@.tableName) + ' from sys.objects'

exec (@.query)

select *
from sys.objects
where name = @.tableName

|||

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris

sql

Create Table with current date as part of the table name

Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name?

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included?

Any help would be appreciated.

Thanks,

Chris

Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @.tableName varchar(8), @.query nvarchar(1000)

set @.tableName = convert(varchar(8), getdate(),112)

select @.query = 'select name into ' + quotename(@.tableName) + ' from sys.objects'

exec (@.query)

select *
from sys.objects
where name = @.tableName

|||

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris

Create Percentage Calculated Measure... Getting the current Dimension

Hello,

I created a cube for surveys and would like to create a measure for the percentage of people (surveys) that answered Q1, Q2, Q3, etc. I have the following calculated member:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Answered Q2]

AS Case

// Test to avoid division by zero.

When IsEmpty

(

[Measures].[Surveys Count]

)

Then Null

Else ( [Q2].[Units].CurrentMember,

[Measures].[Surveys Count])

/

(

// The Root function returns the (All) value for the target dimension.

Root (), [Measures].[Surveys Count])

End ,

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

Do I have to specify the dimension in the script (like [Q2].[Units].CurrentMember) ? That means I have to create one percentage calculation member for each of the questions (dimensions). Is there a way to get the current dimension?

I hope I explain my question clearly, please let me know if I can explain further.

Thank you very much,

Sincerely,

Annie

There is no such concept as "the current dimension". Any given cell in a cube is always defined by a coordinate of all attributes of all cube dimensions. From your description, it seems like you created one dimension per survey question. Then a coordinate in your cube space would consist of members from all question dimensions. You might have multiple measure groups in the cube, in which case a cube dimension may or may not relate to a given measure group.

In any case, the CurrentMember component in the tuple expression in your example is redundant and can be safely removed.

|||

Thank you so much for the prompt reply.

As I'm fairly new to MDX. Do you mean change the script to:

-

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Answered]

AS Case

// Test to avoid division by zero.

When IsEmpty([Measures].[Surveys Count]) Then 0

Else [Measures].[Surveys Count] /(Root (),[Measures].[Surveys Count])

End,

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

-

This then gives an "#value" error.... Could you be a bit more specific?

Thank you so much,

Sincerely,

Annie

|||

The Root() function without argument returns a tuple containing members from all attribute hierarchies, including the measures hierarchy. The [Measures].[Surveys Count] component in your tuple will cause a duplicate hierarchy error.

Create Dimension Table from Fact Table!!

I have picked an exmple from this forum, to help me explain my current problem...

"I'm looking for a solution to import data from a flat file into an normalized data modell. To explain it a little simpler think about to following:

The Data Souce is a CSV-File with FirstName, LastName and Category. Sample data could be

Dirk; Bauer; sailing
Peter; Bauer; fishing
Marc; Bauer; reading

In my data modell I have defined the 2 tables "Person" and "Category":

Table "Person"
-
[PersonID] [int] IDENTITY(1,1) NOT NULL
[CategoryID] [int] NOT NULL
[FirstName] [nvarchar](50)
[LastName] [nvarchar](50)

Table "Category"
-
[CategoryID] [int] IDENTITY(1,1) NOT NULL
[CategoryName] [nvarchar](50)

Now I like to read my first row from the source and lookup a value for the CategoryID "sailing". As my data tables are empty right now, the lookup is not able to read a value for "sailing". Now I like to insert a new row in the table "Category" for the value "sailing" and receive the new "CategoryID" to insert my values in the table "Person" INCLUDING the new "CategoryID".

I think this is a normal way of reading data from a source and performing some lookups. In my "real world" scenario I have to lookup about 20 foreign keys before I'm able to insert the row read from the flat file source.

I really can't belief that this is a "special" case and I also can't belief that there is no easy and simple way to solve this with SSIS. Ok, the solution from Thomas is working but it is a very complex solution for this small problem. So, any help would be appreciated...

Thanks,
Dirk"

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74752&SiteID=1

Could someone help me creating the dimension table?

Thanks!!

It's very common to derive dimensions from source data - that is the purpose of a data warehouse after all.

What you need to do is perform the dimension lookup with your source data. For values not found (New dimensions), you can run the records down the error output into a Derived Column derivation to create your new dimension table complete with keys, then merge them back into the flow to update the dimension tables. Your methodology will determine how this is done.

|||How about seperate packages for dimensions and facts. The dimensions get built first from the source data so there will always be a lookup match when building the fact|||That will work as well. Always transform dimensions before facts.

Wes|||

wesd wrote:

That will work as well. Always transform dimensions before facts.

Wes

That's what I would do.

|||Please mark this thread as answered, the op has the information he needs. Email me if you need more information.|||

I will Try!!

Thanks!!!

Create default

Hi All,
Can someone tell me how to create a default that put the current date into a record on insert and current date + 1 year into another record!?
Cheers Wimmouse getdate() in the field where date field is used in insert.|||Originally posted by nhariharan
use getdate() in the field where date field is used in insert.

I tried it, but i keeps the null value.|||use pubs
go
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
joinyear int default datepart(yyyy,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go|||Originally posted by Enigma

use pubs
go
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
joinyear int default datepart(yyyy,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go


Thanx the getdate() works.
I use 2 columns 1 named join date and 1 named enddate ,standard users get 1 year acces to the application so when a new user register the enddate must be automatically set 1 year after the joindate,
do you know how to manage that?

Thanx already.

Cheers Wim

I|||Originally posted by Wimmo
Thanx the getdate() works.
I use 2 columns 1 named join date and 1 named enddate ,standard users get 1 year acces to the application so when a new user register the enddate must be automatically set 1 year after the joindate,
do you know how to manage that?

Thanx already.

Cheers Wim

I
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
Enddate datetime default dateadd(yy,1,getdatE())
)|||Originally posted by harshal_in
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
Enddate datetime default dateadd(yy,1,getdatE())
)

I tried this but the result seems strange:

joindate 13-2-2004 11:48:45 enddate Feb 13 200|||Originally posted by Wimmo
I tried this but the result seems strange:

joindate 13-2-2004 11:48:45 enddate Feb 13 200

create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
endate datetime default dateadd(yy,1,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go