Copy Table and Data from one database to another

I am working on a migration project.

Doing the standard processing, taking source data in a staging database, where I then create the new target tables, transform the source data into the new target table structure, and load the data.

However, having created the new target tables in my staging database, I cannot accurately migrate these tables into the new database.

An example table.

The following is the script created by SQL Management Studio if I right click and script the table as CREATE.

CREATE TABLE [dbo].[tbPRO_Package](
[PRO_PackageID] [int] IDENTITY(1,1) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedBy] DEFAULT (suser_sname()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedDate] DEFAULT (getdate()),
[ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedBy] DEFAULT (suser_sname()),
[GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbPRO_Package_GUID] DEFAULT (newid()),
CONSTRAINT [PK_tbPackage_ID] PRIMARY KEY CLUSTERED
(
[PRO_PackageID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Note that the field PRO_PackageID has an identity, and is a primary key.

Also note the constraints on several fields and the default values.

If I try to move this table and data using the SQL Server Import and Export Wizard, telling it to drop and create the table, and to keep identity, it does not correctly generate the table defintion - it generates the following script

CREATE TABLE [Migration Staging].[dbo].[tbPRO_Package] (
[PRO_PackageID] int NOT NULL,
[PackageCode] varchar(8) NOT NULL,
[LKU_VatRateCode] nchar(10),
[CancellationCharge] decimal(18,2),
[CancellationMultiplier] decimal(18,2),
[CreatedDate] datetime NOT NULL,
[CreatedBy] varchar(50) NOT NULL,
[ModifiedDate] datetime NOT NULL,
[ModifiedBy] varchar(50) NOT NULL,
[GUID] uniqueidentifier NOT NULL
)

We've lost the identity, the PK,the constraints and the defaults.

If I use the SSIS Transfer SQL Server Objects Task, then the primary key is kept, but the identity is not, nor are the constraints or defaults.

Am I missing something? Is it actually POSSIBLE to accurately move tables between databases?"If

I use the SSIS Transfer SQL Server Objects Task, then the primary key

is kept, but the identity is not, nor are the constraints or defaults."

This behavior is by design to maintain backward compatibility with the Copy SQL Server Objects task in SQL Server 2000. Only two constraints (Primary key and foreign key constraints) are expected to be copied by Transfer Objects task.

Sorry for the inconvenience.

If transferring the entire database (as opposed to transferring only the tables) is okay, you can use "Transfer Database" task.

If you want to transfer only tables and preserve the table definition, I can think of two ways it can be done using SMO. You can implement C# code using SMO API to copy the tables or you can implement the same (in VB) inside a script task in SSIS. Both of these approaches should be pretty easy.

Hope this helps.|||so - it is not possible to accurately move a table and its data from one database to another, without resorting to writing code?

DESPITE there being two SQL tools that say they do it?

what is the purpose of these SQL tools then? For those oocassions where you want to move tables and data inaccurately?

you have stated this reponse before, in my post here.

Another poster asked in this thread:

MehtaR wrote:

Kaarthik,

I am not sure why you say "This behavior is by design

to maintain backward compatibility with the Copy SQL Server Objects

task in SQL Server 2000."
In SQL 2000 you can use DTS to transfer tables from one database to another preserving the primary as well as identity columns.
Rahul

edit: added smiley to ensure sarcasm detectors are activated |||I have two issues with this:

1) It's *not* transferringg foreign key constraints. That is what it's complaining about.

2) The "transfer database" task is a joke becuase it doesn't transfer the entire database, just the bits and pieces it chooses to transfer.

All I need is a simple, exact, bit for bit transfer from a 2000 to a 2K5 DB. This appears to be an impossibility in SSIS.|||

You can use Redgate SQL Packager to do what you are doing, since i have worked recently on a similar project. You can download their trial verison to play around with it I htink. SQL Packager will allow you to pick and choose the tables or any objects in your source db, and then package it. Then all you gotta do is to move the package(which is an exe file) to the server where you want to migrate it to. When you execute the package, it will cleanly create the db, with the chosen tables. If you are doing an upgrade, and then choose destination db when preparing the package, and it will create an upgrade package, to again move data/tables both(any that you choose). Thanks

MA

|||exactly!

surely a means to do this is fundamental requirement of a product such as SSIS?

I dont think this is too much to expect.|||I have several Redgate tools, such as packager, sql compare and data compare, and they are fantastically useful - I honest couldn't do my job without sql compare now.

But - we shouldn't need a third party tool to do such a basic task.|||There is a "wizard" that supposedly does this. I've not yet tested it and the way it works is quite bass-ackwards. It also only works for 2K/2K5 --> 2K5. What you have to do is go to your 2K5 DB, right click on the desired DB, go to tasks and down at the very bottom you'll see "Copy database". This theoretically creates a SSIS package which I am hoping I can them tweak if necessary.

What info I found on this is here:
http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx