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.


Comments

Post a Comment

Popular posts from this blog

jquery - How can I dynamically add a browser tab? -

since using iframes house external pages are, accounts "not preferred method" , disallowed sites (flickr, one), want dynamically generate browser tabs. e.g., when user submits form, want dynamically generate tab on browser in response submitted. e.g., might want add new tab url like: http://www.bigsurgarrapata.com/contact how in jquery? you want form target attribute action attribute. http://www.w3schools.com/tags/att_form_target.asp browsers have ultimate control on result of target attribute open new tab value of _blank. <form action="contact_submit.php" target="_blank" method="post">
Read more

keyboard - C++ GetAsyncKeyState alternative -

i using windows.h library's getasynckeystate function define key pushed on keyboard . here program returns int value of pushed key example. #include <iostream> #include <windows.h> using namespace std; int main (){ char i; for(i=8;i<190;i++){ if(getasynckeystate(i)== -32767){ cout<<int (i)<<endl; } } return 0; } but trying make opengl simple game , function appeared slow . there function not need cycle 180 times , if statement . thanks you're trying make opengl game, you're going use glfw. if that's case can away using glfwgetkey() such: if (glfwgetkey(glfw_key_up) == glfw_press) {...} if (glfwgetkey(glfw_key_down) == glfw_press) {...} that method nicely explained in tutorial 6 opengl-tutorial.org . should start first tutorial of beginners tutorials , later intermediate tutorials . if you're going make win32 window can listen wm_keydown or wm_keyup messages , virtua...
Read more

android - java.net.UnknownHostException(Unable to resolve host “URL”: No address associated with hostname) -

i developing app testing asp.net webservice in android.for simple webservice adding 2 numbers used.where numbers passed parameters.the result in dialogue.the webserver works in local. when application runs following result exception.i provided internet permission in manifest. java.net.unknownhostexception(unable resolve host “ http://url.com/ ”: no address associated hostname) my code given below main activity public static string rslt=""; /** called when activity first created. */ @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.activity_main); button b1=(button)findviewbyid(r.id.button1); final alertdialog ad=new alertdialog.builder(this).create(); b1.setonclicklistener(new onclicklistener() { @override public void onclick(view arg0) { // todo auto-generated method stub try { ...
Read more