Copy table from another schema

Question

Hi,
In SQL server, how to duplicate one table from another schema, into the current schema?

Answer 1

Hi @Peter_1985
You could use this query to copy the table schema and data:

Select * into DestinationDB.dbo.tableName from SourceDB.dbo.SourceTable   

The SELECT INTO statement creates a new table from the result set of a query. The columns of the new table inherit their names, data types, nullability, and IDENTITY property from the query’s result set.SELECT INTO doesn’t copy constraints, indexes, or triggers from the query’s source.
If you don't want the data,then add a where 1=2 clause:

Select * into DestinationDB.dbo.tableName from SourceDB.dbo.SourceTable Where 1=2  

For more methods about Copy table, please refer to this article: Copy Table Schema and Data From One Database to Another Database in SQL Server

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

I assume that you mean to duplicate a table in the same database.

Please clarify what exactly do you need to duplicate. A table is not an object that stand on it's own usually. It have data d you need to duplicate the data as well?), it might has constraints, indexes, it might has primary key which is used for foreign key in another table and vise versa it might include foreign key to other table(s) and so on...

The question (clarification we need) is what do you want to duplicate exactly?

Answer 3

Not clear what is "format" of the table but can guess that you mean to build a table with the same structure

If this is a one time task, then you can use SSMS to generate the script code to create the same table -> so next you can simply replace the name of the schema in the code

to do so, navigate to the table in the Object Explorer windows ->right click on the table -> select Script table as -> create to -> new query editor windows

nex, go to the editor windows when the code created for you and edit the schema name

Answer 4

What is the exact way/format to select all (of a table) within one schema, into another table (of another schema)?

Once you have the table set up, you do

INSERT schema1.tbl(col1, col2, col3, ...)
   SELECT col1, col2, col3, ....
   FROM  schema2.tbl

That is, not any different than if the tables would be in the same schema.

I have a nagging feeling, though, that you are really asking about something else. But what is not clear.