complicated SQL Join request from two server
-
i have Two Database in two different servers i want to join two tables from this two Databases
i have no relation between them but just a column wich contain information about the other database
Ex : Database1.dbo.informationCity contain a column named Specification and have this information:
14042020111925_GMS3439_09_04_2020-04_48.xml_6456.zipi want to extract from this column just this number 3439 because with this number i can rely the other database
Ex : Database2.dbo.city contain a column named cityID : 3439
any body have an idea how to do that ?
i tried this Sql request but it did not workSELECT
*
FROM [mo].[dbo].[Database1]
INNER JOIN [Database2].[dbo].[city ] ON substring([Specification], CHARINDEX('GMS', [Specification ]) + 3,4) =
[Database2].[dbo].[city ].[cityID] -
i have Two Database in two different servers i want to join two tables from this two Databases
i have no relation between them but just a column wich contain information about the other database
Ex : Database1.dbo.informationCity contain a column named Specification and have this information:
14042020111925_GMS3439_09_04_2020-04_48.xml_6456.zipi want to extract from this column just this number 3439 because with this number i can rely the other database
Ex : Database2.dbo.city contain a column named cityID : 3439
any body have an idea how to do that ?
i tried this Sql request but it did not workSELECT
*
FROM [mo].[dbo].[Database1]
INNER JOIN [Database2].[dbo].[city ] ON substring([Specification], CHARINDEX('GMS', [Specification ]) + 3,4) =
[Database2].[dbo].[city ].[cityID]Your join seems to be confused about the table names. According to your question, your tables are:
Database1.dbo.informationCityDatabase2.dbo.city
But according to your query, the tables are:
mo.dbo.Database1(Completely different name)Database2.dbo.[city ](Extra space at the end of the name)
There's also nothing in there that would suggest the databases are on different servers. If they are, you'd need to create a linked server on the server where this query will run pointing to the other server, and use the four part name of the table you're trying to query -
[Linked Server Name].[Database Name].[Schema Name].[Table Name]. Create Linked Servers - SQL Server | Microsoft Docs[^] If it still doesn't work once you've fixed that, you'll need to provide the full details of the error(s) you're getting.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Your join seems to be confused about the table names. According to your question, your tables are:
Database1.dbo.informationCityDatabase2.dbo.city
But according to your query, the tables are:
mo.dbo.Database1(Completely different name)Database2.dbo.[city ](Extra space at the end of the name)
There's also nothing in there that would suggest the databases are on different servers. If they are, you'd need to create a linked server on the server where this query will run pointing to the other server, and use the four part name of the table you're trying to query -
[Linked Server Name].[Database Name].[Schema Name].[Table Name]. Create Linked Servers - SQL Server | Microsoft Docs[^] If it still doesn't work once you've fixed that, you'll need to provide the full details of the error(s) you're getting.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
thank you sir for your Help , i fixed it and it works now is that possible to get automaticaly my results to an Excel file => simply export my result to excel directly
-
thank you sir for your Help , i fixed it and it works now is that possible to get automaticaly my results to an Excel file => simply export my result to excel directly
Yes. For example, from SQL Server Management Studio: Start the SQL Server Import and Export Wizard - Integration Services (SSIS) | Microsoft Docs[^] From .NET code, you could use a library such as ClosedXML[^] to create an Excel sheet containing the query results. (Previously, I would have recommended EPPlus, but that's no longer free for commercial use.) Or you could write the results out to a CSV file, which Excel will be able to open. I like to use the CsvHelper[^] for reading and writing CSV files, but other libraries are available (eg: fastCSV[^]).
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Yes. For example, from SQL Server Management Studio: Start the SQL Server Import and Export Wizard - Integration Services (SSIS) | Microsoft Docs[^] From .NET code, you could use a library such as ClosedXML[^] to create an Excel sheet containing the query results. (Previously, I would have recommended EPPlus, but that's no longer free for commercial use.) Or you could write the results out to a CSV file, which Excel will be able to open. I like to use the CsvHelper[^] for reading and writing CSV files, but other libraries are available (eg: fastCSV[^]).
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
sorry but i am a beginner , i do not know how to start exactly i have just my sql request which is correct and works well
-
i have Two Database in two different servers i want to join two tables from this two Databases
i have no relation between them but just a column wich contain information about the other database
Ex : Database1.dbo.informationCity contain a column named Specification and have this information:
14042020111925_GMS3439_09_04_2020-04_48.xml_6456.zipi want to extract from this column just this number 3439 because with this number i can rely the other database
Ex : Database2.dbo.city contain a column named cityID : 3439
any body have an idea how to do that ?
i tried this Sql request but it did not workSELECT
*
FROM [mo].[dbo].[Database1]
INNER JOIN [Database2].[dbo].[city ] ON substring([Specification], CHARINDEX('GMS', [Specification ]) + 3,4) =
[Database2].[dbo].[city ].[cityID]Hi, You can try to add a linked server for the secondary server to the primary server. After that your query might work. You could also try the OPENROWSET function. Regards, Johan
My advice is free, and you may get what you paid for.
