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(11) 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] (LastNameFirstNameAddressCity) SELECT LastNameFirstNameAddressCity 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_IdLastNameFirstNameAddressCity) SELECT P_IdLastNameFirstNameAddressCity FROM [HKS].[dbo].[ADDRESS] ----Verify that Data in ADDRESS table SELECT * FROM [HKS_Testing].[dbo].[ADDRESS] GO