April 16, 2008 at 10:07 am
Is there a way to create a table using a stored proc where the table name to be created is passed into the stored proc as a parameter.
I'm looking to create the table from an existing table in the DB as a backup using either INSERT INTO or SELECT * INT0 newTableName from OldTableName.
Ideally, I want to use insert into as I want to use slightly different column naming in the new table.
The problem I have is getting the stored proc to accept the table name from a parameter.
Andrew
April 16, 2008 at 10:46 am
Found one way round it using SELECT (pasted below) now going to try something similar with INSERT.
ALTER PROCEDURE [dbo].[aiidb2_DRAc_Copy_DRAcCURRENTData_To_DRAc_Archive_File]
@FilenameVARCHAR(20)
AS
DECLARE @cmd AS NVARCHAR(1000)
SET @cmd = N'SELECT * INTO ' + @Filename + ' FROM DRAcCURRENTData'
BEGIN
BEGIN TRANSACTION
/* This was original attempt that fails*/
/*SELECT * INTO @Filename
FROM DRAcCURRENTData*/
EXEC sp_executesql @cmd
END
April 16, 2008 at 11:18 am
A similar concept would work for an INSERT, but because I specify the column names in the insert statement my insert statement exceeds the size limit for the @cmd variable.
Any one got any other ideas ?
April 16, 2008 at 11:40 am
Solved it.....
EXEC ('INSERT INTO [' + @Filename + '] remainder of query')
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply