Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Unanswered: inserting into temp table in dynamic sql

    I have a temp table in my stored procedure when I try to insert into the temp table thro dynamic sql, it says that table has to be defined. What could be the problem. i have added the code below

    CREATE PROCEDURE USP_RULE
    AS
    declare @TABLE1 table
    (
    SlNo int identity(1,1), EqNum varchar(25),Pointnum varchar(25)
    )
    declare @EqNum varchar(25),@Pointnum varchar(25)
    DECLARE @STRDBNAME VARCHAR(50)
    SET @STRDBNAME = 'DB1'
    EXEC('insert into '+@TABLE1+' select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')

    GO

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: inserting into temp table in dynamic sql

    where do you give the value for @Table1 ???

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you want to pass the name of the table in?

    Anyway, why are you doing this? You've clearly identified that the column names must be statis...are yoiu telling me you a hundred different tables with different names and the same columns?

    Not to mention the SELECT, which is coming from the same table

    Describe what business rules you are trying to enforce, and we'll come up with a solution.

    Dynamic SQL should be an application last resort...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Use temporary table instead:

    create table #table(id int)
    insert #table
    exec('select 1')
    select * from #table

  5. #5
    Join Date
    Jan 2003
    Posts
    41

    Re: inserting into temp table in dynamic sql

    Thanks Snail

    It worked out

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •