How to Select Data of a Table from Another Database in SQL Server

How to select data of a table from another database in my sql?

you are querying directly to the database. Put tables name in select clause db1.table1.*.

and also you are using alias of tables in where clause where dt1.table1.id = dt2.table2.id

try instead of dt1 = db1 and dt2 = db2

select db1.table1.* 
from db1.table1 dt1,db1.table2 dt2
where db1.table1.id = db2.table2.id

how to copy only data from one database table to another database existing table in sql server

Try this ...

INSERT INTO DataBase2.dbo.table2
SELECT * FROM DataBase1.dbo.table1

Transfer data from one database to another database

There are several ways to do this, below are two options:

Option 1
- Right click on the database you want to copy

• Choose 'Tasks' > 'Generate scripts'

• 'Select specific database objects'

• Check 'Tables'

• Mark 'Save to new query window'

• Click 'Advanced'

• Set 'Types of data to script' to 'Schema and data'

• Next, Next

You can now run the generated query on the new database.

Option 2

• Right click on the database you want to copy

• 'Tasks' > 'Export Data'

• Next, Next

• Choose the database to copy the tables to

• Mark 'Copy data from one or more tables or views'

• Choose the tables you want to copy

• Finish

Insert into select from another database table in SQL Server

I finally found the answer, it can be done this way:

 conn.Open();
 string database1 = conn.Database.ToString();
 SqlCommand insert = new SqlCommand();
 insert.Connection = conn2;             
 insert.Parameters.Add("@Réf", SqlDbType.Int).Value = row["N° Caisse"];
 insert.CommandText = @"INSERT INTO Caisse([N° Caisse],[Date d'Ouverture],[Date de Clôture],[Fond de Caisse],[Vendeur]) SELECT*FROM ["+database1+"].dbo.Caisse x WHERE  x.[N° Caisse]=@Réf";
 conn2.Open();
 insert.ExecuteNonQuery();
 conn2.Close();

the target database can be accessed with the SqlConnection.Database property.

How to use table of different database in SQL Server

If you want to use a table in another database then you can do like this in sql server when the database is on same server:

Select * from [DBName].[Schema].[Table]

If the database is in another server, specify the linked server name too:

Select * from [DBServer].[DBName].[Schema].[Table]

Schema name - replace by your schema which is "dbo" by default in sql server.

copy tables with data to another database in SQL Server 2008

You can use the same way to copy the tables within one database, the SELECT INTO but use a fully qualified tables names database.schema.object_name instead like so:

USE TheOtherDB;

SELECT *
INTO NewTable
FROM TheFirstDB.Schemaname.OldTable

This will create a new table Newtable in the database TheOtherDB from the table OldTable whih belongs to the databaseTheFirstDB

---

Related Topics