How to Copy a Table Schema and Constraints to a Table of Different Database

How to copy a table schema and constraints to a table of different database?

SELECT INTO will create a new table with the same schema. So you could:

 SELECT *
 INTO newdb.dbo.newtable
 FROM olddb.dbo.oldtable

To just copy the schema and not the data:

 SELECT TOP 0 *
 INTO newdb.dbo.newtable
 FROM olddb.dbo.oldtable

This would not copy indexes or keys. To copy those, right click the table in SQL Sever Management Studio and choose 'Script table as'. That will give you a script you can run on the new database.

Copy Access table structure and constraints using C# or SQL

You can "export" to the same database, which will create a copy with all indexes etc, but not relationships.

        // Start a new instance of Access for Automation
        oAccess = new Access.Application();

        // Open a database
        oAccess.OpenCurrentDatabase(@"z:\docs\test.accdb");
        oAccess.DoCmd.TransferDatabase(
            Access.AcDataTransferType.acExport,
            "Microsoft Access",
            @"z:\docs\test.accdb",
            Access.AcObjectType.acTable, 
            "table1",
            "newtable",true,false);

Searching for a way to copy table data from one SQL Server database to another (concerning Identity and Foreign Keys)

Using the vendor's SQL Server database schema and loading the data yourself is typically the correct approach for migrating to SQL Server with packaged software. But there may be additional guidance available from the vendor.

Instead of trying to load the tables in an order that is compatible the foreign key constraints, which is not always even possible, disable all the foreign keys before loading the database and re-enable them after. See eg Temporarily disable all foreign key constraints

---

Related Topics