Moving table from one database to another with primary key and other keys

Question

I want to move all the table from one database to another with primary key and all other keys using SQL queries. I am using SQL Server 2005 and I got a SQL queries to move the table but the keys are not moved.

And my queries is as follows

set @cSQL='Select Name from SRCDB.sys.tables where Type=''U''' 

Insert into #TempTable
exec (@cSQL)

while((select count(tName) from #t1Table)>0)
begin
    select top 1 @cName=tName from #t1Table
    set @cSQL='Select * into NEWDB.dbo.'+@cName+' from   SRCDB.dbo.'+@cName +' where 1=2'
    exec(@cSQL)
    delete from #t1Table where tName=@cName
end

where SRCDB is the name of source database and NEWDB is the name of destination database

How can I achieve this..?

Can anyone help me in this...

Thank you...

I don't think having `where 1=2` is going to do you any favours. — Colm Prunty, Nov 01 '13 at 11:02
@Upendra : I dont want to move the records of tables so i made the condition to false — ShaQue, Nov 01 '13 at 11:40

Aleksandr Fedorenko · Accepted Answer · 2013-11-03T10:28:33.777

The following T-SQL statement move all the table, primary key and foreign key from one database to another. Notice that the method SELECT * INTO FROM ... WHERE 1 = 2 does not create COMPUTED columns and user-data types. Suppose also that all primary keys are clustered

--ROLLBACK
SET XACT_ABORT ON
BEGIN TRAN
DECLARE @dsql nvarchar(max) = N''
SELECT @dsql += ' SELECT * INTO NEWDB.dbo.' + name + ' FROM SRCDB.dbo. ' + name + ' WHERE 1 = 2'
FROM sys.tables
--PRINT @dsql
EXEC sp_executesql @dsql

SET @dsql = N''
;WITH cte AS
 (SELECT 1 AS orderForExec, table_name, column_name, constraint_name, ordinal_position, 
         'PRIMARY KEY' AS defConst, NULL AS refTable, NULL AS refCol
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE          
  WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1    
  UNION ALL
  SELECT 2, t3.table_name, t3.column_name, t1.constraint_name, t3.ordinal_position,
          'FOREIGN KEY', t2.table_name, t2.column_name
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as t1
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2 ON t1 .UNIQUE_CONSTRAINT_NAME = t2.CONSTRAINT_NAME
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t3 ON t1.CONSTRAINT_NAME = t3.CONSTRAINT_NAME
          AND t3.ordinal_position = t2.ordinal_position
    )
    SELECT @dsql += ' ALTER TABLE NEWDB.dbo.' + c1.table_name + 
           ' ADD CONSTRAINT ' + c1.constraint_name + ' ' + c1.defConst + ' (' +        
           STUFF((SELECT ',' + c2.column_name
                  FROM cte c2
                  WHERE c2.constraint_name = c1.constraint_name
                  ORDER BY c2.ordinal_position ASC
                  FOR XML PATH(''), TYPE
                  ).value('.', 'nvarchar(max)'), 1, 1, '') + ')' +
                  CASE WHEN defConst = 'FOREIGN KEY' THEN ' REFERENCES ' + c1.refTable + ' (' +
           STUFF((SELECT ',' + c2.refCol
                  FROM cte c2
                  WHERE c2.constraint_name = c1.constraint_name
                  ORDER BY c2.ordinal_position ASC
                  FOR XML PATH(''), TYPE
                  ).value('.', 'nvarchar(max)'), 1, 1, '') + ')' ELSE '' END 
    FROM (SELECT DISTINCT orderForExec, table_name, defConst, constraint_name, refTable FROM cte) AS c1
    ORDER BY orderForExec
    --PRINT @dsql
    EXEC sp_executesql @dsql
COMMIT TRAN

it is working fine can you explain me clearly about ;WITH cte AS and the what the process is going on — ShaQue, Nov 13 '13 at 05:30
@ShaQue In this case ;WITH cte uses as SELECT...FROM(SELECT...FROM), in other words it's a subquery. I my opinion it's more readable — Aleksandr Fedorenko, Nov 16 '13 at 09:06

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

You can generate customized script of Source Database and run the script for Destination Database.

Here is the link and slightly better [one][2]

Get the complete table and then perform the delete queries on Destination database as per requirement

If you want to do with Query. I guess this link would be helpful

edited May 23 '17 at 11:50

Community

1
1

answered Nov 01 '13 at 11:04

Dusht

4,712
3
18
24

@ShaQue I have appended my above answer with a link which shows how to do it with query. – Dusht Nov 01 '13 at 11:19
@Dushi : I dont know which and all tables having the primary key so i cannot set in your way – ShaQue Nov 01 '13 at 11:42

score 0 · Answer 3 · edited May 23 '17 at 12:21

DECLARE @strSQL NVARCHAR(MAX)
DECLARE @Name VARCHAR(50)

SELECT Name into #TempTable FROM SRCDB.sys.tables WHERE Type='U'

WHILE((SELECT COUNT(Name) FROM #TempTable) > 0)
BEGIN
    SELECT TOP 1 @Name = Name FROM #TempTable
    SET @strSQL = 'SELECT * INTO NEWDB.dbo.[' + @Name + '] FROM   SRCDB.dbo.[' + @Name + ']'
    EXEC(@strSQL)

    DELETE FROM #TempTable WHERE Name = @Name
END

DROP TABLE #TempTable

If you have destination table already created then just set identity insert on and change query like below :

SET @strSQL = ' SET IDENTITY_INSERT NEWDB.dbo.[' + @Name + '] ON; ' +
              ' INSERT INTO NEWDB.dbo.[' + @Name + '] SELECT * FROM SRCDB.dbo.[' + @Name + ']' +
              ' SET IDENTITY_INSERT NEWDB.dbo.[' + @Name + '] OFF '

UPDATE :

If you don't want records and only want to create table with all key constaints then check this solution :

In SQL Server, how do I generate a CREATE TABLE statement for a given table?

thank you, It is not moving the keys and i cant use the update statement because some table does not have the keys — ShaQue, Nov 01 '13 at 11:39
What is meaning by not moving the keys ? You mean all primary key constraints of tables ? — Upendra Chaudhari, Nov 01 '13 at 11:46

score 0 · Answer 4 · answered Nov 21 '13 at 23:21

0

The following script copies many tables from a source DB into another destination DB, taking into account that some of these tables have auto-increment columns: http://sqlhint.com/sqlserver/copy-tables-auto-increment-into-separate-database

answered Nov 21 '13 at 23:21

bjnr

3,353
1
18
32

Moving table from one database to another with primary key and other keys

4 Answers4