none
query to move tables from one database to another database

    Question

  • I'm new in SQL

    There are several tables in DB1, their names a quite similar, Table1, Table2....... Table900

    There are also other tables DB1, but I just want to move these 900 tables to DB2.

    I know SQL Server has table movement tool, but choose 900 tables by hand is crazy.....

    Do you know how to write query to move the tables I want from DB1 to DB2?    Or do I need to stored procedure? I have not used stored procedure and the table movement tool asks me to write query.....

    Friday, October 18, 2013 8:28 PM

Answers

All replies

  • One approach is using a third party tool - RedGate SQL Compare and Data Compare.

    Narsimha

    Friday, October 18, 2013 9:45 PM
  • Hi,

    Even if you write a query (or SP), you need to still select 900 tables or de-select the rest. i believe that Import/Export wizard (or any other tool) is the easiest way.

    However, if you have to do more filtering/selection based on business logic, you may write a query something like below. I have written it in very simple way, you may enhance it as your requirements.

    DECLARE @sql varchar(500)
    	, @tableName varchar(100)
    	, @schemaName varchar(100)
    
    DECLARE curTables CURSOR
    FOR
    SELECT TOP (2) s.name SchemaName, t.name TableName 
    FROM [AdventureWorks2008R2].sys.tables t
    	INNER JOIN [AdventureWorks2008R2].sys.schemas s
    		ON t.schema_id = s.schema_id
    -- WHERE t.name LIKE 'TABLE%'
    
    OPEN curTables
    FETCH NEXT FROM curTables INTO @schemaName, @tableName
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    
    	SET @sql = 'SELECT * INTO dbo.'
    	SET @sql += @tableName
    	SET @sql += ' FROM AdventureWorks2008R2.'
    	SET @sql += @schemaName
    	SET @sql += '.'
    	SET @sql += @tableName
    
    	EXEC (@sql)
    
    	FETCH NEXT FROM curTables INTO @schemaName, @tableName
    END
    CLOSE curTables
    DEALLOCATE curTables
    

    ------------------------------------

    Dinesh Priyankara
    http://dinesql.blogspot.com/

     
    Sunday, October 20, 2013 3:04 AM
  • I'm new in SQL

    There are several tables in DB1, their names a quite similar, Table1, Table2....... Table900

    There are also other tables DB1, but I just want to move these 900 tables to DB2.

    I know SQL Server has table movement tool, but choose 900 tables by hand is crazy.....

    Do you know how to write query to move the tables I want from DB1 to DB2?    Or do I need to stored procedure? I have not used stored procedure and the table movement tool asks me to write query.....

    Hi ,

    If you want to move only tables definition , get the scripts by - >

    Right-click on the database name and choose Tasks -> Generate Scripts. On the Scripting Options screen set Script Data to False.

    If you want to move tables definition with data , get the scripts by - >

    Right-click on the database name and choose Tasks -> Generate Scripts. On the Scripting Options screen set Script Data to True.

    Related reference links :

    http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/

    Transfer SQL Server Objects Task

    http://www.codeproject.com/Articles/490448/Transfer-data-or-script-between-two-SQL-Server-dat

    You can also try like this ,

    USE MASTER
    GO
    CREATE DATABASE DB1
    GO
    CREATE DATABASE DB2
    GO
    USE DB1
    GO
    CREATE TABLE Table1 (Id INT)
    CREATE TABLE Table2 (Id INT)
    SELECT name , IDENTITY(INT) n INTO ##temp FROM sys.tables WHERE name LIKE 'Table%'
    DECLARE @i INT  = 1 ,@Tblname NVARCHAR(256)
    WHILE @i <= (SELECT MAX(n) FROM ##temp)
    BEGIN
    SELECT @Tblname = name FROM ##temp WHERE n = @i
    EXEC ('SELECT * INTO DB2.dbo.'+@Tblname+' FROM '+@Tblname)
    SET @i = @i + 1
    END
    DROP TABLE ##temp
    SELECT * FROM DB2.dbo.Table1
    SELECT * FROM DB2.dbo.Table2
    USE MASTER
    GO
    DROP DATABASE DB1,DB2

    If you want to Compare two tables definition / metadata in different databases -

     http://social.technet.microsoft.com/wiki/contents/articles/19133.how-to-compare-two-tables-definition-metadata-in-different-databases.aspx

    SQL Server Data Comparison Tool in Visual Studio 2010


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Sunday, October 20, 2013 3:10 AM
    Moderator