SQLServer : How to copy a particular table from one schema to other schema on the same db,same serve-CodePudding

I have Employee database which has two schemas. dbo and emp_test. I want to copy dbo.employee_salary and create the same table in the emp_test schema.

I tried with SSMS->DB->Tasks->Export option but i couldn't do that. Do we have any sqlscript to achieve the same? Kindly advise.

CodePudding user response：

There is no TSQL command to script or copy a table. You can use

select *
into dbo.SomeTable
from dbo.SomeOtherTable

but that will create an unindexed heap table, without any constraints.

The normal workflow here would be to use SSMS to script the table as create to new Query Editor Window, edit the script to change the names of the table, indexes, constraints, etc, and run it. Followed by running

insert into dbo.SomeTable (col1,col2,...)
select col1, col2, . . .
from dbo.SomeOtherTable

If you don't want to type all the column names you can left-click-drag the columns folder under the table in SSMS and drop onto a query window. This will paste a comma-delimited list of table columns.