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

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: Having a stored procedure copy tables & also preserve indexing
    ... FOREIGN KEY, CHECK, NOT NULL, DEFAULT constraints ... INTO <tablename> doesn't create any of the PRIMARY KEY, UNIQUE, ...
    (microsoft.public.sqlserver.mseq)
  • Disabling Constraint for Bulk Loading
    ... For this i want to disable all the constraints on all the user ... fetch next from c1 into @tablename ... Now when i try to truncate one of the tables (say titles) it gives me ...
    (comp.databases.ms-sqlserver)
  • Re: A real world example
    ... If a constraint is defined in terms of successive states of a database, ... Yes, that's all facts are, though constraints mean that your facts are ... value of a candidate key determines the values of all other attributes, ... The frame of reference for a candidate key is a ...
    (comp.databases.theory)
  • Re: A real world example
    ... If a constraint is defined in terms of successive states of a database, ... facts cannot be thought of just in terms of instances of predicates. ... Yes, that's all facts are, though constraints mean that your facts are ...
    (comp.databases.theory)
  • Re: A real world example
    ... If a constraint is defined in terms of successive states of a database, ... Yes, that's all facts are, though constraints mean that your facts are ... model already allows surrogate keys. ...
    (comp.databases.theory)