Copying all tables from one database to another database

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/

Try this,

SELECT * INTO NewDB.dbo.NewTable FROM OldDB.dbo.OldTable 

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
 

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