Add Date Stamp to File created using 'INTO' Statement

Question

Post reply

Add Date Stamp to File created using 'INTO' Statement

OscarBoots

Ten Centuries

Points: 1258
More actions
March 17, 2016 at 8:22 pm

#317074

Hi Forum,
I'm creating a file each day that is created as follows;
[/Select a,b,c, INTO dbo.MyResults from Table1 quote]
I would like to keep each days file so I need a new filename that adds the days date each day.
ie;
MyResults_20160315,
MyResults_20160316,
MyResults_20160317 etc.
Can anyone advise how the syntax goes? I've tried everything I can think of.
If this is not possible, is there another way of achieving what I need to do?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

pbo71465 (3/17/2016)

Hi Forum,

I'm creating a file each day that is created as follows;

[/Select a,b,c, INTO dbo.MyResults from Table1 quote]
I would like to keep each days file so I need a new filename that adds the days date each day.
ie;
MyResults_20160315,
MyResults_20160316,
MyResults_20160317 etc.
Can anyone advise how the syntax goes? I've tried everything I can think of.
If this is not possible, is there another way of achieving what I need to do?
Thanks

Quick suggestion, us sp_rename after loading the data, much cleaner approach than dynamic sql.

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @MYDATE DATE = GETDATE();

DECLARE @TABLE_NAME NVARCHAR(128) = N'MyResults_' + CONVERT(NVARCHAR(25),@MYDATE,112);

SELECT

TOP(10)

SM.language_id

,SM.text

INTO dbo.TEMP_MSG_TABLE

FROM sys.messages SM;

EXEC sp_rename N'dbo.TEMP_MSG_TABLE',@TABLE_NAME,OBJECT;

SELECT @TABLE_NAME;

OscarBoots Ten Centuries Points: 1258 More actions · Answer 2

Hi Eirikur,

Thanks!

This works well & creates a table as I wanted.

I wonder if you can advise how to get the date to be a part of the tablename for the table it creates?

The below has your code with my real tables & fields & runs ok until the end when I get the below error, can you suggest what mat be wrong?

As I said the results the query gives are correct but the error appears every time?

Cheers

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

USE reporting;
GO
SET NOCOUNT ON;
DECLARE @MYDATE DATE = GETDATE();
DECLARE @TABLE_NAME NVARCHAR(128) = N'A_REDATE_TEST_' + CONVERT(NVARCHAR(25),@MYDATE,112);
SELECT
TOP(10)
SM.email
,SM.service_id
INTO dbo.A_TEMP_MSG_TABLE
FROM dbo.customer SM;
EXEC sp_rename N'dbo.TEMP_MSG_TABLE',@TABLE_NAME,OBJECT;
SELECT @TABLE_NAME;

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

The error message tells you where to look - it's in the sp_rename stored procedure. My guess is that "OBJECT" needs to go in (single) quotes.

John

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Here is a more complete example with some error handling and listing of the tables with the date extracted from the name.

😎

USE tempdb;

GO

SET NOCOUNT ON;

--http://www.sqlservercentral.com/Forums/FindPost1770476.aspx

-------------------------------

-- DATE SATAMP THE TABLE

-------------------------------

DECLARE @TABLE_NAME NVARCHAR(128) = N'A_REDATE_TEST_' + CONVERT(NVARCHAR(25),GETDATE(),112);

IF OBJECT_ID(N'dbo.TEMP_MSG_TABLE') IS NOT NULL DROP TABLE dbo.TEMP_MSG_TABLE;

BEGIN TRY

SELECT

TOP(10)

SM.language_id

,SM.text

INTO dbo.TEMP_MSG_TABLE

FROM sys.messages SM;

EXEC sp_rename N'dbo.TEMP_MSG_TABLE',@TABLE_NAME,OBJECT;

SELECT @TABLE_NAME;

END TRY

BEGIN CATCH

SELECT ERROR_MESSAGE();

IF OBJECT_ID(N'dbo.TEMP_MSG_TABLE') IS NOT NULL DROP TABLE dbo.TEMP_MSG_TABLE;

END CATCH

----------------------

-- RETRIEVE THE DATES

----------------------

SELECT

ST.name

,CONVERT(DATE,RIGHT(ST.name,8),112) AS TDATE

,ST.create_date

FROM sys.tables ST

WHERE ST.name LIKE N'A_REDATE_TEST_%'

----------------------

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Just a word of advice.

This will become annoying in the future, you'll end with hundreds of tables all with the same structure. You'd be better by creating a table with a date column to identify each "file". Querying from there shouldn't be that hard and will prevent you lots of dynamic sql coding.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

OscarBoots Ten Centuries Points: 1258 More actions · Answer 6

Thanks again Eirikur, this is perfect!

Also appreciate the advice from John & Luis.

Peter

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Luis Cazares (3/18/2016)
You'd be better by creating a table with a date column to identify each "file"

- and a clustered index with this column in the first position.

_____________
Code for TallyGenerator