Quering Multiple DB's and consolidating info

• jthuynsma

  SSC Enthusiast

  Points: 188

  More actions

  May 28, 2009 at 9:38 am

  #205417

  I have a set of database for example db1, db2, db3, db4, db5 and dbconsolidate in reality there is 35.

  so i want to query identical tables from db1 - db5 and write the results to dbconsolidate adding a colum to the row containing the dbid as a reference

  so instead of using a union for each database I would like to use either the for each loop where the dbid is located in a table in dbcons.

  With the statement in the for each loop to look something like this

  INSERT GPCONS..GPBATCHES

  SELECT *

  FROM @dbid..SY00500 (NOLOCK)

  WHERE BACHNUMB LIKE 'RMAPY%'

  OR BACHNUMB LIKE '%PYMNT%' AND BCHSOURC = 'GL_Normal'

  OR BACHNUMB LIKE 'IV%' AND BCHSOURC = 'GL_Normal'

  OR BACHNUMB LIKE 'CB%' AND BCHSOURC = 'GL_Normal'

  OR BACHNUMB LIKE '%' AND BCHSOURC = 'RM_Cash'

  OR BACHNUMB LIKE 'SLSTE%' AND BCHSOURC = 'GL_Normal'

  with the @DBID being passed from the for each container

  Is ther anybody that have had a resolution to this

  Thanks

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  May 28, 2009 at 11:57 am

  #1001624

  A couple of options:

  1) Dynamic SQL - build a sql string and execute the string.

  DECLARE @sqlCommand varchar(max);

  Set @sqlCommand = '';

  SELECT @sqlCommand = @sqlCommand + ' SELECT {columns} FROM ' + db.Name + '.dbo.MyTable;'

  FROM sys.databases db

  WHERE db.Name 'dbcons' -- or other where clause

  Print @sqlCommand;

  Execute (@sqlCommand);

  2) Use SQLCMD mode

  3) Use SSIS

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs