Copying all tables from one database to another database

Question

how to copy all the tables from one databse to another in sql server 2008 r2

Jaganathan Bantheswaran · Answer

Try this, SELECT * INTO NewDB.dbo.NewTable FROM OldDB.dbo.OldTable

Hemant Srivastava · Answer

Is your problem got solved?

Pankaj Kumar · Answer

try this link it will help u... http://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server 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/

Hemant Srivastava · Answer

And if you have an identity column then you don't need to put that column in the INSERT-SELECT statment. For example, suppose we have PID column as an identity column in HKS database, then query will be as: -- Creation script for ADDRESS table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [HKS_Testing].[dbo].[ADDRESS]( [P_Id] [int] IDENTITY(1,1) NOT NULL, [LastName] [varchar](50) NULL, [FirstName] [varchar](50) NULL, [Address] [varchar](50) NULL, [City] [varchar](50) NULL, CONSTRAINT [PK_ADDRESS_1] PRIMARY KEY CLUSTERED ( [P_Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ----INSERT INTO ADDRESS using SELECT INSERT INTO [HKS_Testing].[dbo].[ADDRESS] (LastName,FirstName,Address,City) SELECT LastName,FirstName,Address,City FROM [HKS].[dbo].[ADDRESS] ----Verify that Data in ADDRESS table SELECT * FROM [HKS_Testing].[dbo].[ADDRESS] GO

Hemant Srivastava · Answer

Create a table first using Creation Table script, then insert all the data from DB1.table to Db2.table using Insert-Select command For example, I am copying here ADDRESS Table from HKS databse to HKS_Testing database in following way: -- Creation script for ADDRESS table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [HKS_Testing].[dbo].[ADDRESS]( [P_Id] [int] NOT NULL, [LastName] [varchar](50) NULL, [FirstName] [varchar](50) NULL, [Address] [varchar](50) NULL, [City] [varchar](50) NULL, CONSTRAINT [PK_ADDRESS_1] PRIMARY KEY CLUSTERED ( [P_Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ---- Creation table ends here -- Now start copying the data ----INSERT INTO ADDRESS using SELECT INSERT INTO [HKS_Testing].[dbo].[ADDRESS] (P_Id,LastName,FirstName,Address,City) SELECT P_Id,LastName,FirstName,Address,City FROM [HKS].[dbo].[ADDRESS] ----Verify that Data in ADDRESS table SELECT * FROM [HKS_Testing].[dbo].[ADDRESS] GO