Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Exclamation Unanswered: Copying data from another DB on server problem

    What iam trying to do is put the table name from one db into a variable and another one into another variable and pass them into my statement basicaly trying to bulk copy data from one table in a db and insert it into another db on the same server based on a condition

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]')
    and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    Declare @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20)
    set @BDFR = 'Commander' + '.dbo.' + 'ClientComment'
    set @BDTO = 'Test_Commander' + '.dbo.' + 'ClientComment'
    set @EQID = '80_300_005'
    insert into @BDTO
    select * from @BDFR where Eqid = @EQID

  2. #2
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Plz help

    plz help .........

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to use dynamic SQL to do what you're envisioning.

    Beware that dynamic SQL breaks most of the commonly accepted rules about security/permissions/etc because of the way that it works... The dynamic SQL is permission checked based on the user that is running the SQL.

    With that said, I'd use something like:
    Code:
    DECLARE @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20)
    
    SET @BDFR = 'Commander' + '.dbo.' + 'ClientComment'
    SET @BDTO = 'Test_Commander' + '.dbo.' + 'ClientComment'
    SET @EQID = '80_300_005'
    
    EXECUTE ('insert into ' + @BDTO 
    + ' select * from ' + @BDFR + ' where Eqid = @EQID')
    -PatP

Posting Permissions

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