Stored procedure to copy a table with a unique name

[DataMiner]

Hi,
I'm very new to stored procedures so please excuse my ignorance.

I would like to create a stored procedure to duplicate the action performed by this MS Access code snippet, which just creates a backup copy of the DemandHistory table before I am about to refresh it.

db.execute "SELECT DemandHistory.* INTO [DemandHistory_TEMPBACKUP_" & MyUniqueValue & "] from demandhistory;"

But I can't figure out how to feed the "myUniqueValue " part to the stored procedure.

So far I've got:

SELECT *
INTO setupwheel.dbo.demandhistory_TEMPBACKUP_123
FROM setupwheel.dbo.demandhistory
END

But I will want the "123" part of the new table to be something different everytime, preferably something indicating date and time.

I've tried messing around with parameters in the stored procedure but either they'll only work in a where clause, or I just don't understand the syntax.

Please help!

 

[tehNellie]

You can't pass a columnname or tablename as parameter in T-SQL ie

CREATE PROCEDURE dbo.spDoesntwork
(
  @tblname varchar(50)
)
BEGIN
  SELECT * FROM @tblname
END

Will fail.

You'd need to create the SQL within the stored procedure and use sp_executesql to run it

CREATE PROCEDURE dbo.spExample
(
  @tblpart varchar(50)
)
AS
BEGIN
  DECLARE @strSQL nvarchar(1000)
  SET @strSQL = N'SELECT * INTO dbo.demandhistory_tempbackup_' + @tblpart +
  ' FROM dbo.demandhistory'

  EXEC sp_executesql @strSQL
END