Moving tables in SQL Server 2005 from one server to another

I have been working with databases for nearly 10 years now, but for
the life of me I can't figure out how to move database tables from one
server to another in SQL Server 2005 without using scripts.
I used to use DTS in SQL Server 2000 and everything seemed to go
smoothly. I used a combination of a Visio document (to drop and
recreate constraints, add columns, change defaults, etc) and DTS to
manage my whole deployment.
Now I am trying these tasks in SQL Server 2005 and while Visio still
can keep the structure of my tables in sync, I am having issues with
the replacement tool for DTS.
I tried using SSIS to move the data, and this is a description of the
issues I am having. I can't find any posts from anybody having these
issues, and I really don't understand why because I am getting the
same results on 2 different installations of SQL Server 2005.
1. I start by going through the menu and clicking the "import data" or
"export data" command.
2. I choose the source and destination database. I use the "SQL Native
Client" driver.
3. I choose the "Copy data from one or more tables or views" option.
4. I choose my table and give it a name in the destination database.
5. I click the "Edit Mappings" button, and choose the option to delete
rows in destination table, and check the box for "enable identity
insert".
6. Then I choose to execute the package immediately.
When I follow these exact steps, I get the following results:
1. The destination data is not removed from the table before the data
from the source is inserted.
2. The data that is inserted is renumbered starting from the last
index of the table, rather than using the original identity values.
Can somebody please explain to me why I can't get the exact same data
into another table in another database like I could in the previous
version of SQL Server?
If I have to switch to scripts to do this I will even though I much
prefer the GUI. If someone can suggest an easy way to generate the
scripts, it would be helpful too.I use linked server for example. However , i tried to reproduce your problem
( I don't have right now access to the second server) so I tried to import
table from one database to another. When I cliked on Edit Mappings button
a 'Delete rows in destination table' was not enabled at all. So I went for
re-create destination table and it worked fine
What is the version are you using?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188458357.350505.83230@.z24g2000prh.googlegroups.com...
>I have been working with databases for nearly 10 years now, but for
> the life of me I can't figure out how to move database tables from one
> server to another in SQL Server 2005 without using scripts.
> I used to use DTS in SQL Server 2000 and everything seemed to go
> smoothly. I used a combination of a Visio document (to drop and
> recreate constraints, add columns, change defaults, etc) and DTS to
> manage my whole deployment.
> Now I am trying these tasks in SQL Server 2005 and while Visio still
> can keep the structure of my tables in sync, I am having issues with
> the replacement tool for DTS.
> I tried using SSIS to move the data, and this is a description of the
> issues I am having. I can't find any posts from anybody having these
> issues, and I really don't understand why because I am getting the
> same results on 2 different installations of SQL Server 2005.
> 1. I start by going through the menu and clicking the "import data" or
> "export data" command.
> 2. I choose the source and destination database. I use the "SQL Native
> Client" driver.
> 3. I choose the "Copy data from one or more tables or views" option.
> 4. I choose my table and give it a name in the destination database.
> 5. I click the "Edit Mappings" button, and choose the option to delete
> rows in destination table, and check the box for "enable identity
> insert".
> 6. Then I choose to execute the package immediately.
> When I follow these exact steps, I get the following results:
> 1. The destination data is not removed from the table before the data
> from the source is inserted.
> 2. The data that is inserted is renumbered starting from the last
> index of the table, rather than using the original identity values.
> Can somebody please explain to me why I can't get the exact same data
> into another table in another database like I could in the previous
> version of SQL Server?
> If I have to switch to scripts to do this I will even though I much
> prefer the GUI. If someone can suggest an easy way to generate the
> scripts, it would be helpful too.
>|||On Aug 30, 12:44 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> I use linked server for example. However , i tried to reproduce your problem
> ( I don't have right now access to the second server) so I tried to import
> table from one database to another. When I cliked on Edit Mappings button
> a 'Delete rows in destination table' was not enabled at all. So I went for
> re-create destination table and it worked fine
> What is the version are you using?
>
I have tried it on 2 different physical servers. One of them is using
version 9.0.3054 and the other is still on 9.0.1399 because I haven't
installed the service pack yet.
I am able to reproduce these results reliably (I have never been able
to get another result than this) in both environments. I also tried
dropping the table in the destination database and using the "create
table" option as you did, but the identity columns are always
completely regenerated instead of inserted as-is.|||Hi
I ran SQL Server Profiler while I use Import data wizard and I observered
that SQL Server used TRUNCATE TABLE when you specufy delete daat from
destination , so as you know it would delete/reset an identity on the table.
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188460907.491792.264000@.q3g2000prf.googlegroups.com...
> On Aug 30, 12:44 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> I use linked server for example. However , i tried to reproduce your
>> problem
>> ( I don't have right now access to the second server) so I tried to
>> import
>> table from one database to another. When I cliked on Edit Mappings
>> button
>> a 'Delete rows in destination table' was not enabled at all. So I went
>> for
>> re-create destination table and it worked fine
>> What is the version are you using?
> I have tried it on 2 different physical servers. One of them is using
> version 9.0.3054 and the other is still on 9.0.1399 because I haven't
> installed the service pack yet.
> I am able to reproduce these results reliably (I have never been able
> to get another result than this) in both environments. I also tried
> dropping the table in the destination database and using the "create
> table" option as you did, but the identity columns are always
> completely regenerated instead of inserted as-is.
>|||On Aug 30, 1:33 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> I ran SQL Server Profiler while I use Import data wizard and I observered
> that SQL Server used TRUNCATE TABLE when you specufy delete daat from
> destination , so as you know it would delete/reset an identity on the table.
> "NightOwl888" <sstorh...@.webuniverse.net> wrote in message
>
TRUNCATE TABLE is fine. The problem is that when the new data is
inserted, it renumbers all of the identities of the records. For
example, if the first record has an identity of 2, it renumbers it to
1, the second record that was 3 is now 2, and so on. This is a major
problem since this is the colunn all of the other foreign keys use. I
have tried changing drivers, and pretty much any combination of
actions you can think of to try to get this to work with SSIS, but no
luck.|||Hi
I have not seen in Profiler statement SET INSERT_IDENTITY ON for specific
table. So It could be bug as well.
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188468028.899752.220000@.x35g2000prf.googlegroups.com...
> On Aug 30, 1:33 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> I ran SQL Server Profiler while I use Import data wizard and I
>> observered
>> that SQL Server used TRUNCATE TABLE when you specufy delete daat from
>> destination , so as you know it would delete/reset an identity on the
>> table.
>> "NightOwl888" <sstorh...@.webuniverse.net> wrote in message
> TRUNCATE TABLE is fine. The problem is that when the new data is
> inserted, it renumbers all of the identities of the records. For
> example, if the first record has an identity of 2, it renumbers it to
> 1, the second record that was 3 is now 2, and so on. This is a major
> problem since this is the colunn all of the other foreign keys use. I
> have tried changing drivers, and pretty much any combination of
> actions you can think of to try to get this to work with SSIS, but no
> luck.
>sql