Coding Article #: 8 - Published On: February 26, 2010 @ 17:36:07 PM - Last Updated on: July 13, 2012

Supported Files
No Files for this Article.

Screenshot of Source Code

Importing data from 1 database to another database in SQL Server 2005 is right easy but time-consuming when it comes to having to edit the
Primary Key
Identity Specification
Default Value or Binding
This would take up to an hour to complete until now.
Doing a search on Google gave up no information for restoring these values, so off I went in search of a way to do this and after my findings, I have to say it is right simple to accomplish.
Follow the steps below to get all your data types and information into your new database.
Step #1: Creating our Scripts to use for Importing all Data
#1: Right click on your database name and choose
Tasks / Generate Scripts...
Click [Next] on the [Welcome to the Generate SQL Server Scripts Wizard]
#2: On the [Select Database] choose your database and click the "Script all objects in the selected database" then click "Next".
#3: On the [Choose Script Options] dialog, leave default and click "Next"

#4: On [Output Options] select [Script to file] and save it to a location that is easy to find, Desktop or My Documents or directly into a drive will be and click [Next].
#5: On [Script Wizard Summary], click [Finish] to generate your script.
#6: The last Dialog window is the [Generate Script Progress] window, once this completes; open the folder location where you saved your script.

--------
Now, in order to keep ourselves away from generating many errors we have to edit the script that we just created, so let us do that now.
When you open the script, there is a LOT of information that we do not need, so we will pick through and only take out what we need only.
Step #2: Working with the Generated Script
#1: What we are needing from the generated script is all area's that start with:
[CREATE TABLE]
And end with
[ ) ON [PRIMARY]]

Now, the ON [Primary] means that you have set a "Primary Key" for the Table. Go through the entire script and gather out all of the tables and then we will continue.

#2: Now that we have all of our Table scripts together, Let us edit them to work with our Import procedure.

--(DemoTable (Original Generated SQL Script))
--====================================
CREATE TABLE [dbo].[DemoTable](
 [AFID] [int] IDENTITY(1,1) NOT NULL,
[AFName] [varchar](50) NULL,
[AFDescription] [varchar](max) NULL,
[AFLink] [varchar](300) NULL,
CONSTRAINT [PK_ DemoTable] PRIMARY KEY CLUSTERED 
(
 [AFID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
--====================================.

The above script shows what SQL Server generates, now, what we have to do is remove the brackets around the [Data Type] names.
[int] => int
[varchar] => varchar

I think you get the point, for all your [Data Type] names, they cannot have the brackets around them, this will cause an error in the Import.

--(DemoTable (Custom Edited SQL Script))
--====================================
CREATE TABLE [dbo].[DemoTable](
 [AFID] int IDENTITY(1,1) NOT NULL,
[AFName] varchar(50) NULL,
[AFDescription] varchar(max) NULL,
[AFLink] varchar(300) NULL,
CONSTRAINT [PK_ DemoTable] PRIMARY KEY CLUSTERED 
(
 [AFID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
--====================================.

As you can see in the Custom script above, our [Data Type] no longer has the [] brackets around it, this is all the editing that we need to do to our Generated SQL Script, we can now move on to the Import part of this tutorial.

-------------
Step #3: Importing our database with our custom SQL Script

#1: Right Click on the database that you want to import the data too. And choose
Task / Import Data

#2: Click Next on the [Welcome Screen]

#3: From the [Choose a Data Source] dialog,
choose your database that you want to import data from. Provide your user credentials if needed. Then click [Next].

#4: From the [Choose a Destination]
The database is already selected. Provide your user credentials if needed, then click [Next].

#5: On the [Specify Table Copy or Query]
Choose [Copy data from one or more tables or views] and click [Next].

#6: "Select All" the tables that you want to import Data from.

#7: For each table under [Mapping] click [Edit]
On the [Column Mapping] dialog, click [Edit SQL]
Remove the Auto Generated script and replace with your custom script.
Click [OK] then [OK] again, and REPEAT #7 for each of the Tables that
you are going to Import.

#8: After completing the Mapping - SQL Edit of all our tables, Click [Next].

#9: On [Save and Execute Package] - Choose [Execute immediately] then click [Next].

#10: In the [Complete the Wizard] dialog, make sure that all rows have:
[The new target table will be created].
Making sure that it does not state:
[The target table will be dropped and then re-created].
The above message means that you accidentally clicked the
[Drop and re-create destination table] in the [Column Mapping] section, if this is so, click the [Back] button and correct this for the table(s) and then proceed to step #11.

#11: Click the [Finish] button to import your data.
If you followed these steps correctly, then you should have a Success rate of 100% in the import, and all data should be in the new database.
If you have any questions, please go to http://www.knowledge-exchange.org/Zone=SQL_Server and ask there, and someone will be happy to assist you.

Thank you and I hope that you find this information handy and useful as I did when I figured it out the other morning.