RE: Automatically naming a table with a date calculation 1

[allyne]

Hi everyone,

I have a Make table query that looks like this:

Select *
INTO tblFinalOneLine113001
FROM tblFinalOneLine

This is part of a very large stored procedure. Currently I manually type in the date as part of the INTO clause to create a backup copy of the Month before. What I would like to do is have the date automatically entered as part of the INTO clause so that it looks something like this:

Select *
INTO tblFinalOneLine GETDATE()-30
From tblFinalOneLine

Is it possible to have a calculation as part of the INTO clause? If so can you give me the correct syntax. Can't seem to figure this one out.

Thanks for all your help in advance.

Cathy

 

[rac2]

Hello Cathy,

Use the exec("build a statement with" + @variables) approach. This enables you to build a query with some constant and some variable pieces as you might do in a script, then execute the result. See thread701-168020.

Richard

 

[allyne]

Thanks for your response. Can you show me how the code relates to
Select *
INTO tblFinalOneLine GETDATE()-30
From tblFinalOneLine

Unfortunally I only know only the basics of SQL and am lost in the code. Will this code create a table with all the fields and information from tblFinalOneLine? Thanks in advance for your help in helping me understand the code.

Cathy

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

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

 

[rac2]

These three statements will create a new table named
tblFinalOneLine20010103 with all of the data copied from the original table named tblFinalOneLine.

DECLARE @theDateOfBackup CHAR(8)
SELECT @theDateOfBackup = CONVERT(CHAR(8), getdate(), 112)

EXEC ("Select * INTO tblFinalOneLine" +  @theDateOfBackup + " From tblFinalOneLine" )

The database must have the SELECT INTO/BULKCOPY option turned on in order to use the SELECT INTO command. You can check this with the system stored procedure sp_helpdb.

For example if the database name is ConceptTest use Query Analyzer and run the following

sp_helpdb 'ConceptTest'

Look at the status column. If it has an item "select into/bulkcopy" then you can use SELECT INTO.

You can engage this option with the following command. You must be the dbo to do this. It might be better to ask your DBA to do it, since it affects a lot of things and they might not want to allow this option.

sp_dboption 'ConceptTest', 'select into', TRUE


If you cannot use SELECT INTO, then use CREATE TABLE and INSERT

EXEC("CREATE TABLE tblFinalOneLine" +  @theDateOfBackup + " (column definitions and table constraints go here)")

EXEC("INSERT INTO tblFinalOneLine" +  @theDateOfBackup + " SELECT * FROM tblFinalOneLine" )

Another option is to create one table for backups with an extra column for the date a row was added. If you ever need to get data for a particular month, use the extra column to select the rows added for the backup that month.

CREATE TABLE tblFinalOneLineBackUp (column definitions and table constraints go here)

ALTER TABLE ADD date_of_backup DATETIME DEFAULT getdate()

Then use INSERT for the backup. The TRUNCATE command deletes all rows from the original table and recovers disk space for re-use.

INSERT INTO tblFinalOneLineBackUp
SELECT * FROM tblFinalOneLine

TRUNCATE TABLE tblFinalOneLine

 

[allyne]

Thanks so much for all your help and explanation!