I have to make a copy of some tables from one BD to another BD within the same SQL SERVER instance. I was thinking about using the Import / Export utility but I would have to recreate the indexes again and create the statistics in the target table.
Does anyone know a method that migrates the table with indexes, statistics and everything else?
A thousand thanks.
You can generate a Script where you only include the table you want
Right click on the database-
chores-
generate scripct-
following
and you will get a window similar to this one depending on the version you use
you choose the tables or tables that you want to import you give next and a screen like this will appear
click on the advanced button and there you will be able to select the characteristics that you want to export from your data table, trigers, restrictions, etc. you will generate an .sql file or you can save it in the paper holder then you will have to execute the instructions in the other DB and ready
Maybe it's a more laborious option but it can work =)
To duplicate a table, we can do several things:
Copy only the structure :
SELECT * Into <DestinationTableName>
From <SourceTableName>
Where 1 = 2
Either make an exact duplicate of it:
SELECT * INTO <MyNewTable>
FROM <MyTable>
with the exception that, constraints or indexes will not be copied.
To send a table from a BD_1 to BD_2 , cut and paste :
RENAME TABLE <bd_1.tabla> to <bd_2.tabla_copia>
Note : Remove the table from BD_1
Create a table in the destination DB and store the tuples of the table contained in the source DB:
CREATE TABLE <BD_destino>.<nombre_tabla_copiada>
SELECT * FROM <BD_origen>.<tabla>
Note : Do not delete the table from the Source DB
Create a table in the destination DB and copy the structure but it will not store the tuples of the table contained in the source DB:
CREATE TABLE <BD_destino>.<nombre_tabla_copiada>
LIKE <BD_origen>.<tabla>
You can use insert select to copy all the structure and data of the table, like this:
INSERT INTO tablanueva
SELECT * FROM tablaoriginal;
Of course tablanueva and tablaoriginal should not have the same name if you are going to copy it into the same database.
You can see more information here .
With the database design tool MySQL WorkBench you can export your database with the primary keys and foreign without problem.
entering the Generate Scripts option for the database and after selecting the table that you want to copy, you must enter the Advanced Options button and within the list an item appears on what to do with the statistics, for default is not done, but in the drop-down list you can select it, I leave a screenshot for you to see. 