How to create table in SQL with current date in table name.

[gibbolster]

I'd like to make a copy of a table each time someone opens it(once a day). I'd like to name the new table tblProgress plus that days date. Ex; tblProgress112101 for today.

SELECT [progress report].* INTO ?????????

Any ideas? Thanks.

 

[rac2]

Here is a procedure to do what you describe -

DECLARE @aDayInTheLife DATETIME
DECLARE @yourTableToday VARCHAR(50)
SELECT @aDayInTheLife = getdate()
SELECT @yourTableToday = 'tblProgress' + CONVERT(CHAR(8),@aDayInTheLife,112)

EXEC("CREATE TABLE " + @yourTableToday + "(whyDoThis INT)")

Check the result for the table created today, Nov 21 by -

SELECT * FROM tblProgress20011121

I am a bit horrified that you would do such a thing as create a whole new table just to keep track of when it was opened. It just doesn't seem right. But then I don't know the gruesome details.

 

[gibbolster]

Thanks rac2, but I may have not stated the problem correctly or you may be overthinking it. Isn't there a way to SELECT a group of records from a table and INTO them into a new table that has a name made up of a constant plus the date of whenever the query was run? For example if I ran it today with a constant name of NEWFILE the name of the table would be NEWFILEtodaysdate or to be exact NEWFILE20011121. I don't even care about the formating of the date. Could be NEWFILE11/21/01. I just don't know the syntax of how to concatenate a constant with the date. btw, I'm doing this just to create multiple versions of the table with new data in them.

 

[rac2]

Yes, probably overthinking about the wrong thing. My solution is a solution you could use if you were working in T-SQL on MS SQL Server. Don't have a clue how to do this in Access.

NEWFILE11/21/01 Yikes!

Where is the Access Wizard when you need him.

 

[BoxHead]

G,

This works on a command button in Access '97.

Private Sub Command6_Click()
Dim strName As String
Dim strSQL As String
strName = "Today Is " & Date
strSQL = "SELECT  SaleAmount, Salesman, TotalSales INTO [" & strName & "] FROM tblSalesSum;"

DoCmd.RunSQL (strSQL)

End Sub

A new table named "Today Is 11/22/2001" is created.

If you run the query twice in a day it will tell you that the existing table with that name will be deleted.

If you use

DoCmd.SetWarnings False

before the RunSQL it will run without the prompts. Use

DoCmd.SetWarnings True

afterward.


HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[THoey]

Boxheads answer should work, but I still go back to Rac2's question. WHY would someone ever want to do this? I see a very LARGE MDB file in the near future. And that is the least of your problems, I don't even want to know how you are going to provide forms and reports from this data. Are you really sure you want to do this? Terry M. Hoey