sql - Insert common data from one database into another? -

i have database x , database y. x , y have tables , columns of same schema. there no data in database y.

what sql/t-sql can write in order transfer data database x database y tables , column names same?

thank you

edit: both databases on same server. not know tables , columns of same name, cant insert each table manually (e.g. there may 100s of tables , columns same name)

after receiving additional information question turned bit interesting tried come query should the task of comparing sys.tables , sys.columns between 2 databases , create insert/select script.

test setup:

use x create table t1 (co1 int, col2 varchar(10)) create table t2 (co1 int, col2 varchar(10)) create table t3 (co1 int, col2 varchar(10)) create table t4 (co1 int, col2 varchar(10)) create table t5 (co1 int, col2 varchar(10)) create table t6 (co1 int, col2 varchar(10)) create table t7 (co1 int, col2 varchar(10)) create table t8 (co1 int identity(1,1), col2 varchar(10))  use y create table t1 (co1 int, col2 varchar(10)) create table t2 (co1 int, col2 varchar(10)) create table t3 (co1 varchar(10), col2 varchar(10)) create table t4 (co11 int, col22 varchar(10)) create table t5 (co11 int, col2 varchar(10)) create table t6 (co1 int, col2 varchar(10), col3 int) create table t7 (co1 int) create table t8 (co1 int identity(1,1), col2 varchar(10)) 

i've tried create few different scenarios should cover. table in y having additional or less columns, different datatypes, identities. there might whole more options didn't thought of, idea should ok.

also i've assumed if 2 tables same name have identical columns not all, transfer should not done @ tables. if you'd transfer tables matching columns, joins should tweaked there issue of whatever non-transfered columns in y allow null or not.

in case - tables t1, t2 , t8 copied.

query:

with cte_x  (     select  xt.object_id, xs.name + '.' + xt.name tblname, count(*) colsno x.sys.tables xt     inner join x.sys.columns xc on xc.object_id = xt.object_id     inner join x.sys.schemas xs on xt.schema_id = xs.schema_id     group xt.object_id, xt.name, xs.name ) ,cte_y  (     select  yt.object_id, ys.name + '.' + yt.name tblname, count(*) colsno y.sys.tables yt     inner join y.sys.columns yc on yc.object_id = yt.object_id     inner join y.sys.schemas ys on yt.schema_id = ys.schema_id      group yt.object_id, yt.name, ys.name ) ,cte_xy  (     select xt.object_id, xt.tblname, count(*) colsno cte_x xt     inner join x.sys.columns xc on xc.object_id = xt.object_id     inner join cte_y yt on xt.tblname = yt.tblname , xt.colsno = yt.colsno      inner join y.sys.columns yc on yc.object_id = yt.object_id , xc.name = yc.name , xc.user_type_id = yc.user_type_id , xc.precision = yc.precision , xc.scale = yc.scale     group xt.object_id, xt.tblname  ) ,cte_tables (     select xy.object_id, xy.tblname     cte_xy xy     inner join cte_x x on xy.colsno = x.colsno , xy.tblname = x.tblname  ) ,cte_columns  (     select c.object_id, c.name, c.is_identity cte_tables t     inner join y.sys.columns c on t.object_id = c.object_id ) ,cte_colconc  (     select  object_id,              stuff((select ', ' +  name                       cte_columns c2                      c2.object_id = c1.object_id                     xml path('')), 1, 2, '')  cols,             max(cast(c1.is_identity int)) hasidentity       cte_columns c1      group  c1.object_id ) select        case when hasidentity = 1 'set identity_insert y.' + tblname + ' on; ' else '' end      + 'insert y.' + tblname + ' (' + cols + ') select '+ cols + ' x.'  + tblname + ';'      + case when hasidentity = 1 'set identity_insert y.' + tblname + ' off;' else '' end cte_tables t  inner join cte_colconc c on c.object_id = t.object_id 

result of query script insert/select statements. can copy new query window , double check before running. if need automated process - results #temp table @ end , run sp_executesql row-by-row.