| Author |
Topic |
|
JuniorProgrammer
Starting Member
10 Posts |
 Posted - 2003-08-01 : 15:29:15
|
| I am trying to create a database based on the month. Each month I want to create a new database using a variable that contains the previous month and year. I keep getting an error message when I try the following:Declare @Month varchar, @fn varchar, @MonthDB varchar, @Year varcharSet @fn = REPLACE(convert(char(8),getdate(),1),'/','')Set @Month = left(@fn,2)set @Year = Right(@fn,4)if '01' = @month set @Year = (@Year - 1) set @monthdb = 'December'+@Year+''Create Database @monthdb |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-01 : 15:36:25
|
| Why would you want to do this? Why not keep all of your data in one database and then have tables based upon month and year? What you are trying to do is dynamic sql. Research that in the forums if you really want to go down this route. But I would not recommend this.Tara |
 |
|
|
JuniorProgrammer
Starting Member
10 Posts |
Posted - 2003-08-01 : 16:04:16
|
| The reason we are wanting to create separate databases each month is because there are multiple tables in this database which contain a large amount of data. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-01 : 16:07:44
|
| But what does that affect? Having large tables in one database doesn't really affect anything except backups.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-01 : 16:15:03
|
AAAAAAAAAAAAAAAAAAAAHHHHHHHHHHHHHHHHHHHHHHHHHHHHAre yoy expecting billions and billions * of rows?* Carl Sagan referenceIf not, 1 database, 1 Table, 1 month (better yet datetime) column..Tara? No keys to the kingdom? Brett8-)SELECT POST=NewId() |
|
|
|
JuniorProgrammer
Starting Member
10 Posts |
Posted - 2003-08-07 : 11:40:39
|
| How would I create a table each month with a different name through DTS? |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-08-07 : 11:52:33
|
| Junior,Don't square peg it ... this forum is full of professionals who are universally telling you that your approach is a bad idea. Constructive criticism is the best medicine to such things.Jonathan{0} |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-07 : 11:55:44
|
quote:
Originally posted by JuniorProgrammer
How would I create a table each month with a different name through DTS?
umm..If you want to create a new database each month then you could have a backup of the database in the condition you want it and do a restore. It's quite easy to do the restore from an sp and set the database name and file names.But it doesn't sound like good design. Maybe you should archive the current data instead.And try not to limit yourself by only using dts.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-07 : 12:35:37
|
| Hi Junior --Please listen to the advice given here .... a new database per month is not the way to go.Can you see that instead of:Database: "Jan"Table: Transactions (TransID, ... )Database: "feb"table: Transactions (TransID, ....)you can just have 1 database with 1 table, and in that table have a column indicating which month each transaction is from?Transactions (Month, TransID, .....)- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-07 : 13:51:54
|
| Oh, somebody give him the keys...That way we can wait for the wreck....ahhh rubbernecking along the NJ turnpike...Brett8-)SELECT POST=NewId() |
|
|
|
|
Create Database Using Variable