CODEWITHSUNDEEP
sql-serversql-server-2005
Varinder
Varinder

Reputation: 1910

Want to insert values from one table to another table but from two different databases

My problem is the following

insert into TargetDatabase.dbo.tblContact

Select * from SourceDatabase.dbo.tblContact

As shown above. I want to insert data of same table into same table but database is different

I tried the following

Create Procedure Demo
@SourceDatabase as  nvarchar(100),
@TargetDatabase as  nvarchar(100)
as 
exec ( 'insert into' +@TargetDatabase+'.dbo.tblContact')
exec('select * from ' +@SourceDatabase+'.dbo.tblContact')

In this code Select Query is working Fine

but while inserting it is throwing error 'Incorrect syntax near tbl Contact.'

Upvotes: 0

Views: 429

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

Just a slightly different approach. I prefer sp_executesql over EXEC (some background here) and I find REPLACE a little cleaner than classic concatenation, especially when a variable is embedded in the script multiple times. I also typically add a @debug flag so I can optionally print the statement for sanity checking instead of executing it.

CREATE PROCEDURE dbo.Demo
    @SourceDatabase NVARCHAR(100),
    @TargetDatabase NVARCHAR(100),
    @debug BIT = 0
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);

    SET @sql = N'INSERT [$t$].dbo.tblContact SELECT * FROM [$s$].dbo.tblContact;';

    SET @sql = REPLACE(REPLACE(@sql, '$t$', @TargetDatabase), '$s$', @SourceDatabase);
    
    IF @debug = 1
        PRINT @sql;

    IF @debug = 0
        EXEC sp_executesql @sql;
END
GO

I will caution against using INSERT with no column list and SELECT * - this code is very brittle as a change to either table will result in errors, wrong data, or worse.

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103535

You're running two different exec statements.

Do it in one single one:

exec ('insert into' + @TargetDatabase + '.dbo.tblContact ' + 
      'select * from ' + @SourceDatabase+'.dbo.tblContact')

Upvotes: 3

Related Questions