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
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:
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')