0

I have a database in SQL Server where I've migrated some tables from Oracle. Now, these migrated tables have a different schema from the default .dbo.

I've selected all table names with this query: 

select '[' + SCHEMA_NAME(schema_id) + '].['+name+']' as SchemaTable
from sys.tables 
where schema_name(schema_id) like '%my_schema%'

and I want to move all these tables from db1 to db2.

Is there a way to just move all those tables without making a copy (I think I can do a select * from... with all table names, but there is, like, a thousand tables, so that solution would take quite some time).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Serphentelm
  • 97
  • 9
  • Are you using VisualStudio? It comes with pretty tools for data and schema migrations in SQL Server – mororo Mar 12 '19 at 14:45
  • Possible duplicate of [How do I move a table into a schema in T-SQL](https://stackoverflow.com/questions/1149159/how-do-i-move-a-table-into-a-schema-in-t-sql) – SMor Mar 12 '19 at 14:47
  • no, i've migrated my data from oracle with a SSMA, but i made a mistake and saved all the tables in a different db; and i just want to avoid all the times that convert schema takes – Serphentelm Mar 12 '19 at 14:48
  • 2
    Schemas aren't databases. There's no reason to change the schema of a table – Panagiotis Kanavos Mar 12 '19 at 14:48
  • You can't move a table from one database to another, no. Each database is a completely separate data file. You can move a table to a different schema within a database, but if you want to move from one database to the other you will need to "copy" it from the source database. You'll likely want to script the tables out, with their respective keys, constraints, etc, then use an `INSERT INTO` for each, but dropping the original tables. – Thom A Mar 12 '19 at 14:49
  • @Serphentelm different *database* or different *schema*? `dbo` is a schema. You can have multiple schemas in a database without affecting performance or manageability, on the contrary, multiple schemas are *encouraged*, to make managing large number of objects easier. If you check any of the SQL Server sample databases like AdventureWorks or WorldWideImporters you'll find multiple schemas splitting objects across functional boundaries – Panagiotis Kanavos Mar 12 '19 at 14:51
  • @Serphentelm moving tables from one schema to another is trivial too, as there's not data movement involved. A simple `ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;` will move an object to another schema instantaneously. – Panagiotis Kanavos Mar 12 '19 at 14:53
  • moving tables with this schemas (that's not .dbo) from my db1 to my db2, which are on the same server. I know that i can have multiple schemas in a single db (the case I'm in) but I wuold like to keep separate these tables from the others by changin the db – Serphentelm Mar 12 '19 at 14:54
  • No, there is no way whatsoever to "move" tables across databases without copying the data. Database integrity requires that such operations are duly recorded in the transaction log. You can use the data import/export wizard or do things like script `bcp` invocations or use SMO to copy data programmatically. For very simple tables you can also write a query that produces `SELECT .. INTO` statements, but this will leave you without any indexes or constraints whatsoever, which is not usually what you want. – Jeroen Mostert Mar 12 '19 at 16:14

0 Answers0