July 29, 2011 at 8:22 am
I am trying to automate a process. In the code, I am creating a backup of a table before I modify it. I want to add the current date/time to the name.
SELECT item, itemdesc, quantity INTO [filename] FROM item
with [filename] = itembkup+currentdatetime = itembkup20110729092013
I have tried using this:
DECLARE @BackupDest VARCHAR(100)
SET @BackupDest = 'itembkup' +
CAST(YEAR(GETDATE()) AS VARCHAR (4)) +
REPLACE((SPACE(2 - LEN(CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR (2)))) + RTRIM(CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR (2)))),' ','0') +
REPLACE((SPACE(2 - LEN(CAST(DATEPART(DAY, GETDATE()) AS VARCHAR (2)))) + RTRIM(CAST(DATEPART(DAY, GETDATE()) AS VARCHAR (2)))),' ','0') +
REPLACE((SPACE(2 - LEN(CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR (2)))) + RTRIM(CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR (2)))),' ','0') +
REPLACE((SPACE(2 - LEN(CAST(DATEPART(MINUTE, GETDATE()) AS VARCHAR (2)))) + RTRIM(CAST(DATEPART(MINUTE, GETDATE()) AS VARCHAR (2)))),' ','0') +
REPLACE((SPACE(2 - LEN(CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR (2)))) + RTRIM(CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR (2)))),' ','0')
SELECT item, itemdesc, quantity INTO @BackupDest FROM item
but I get the error:
Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '@BackupDest'.
Does anyone have any ideas?
Thank you.
July 29, 2011 at 8:24 am
DECLARE @cmd VARCHAR(1000)
SET @cmd = 'SELECT item, itemdesc, quantity INTO ' + @BackupDest + ' FROM item'
EXEC(@cmd)
July 29, 2011 at 8:35 am
Perfect! Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply