Insert values from tables on multiple databases

[svagelis]

The objective is to :
insert values into a table from tables that exists in different databases within SQL SERVER 7.

In order to browse to different databases im using EXEC (@strexeceute) . But that doesnt works besause i need to do this

insert into tblMaster.Mydatabase select * from tbl1.db1

can anyone help

i m posting what ive done so far
------------------------------------------------------
DECLARE @COUNTER INT
DECLARE @CURR_DB AS varchar(20)
DECLARE @stringToExecute AS VARCHAR(500)

SET DATEFORMAT mdy

SELECT @COUNTER = 1

WHILE (@COUNTER <12)
BEGIN
IF @COUNTER=1 SELECT @CURR_DB = 'pre_live'
IF @COUNTER=2 SELECT @CURR_DB = 'peiraias_live'

select @STRCOUNTER = cast(@counter as varchar(2))
Select @stringToExecute= 'USE '+ @CURR_DB + ' '

Select @stringToExecute= @stringToExecute + 'INSERT INTO MASTER '

Select @stringToExecute= @stringToExecute + 'SELECT ' + cast(@counter as varchar(2)) + ' ,MIN(REGION) as REGION,cast(ITEMCODE as INT) AS ITEMCODE, '
Select @stringToExecute= @stringToExecute + 'SUM(WEIGHT) AS WEIGHT,SUM(BOXESNO) AS BOXESNO ,month(vdate ) AS MONTH '
Select @stringToExecute= @stringToExecute + 'FROM EXTVD '
Select @stringToExecute= @stringToExecute + 'INNER JOIN '
Select @stringToExecute= @stringToExecute + ' EXTVM ON EXTVD.VID = EXTVM.VID '
Select @stringToExecute= @stringToExecute + 'GROUP BY cast(ITEMCODE as INT), month(vdate ) '
Select @stringToExecute= @stringToExecute + 'ORDER BY cast(ITEMCODE as INT) , month(vdate) '

EXEC (@stringToExecute)
SELECT @COUNTER = @COUNTER + 1
END

 

[TomasDill]

Hmm, whats wrong with syntax like
&quot;SELECT A.*, B.* from Database1..Table1 B left join Database2..TableN B on A.[ID] = B.[ID] where ...&quot; ?
Above does a pure query on 2 tables, each in different database.
Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs