How do I import data from one database to another using script?

Question

I have a database DocData size with 8 GB. I want to move three tables form DocData to another database DocData2 with out using import export wizard and identity columns in these tables. I want to move it using a script and copy data with identity column. One of the tables size is 7 GB.

I am using this query

CREATE TABLE [DocData2].[dbo].DocumentPages AS 
SELECT  * FROM [DocData].[dbo].[DocumentPages]

but I am getting the following error.

Incorrect syntax near the keyword 'SELECT'.

Answer 1

FROM BOL

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table  ON

do inserts here

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table  OFF

Answer 2

You need to run the following statement.

Assumptions:

• Both your databases exist on the same server.
• Your destination table DocumentPages doesn't already exist in your destination database DocData2

Script:

SELECT  *   
INTO    [DocData2].[dbo].[DocumentPages] 
FROM    [DocData].[dbo].[DocumentPages]

Your Issue:

You cannot create tables using the script in the question. CREATE TABLE doesn't have any option to accept SELECT output to create a new table in such manner.