July 7, 2015 at 5:46 pm
Hi, I'm trying to migrate tables from one database to another. The tables in the source database (MY_SOURCE_DB) have a schema of "dbo". I want to create the tables in the target database (MY_TARGET_DB) with a schema of "xyz". I want to migrate all of the tables with "contract" in the table name:
select table_name from information_schema.tables
where table_name like '%contract%'
I want to migrate the full table definitions, table data and dependent objects. This needs to be done through a sql script as opposed to SSIS. Can you provide a script that will accomplish this?
July 7, 2015 at 7:27 pm
Are the databases on the same server? If so you should be able to use a SELECT...INTO... syntax to migrate the tables:
SELECT [Col1], CAST([Col2] AS VARCHAR(10),...)
INTO MY_TARGET_DB.xyz.tablename
FROM MY_SOURCE_DB.dbo.tablename
Notice on Col2, it should use the source table data type unless you cast it to something else. If it's a complete copy with no changes, just use SELECT *.
As far as the table names, you could use dynamic SQL to loop through the list you generate with your "select table_name from information_schema.tables where table_name like '%contract%'" query.
July 7, 2015 at 7:46 pm
sqlguy-736318 (7/7/2015)
I want to migrate the full table definitions, table data and dependent objects. This needs to be done through a sql script as opposed to SSIS. Can you provide a script that will accomplish this?
Why would you avoid the exact tool you need to do the job?
Be aware of SELECT...INTO... because it won't copy table definitions exactly the same and can create problems.
What do you consider dependent objects? Indexes, constraints, views, stored procedures, functions, synonyms, triggers?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply