Select from one database table where column value equals a column value in a different database
-
I am trying to pull all data from SQL Database A, Table 1 where column name is X, and the value in column X equals the value in Database B, Table 2, column name Y. So I have 2 databases on the same server. One named A, the other named B. I need to get all the data from A, Table1, where the values of Column Name X are equal to the values in in Column Name Y in B, Table2, and insert/write them to a new table in Database B. Any and all help is appreciated. My SQL writing skills are very sub par. If the title, and the subject differ, I apologize. I am just trying to put the thought thru my head as I write.
-
I am trying to pull all data from SQL Database A, Table 1 where column name is X, and the value in column X equals the value in Database B, Table 2, column name Y. So I have 2 databases on the same server. One named A, the other named B. I need to get all the data from A, Table1, where the values of Column Name X are equal to the values in in Column Name Y in B, Table2, and insert/write them to a new table in Database B. Any and all help is appreciated. My SQL writing skills are very sub par. If the title, and the subject differ, I apologize. I am just trying to put the thought thru my head as I write.
This is the general gist of where I think you need to be heading:
insert into B.NewTable
select a1.*
from A.Table1 a1
join B.Table2 b2
on a1.X = b2.Y“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
This is the general gist of where I think you need to be heading:
insert into B.NewTable
select a1.*
from A.Table1 a1
join B.Table2 b2
on a1.X = b2.Y“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
a1 and b2 are aliases for tables.
moordoom wrote:
what is X and Y?
They are the columns you mentioned in your question. I think you need to read a book on sql or get some basic training, since if you cannot make sense of my answer you need to start reading.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I am trying to pull all data from SQL Database A, Table 1 where column name is X, and the value in column X equals the value in Database B, Table 2, column name Y. So I have 2 databases on the same server. One named A, the other named B. I need to get all the data from A, Table1, where the values of Column Name X are equal to the values in in Column Name Y in B, Table2, and insert/write them to a new table in Database B. Any and all help is appreciated. My SQL writing skills are very sub par. If the title, and the subject differ, I apologize. I am just trying to put the thought thru my head as I write.
you can solve your problem with the help of SQL join(Inner Join).Sql Join on W3school
-
you can solve your problem with the help of SQL join(Inner Join).Sql Join on W3school
Looking at the W3 school, it shows table to table joins, not database to database joins. Here is what I have so far... (minus the insert, and it works) SELECT * from Table1 join Database2.Table2 on ColumnX = ColumnY X is a Column in Database1.Table1, and Y is a Column in Database2.Table2. This gets me my info when I run the query on Database1. But now I want write these info into Database2.Table3 and delete what was in there before. So would my statement then be...?? DELETE * FROM Database2.Table3 INSERT INTO Database2.Table3 SELECT * from Table1 join Database2.Table2 on ColumnX = ColumnY