I am searching for a loop query over multiple databases and insert result into existing table in one database to collect al data

Question

I am searching for a loop query over multiple databases and insert result into existing table in one database to collect al data. There are 28 existing databases at the moment but when i start the query below it says table already exists at the second database.

when this works i want to loop a much larger query then this.

I also tried executing and union all but if a new database is added it must be collected autmatically.

See example i've tried below:

--drop table if exists [hulptabellen].dbo.HIdatabases

declare @dbList table (dbName varchar(128), indx int)

insert into @dbList select dbName = dbname, row_number() over (order by dbname)
from [hulptabellen].dbo.HIdatabases

--declare variables for use in the while loop declare @index int = 1 declare @totalDBs int = (select count(*) from @dbList) declare @currentDB varchar(128) declare @cmd varchar(300) --define the command which will be used on each database. declare @cmdTemplate varchar(300) =

' use {dbName};

select * insert into [hulptabellen].dbo.cladrloc from {dbname}.dbo.cladrloc

'

--loop through each database and execute the command while @index <= @totalDBs begin set @currentDB = (select dbName from @dbList where indx = @index) set @cmd = replace(@cmdTemplate, '{dbName}', @currentDB)

execute(@cmd)

set @index += 1

end

Answer 1

Create the table outside your loop and insert into the table this way:

INSERT INTO [hulptabellen].dbo.cladrloc  (col1,col2)
SELECT col1,col2
FROM {dbname}.dbo.cladrloc

FYI: When you use the following syntax, a new table is created, so it can be executed only once.

SELECT *
INTO [hulptabellen].dbo.cladrloc 
FROM {dbname}.dbo.cladrloc