How do I export tables with keys & indexes in SQL 2005 Mgt Studio?

cricketman
cricketman used Ask the Experts™
on
I am trying to export several tables from one database to another using the Import/Export Wizard in SQL 2005 Management Studio (I'm not a programmer!).  I can export the tables fine, but they don't have the primary keys & indexes once they land in the destination database.

I found an old question here on EE that described generating a script with the source database, then running it on the destination database before I export the tables into it, but I couldn't make that work (got errors, not sure I really grasped the whole process).

Any EASY way to do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
why dont you try backup restore method, that ill give you a duplicate of your database

Author

Commented:
I don't want the whole database - only a few tables.  I don't know of a way to choose only certain tables when performing a backup/restore - can that be done?
Two step process:
1. Script the objects you want, including indexes. Build those objects in the destination database.
2. Use the Import/Export utility to move the data.
Unfortunately, when you move the data and the destination table does not exist, it only creates the table, no indexes or keys.
HoggZilla
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
OK, trying to walk myself thru this, could use a little more detailed assistance, please.  
Here's what I'm doing (in SQL Mgt Studio):
1) create new empty database
2) r-click on existing dbase (that I want to copy indexes/keys from)
3) Tasks > Generate Scripts
4) select dbase I want to generate them FROM, right?
---   Do I select "script all objects"?
5) In the "Choose script options" dialog, what items do I set to TRUE?
6) In the "Choose object types" dialog what do I check?  (choices are: roles, defaults, schema, procedures, tables, udf's, users, views)
7) in the "Output option" I chose to script to a FILE (thinking it would save a sql script to this file that I would then run against the empty database I created), but when I then opened the saved file in Mgt Studio it was empty.

Need help please.....
8)

Author

Commented:
Can anyone confirm the details in my comment above please?

Thanks in advance!
AneeshDatabase Consultant
Top Expert 2009
Commented:
>---   Do I select "script all objects"?
you said you just need to script few tables, in that case select only the tables you need
>5) In the "Choose script options" dialog, what items do I set to TRUE?
all the keys (primary keys, foreign keys.. ). Indexes, triggers ..
>6) In the "Choose object types" dialog what do I check?  (choices are: roles, defaults, schema, procedures, tables, udf's, users, views)
choose the tables you need and the sps and functions which depends on those tables
 

Author

Commented:
I decided to give up on this... and the B grade is b/c don't feel I got quite as detailed and accurate asssistance as I have in the past here on EE.  I do appreciate your trying though.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial