RE: Having a stored procedure copy tables & also preserve indexing

As I previously stated I *do* understand that SELECT INTO does not create
PRIMARY KEYS etc. I was hoping there would be an alternate way to do
a full blown copy that includes creation of the INDEXES, KEYS etc. For
example there is a stored procedure called SP_RENAME. I was hoping there was
an SP_FULLCOPY or something that might get me what I was looking for.

Is there a way I can automatically pull (from my stored procedure) the
ALTER commands to create the keys etc and execute them on the fly?
That would do it. I have looked into SQL-DMO and Automatically generating
scripts through Enterprise Manager but that doesn't get me what I need.
I have over 80 tables to deal with and would like to create the necessary
ALTER
commands automatically....

Any helpful information you might be able to supply would be GREATLY
appreciated!

-Peter

"Vishal Parkar" wrote:

> actually it creates identity column but not PRIMARY KEY, UNIQUE,
> FOREIGN KEY, CHECK, NOT NULL, DEFAULT constraints
>
> --Vishal.
>
> "Vishal Parkar" wrote:
>
> > SELECT ... INTO <tablename> doesn't create any of the PRIMARY KEY, UNIQUE,
> > FOREIGN KEY, CHECK, NOT NULL constraints and doesn't define DEFAULT and
> > IDENTITY column properties for the new table.
> >
> > You will have to write seperate statements into your stored procedure to
> > create them.
> >
> > --Vishal.
> >
> >
> > "Peter S." wrote:
> >
> > > Hello,
> > >
> > > I created a stored procedure that renames a table to OLD_xxxxx and replaces
> > > that table with another (copy) that resides on a different database. I pull
> > > the
> > > tablename names through the use of a cursor table and construct a SELECT INTO
> > > statement as follows
> > >
> > > 'SELECT * INTO DB1.dbo.' + @tableName + ' FROM DB2.dbo.' + @tableName
> > >
> > > It works great especially since there are 80+ tables that need to be copied
> > > from one database to another. The drawback is that it doesn't preserve the
> > > indexing/foriegn key constraints. Is there a way to do this without having to
> > > deal with DTS or creating additional scripts?? Ideally I would like to
> > > replace
> > > the "SELECT * INTO" statement with something that not only does a copy but
> > > also preserves the indexing! Does such a command exist???? Any help from the
> > > Microsoft guru's would be greatly appreciated!!!!
> > >
> > >
.

Relevant Pages

  • Re: Indexes and Keys
    ... Primary keys and UNIQUE constraints are both enforced via indexes. ...
    (microsoft.public.sqlserver.programming)
  • Re: How should I generate a primary key?
    ... generate primary keys. ... that, these days, primary keys are either an intrinsic part of the ... and ticket numbers to tickets. ... The deli department in the supermaket where I shop assigns sequence numbers ...
    (comp.databases)
  • Re: Whats the best practice for primary keys?
    ... I'm about to start a new project and the decision on primary keys is a ... There is something very 'clean' about using GUIDs, ... This method gets the efficiency of using int keys and allows me to ...
    (microsoft.public.sqlserver.programming)
  • Re: Trying to optimize a query with a bunch of INNER JOINs
    ... how fast is it if you drop out the SUM and the ... > Yes, I do have foreign keys on the main table, as well as primary keys on ...
    (microsoft.public.sqlserver.programming)
  • Re: Trying to optimize a query with a bunch of INNER JOINs
    ... Yes, I do have foreign keys on the main table, as well as primary keys on ... > Pro SQL Server 2000 Database Design - ...
    (microsoft.public.sqlserver.programming)