IMPORT EXPORT fails for existing TARGET tables.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

IMPORT EXPORT fails for existing TARGET tables.

Author

Topic

Raj_Mahendran
Starting Member

14 Posts

Posted - 2008-08-14 : 17:52:33

I need to move tables from Database A to Database B. I used a import export wizard in SMStudio 2005. The tables are created on the destination and data gets copied. Now the desitnation tables dont have keys and identity retained (there are almost 50 tables). So I created scripts from Database A and ran it against Database B. The tables were created successfully with keys and identity. Now I tried to copy data from A to B and it threw me error stating that the tables already exist and wont go. Now how do I copy data with the keys and identity?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-14 : 17:56:14

Do you just want to copy the entire database? Or is this just for select few tables?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-14 : 18:02:45

You can start import export wizard again. i know sure there is one step you can check to not create the tables but i cannot see now because i am at home.

the second way is
insert database_b.dbo.table_1 (
column_1,
column_2,
...
)
select
column_1,
column_2,
...
from database_a.dbo.table_1
--> but don't take the identity-column or you have to set identity_insert on

Greetings
Webfred

Too Old to Rock 'n' Roll, Too Young to Die

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-14 : 18:35:53

Here is what you need to do:

Use export/Import wizard with Enable identity insert and you should be good. For keys and constraints, Script from source server and run it in destination server.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-14 : 23:18:37

Use ssis copy objects task, it can copy everything in the table.

Raj_Mahendran
Starting Member

14 Posts

Posted - 2008-08-15 : 09:51:38

I need to copy selected tables from Dev to QA which are different boxes. The wizard doesnt have any options to change.. it just has next next next... only the generate scripts has such options.

Wenfred, I was looking for such option which states not to create target tables but there is no such option.

Sodeep, i dont know where to set "enable identity insert" during the import export from Mangement studio 2005.

Raj_Mahendran
Starting Member

14 Posts

Posted - 2008-08-15 : 09:54:57

Are you guys saying about Business Intelligence ---> SSIS --> Import export? I meant there are no options in Management Studio.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-15 : 10:07:08

In Import/Export Wizard comes up a Screen "Select Source Tables and Views".
In this Screen you can choose the tables that you want to copy.
And exactly there is a Button [Edit...] for each table.
Click on [Edit...] and you will see/make the desired settings.

Greetings
Webfred

Too Old to Rock 'n' Roll, Too Young to Die

Raj_Mahendran
Starting Member

14 Posts

Posted - 2008-08-15 : 10:32:18

Thanks Webfred. I got it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-15 : 10:49:45

Choose Edit-Append-Enable identity insert. Thats it.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-16 : 18:31:29

That doesn't copy keys, constraints nor identity properties over as OP wanted.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-16 : 18:44:39

quote:
The tables were created successfully with keys and identity.

first post

Too Old to Rock 'n' Roll, Too Young to Die

Edit: I'm too dumb to write quote...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-16 : 18:47:42

OP created them with script. Here is last sentence in first post:

Now how do I copy data with the keys and identity?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-16 : 18:55:51

Yeah!
You can interpret what you like.
Never mind, i think op is happy now.

Greetings
Webfred

Too Old to Rock 'n' Roll, Too Young to Die

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-17 : 00:56:00

quote:
Originally posted by rmiao

That doesn't copy keys, constraints nor identity properties over as OP wanted.

I know that.Did you see the post I made in 08/14/08 18:35:53 before you ask? You don't see everything before you post.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-17 : 21:59:04

What I don't understand is why tell people script constraints and keys while ssis can copy them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources