How to create an alias of database in SQL Server
Create a database with the name you want to impersonate. Re-jigg the DDL code generator to create a view for every table in the database that has the tables I need to access via the hardcoded name. Basically, each view will have a statement that looks like this..
CREATE VIEW schemaname.tablename as SELECT * FROM targetdbname.schemaname.tablename
Example:
The target database name that is hardcoded is called ProdDBV1
and the Source DB you have is named ProductDatabaseDatabaseV1
, schema is dbo
and table name is customer
- Create the database called
ProdDBV1
using SSMS or script. CREATE VIEW dbo.customer as SELECT * FROM ProductDatabaseDatabaseV1.dbo.customer
If you can enumerate each table in your "source" database and then create the DDL as above. If you want I can update this posting with a code example. (using the sp_msforeachtable
procedure if possible)
Microsoft SQL Server 2005: Create alias for database
You can do replication from one database to another database on the same machine. You can also copy data directly without having to create an alias. For instance if you had a table named Users in DB2 and a Users table in DB1 and they are the same schema you could easily just do
INSERT INTO DB1..Users
select * from DB2..Users
Now, a synonym would allow you to use a table from DB2 as if it was a table in DB1 so for instance if you have a table named Products in DB2 you could do
use DB1
GO
CREATE SYNONYM [dbo].[Products] FOR [DB2].[dbo].[Products]
GO
-- Now the following would give you the same result
select * from DB2..Products
select * from Products
For more information on synonyms see here
Create alias for SQL server to use in query
InstanceName and alias are two different things.
For local server use Database.dbo.Table
To connect to another server (or another instance) you should add Linked Server
https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine.
Usually you do not need any alias, but if you really want to do this use example:
EXEC sp_addlinkedserver
@server=N'S1_instance1',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'S1\instance1'
Related Topics
Oracle: SQL Query That Returns Rows with Only Numeric Values
Postgresql Query to Count/Group by Day and Display Days with No Data
SQL Server Equivalent of MySQL's Now()
Add Row to Query Result Using Select
How to Drop All Foreign Key Constraints in All Tables
MySQL Statement Combining a Join and a Count
Get All Characters Before Space in MySQL
Delete with "Join" in Oracle SQL Query
Comparing Results with Today's Date
How to Do a Simple 'Find and Replace" in Mssql
Calling a Stored Procedure in Oracle with in and Out Parameters
Exporting Blob from MySQL Database to File with Only SQL
Postgresql Calculate Difference Between Rows
How to Execute SQL Statements Saved in a Table with T-Sql
Adding a Uniqueidentifier Column and Adding the Default to Generate New Guid