script to create and populate multiple tables from one data table

[wbodger]

So, I have a table with fields protoid and dbname. I now want to create a table called foo with one field called foobar inside of every database (dbname). I then want to populate that field with the matching protoid. I have started with this, but feel like I have taken a wrong turn somewhere. Help?

DECLARE @protoid int
DECLARE @dbname varchar(20)
DECLARE @count int

;with temp as (
select ROW_NUMBER() over(partition by proto_id order by proto_id) as rownumber, proto_id, dbname
from [dbDEMAPPING].[dbo].[tblDataConnect]
)
SELECT distinct @protoid=PROTO_ID
      ,@dbname=DBNAME
  FROM temp where rownumber = 1

wb

 

[simian336]

Is this a one off or something you need to repeat?

Simi

 

[wbodger]

Sorry this is so late, I was gone for a few days. This is something that I will need to repeat.

Thanks,
wb

 

[SkipVought]

What is the purpose of producing multiple tables?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wbodger]

Ahh... Getting my mind back into work mode now. This is a one time deal. I have three db servers (prod, test and dev), each with 30 or so dbs. What I want to do is add a new table with one column and one row to each db. That data is pulled from a table in a separate db that has the db names and the protocol ids. So, it has

dbname protocolid
db123 123
db456 789
dbLGH 324
etc.

From this I want to create the table Foo with the column ProtocolID in each dbname. I will then populate that column with the matching protocolid.

wb

 

[wbodger]

I guess I did not answer the purpose question, sorry. The purpose of creating and populating this table in each database is to aid in the use of common code across different studies. This table allows me to self identify each study within itself.