locked
How to add parameter to the name of the sql table when copying it RRS feed

  • Question

  • User2074777333 posted

    Hello,

    I want to copy the existing sql table into new one using the number of the version. I have this code, but it doesn't add the number as a parameter which was entered by the code. It adds only @i to the table name. Can anybody help me please how to add it as the parameter passed from the asp page?

    CREATE PROCEDURE Saveas  

    @i nvarchar

    AS

     Select *

    Into   TrafInc1_version_@i

    From   TrafInc1

    Thanks

    Saturday, September 13, 2014 3:45 PM

Answers

  • User-1806150748 posted

    please remove brackets from sp_executesql. So modified one will be.

    CREATE PROCEDURE Saveas  
    
    @i nvarchar
    
    AS
    
    begin
    declare @sql nvarchar(100)
    
    set @sql = 'Select * Into   TrafInc1_version_'+ @i + ' From   TrafInc1' -- assuming you have TrafInc1 as actual table
    
    EXEC SP_EXECUTESQL @sql
    end

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 14, 2014 2:23 PM

All replies

  • User-1806150748 posted

    You need to use dynamic SQL to achieve this.

    CREATE PROCEDURE Saveas  
    
    @i nvarchar
    
    AS
    
    begin
    declare @sql nvarchar(100)
    
    set @sql = 'Select * Into   TrafInc1_version_'+ @i + ' From   TrafInc1' -- assuming you have TrafInc1 as actual table
    
    EXEC SP_EXECUTESQL(@sql)
    end

    Saturday, September 13, 2014 3:51 PM
  • User-1716253493 posted

    Try this : 

    exec 'Select * Into TrafInc1_version_' + @i + ' From TrafInc1'

    Saturday, September 13, 2014 4:50 PM
  • User2074777333 posted

    Hi,

    I tried both ways.

    If I use the first option I get an error:

    Incorrect syntax near '@sql'.

    and if I use oned_gk's option like this

    CREATE PROCEDURE Saveas  
    
    @i nvarchar
    
    AS
    
    begin
    exec 'Select *
     Into TrafInc1_version_' + @i + '
     From TrafInc1' 
    end

    I get an error

    Incorrect syntax near 'Select *
     Into TrafInc1_version_'.

    Sunday, September 14, 2014 11:36 AM
  • User-1806150748 posted

    please remove brackets from sp_executesql. So modified one will be.

    CREATE PROCEDURE Saveas  
    
    @i nvarchar
    
    AS
    
    begin
    declare @sql nvarchar(100)
    
    set @sql = 'Select * Into   TrafInc1_version_'+ @i + ' From   TrafInc1' -- assuming you have TrafInc1 as actual table
    
    EXEC SP_EXECUTESQL @sql
    end

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 14, 2014 2:23 PM
  • User61956409 posted

    Hi nat,

    As for your problem, you could refer to the following code to solve it.

    CREATE PROCEDURE Saveas 
    @tab nvarchar(50)
    AS
    
    begin
    declare @sql nvarchar(100)
    set @sql = 'Select * Into ChartInfo'+ @tab  +' From ChartInfo'
    
    EXEC SP_EXECUTESQL @sql
    
    end

    Hope it will be helpful to you.

    Best Regards,

    Fei Han

    Monday, September 15, 2014 4:07 AM
  • User2074777333 posted

    Thanks a lot Arindamnayak. Thank you to all of you for your try

    Monday, September 15, 2014 9:00 AM