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

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: Unique fields in table design
    ... Otherwise, the multi-column primary key ... The so-called 'surrogate key' ... Using UNIQUE or PRIMARY KEY constraints for ... INSERT INTO SalaryReviews (employee_nbr, salary_start_date, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: writing a Delete trigger on a table for cascading effect
    ... "But our developers out here do not beleive in primary key and foreign key ... constraints so putting constraints on tables is ruled out" ... You get a trigger that fires on a delete only. ...
    (microsoft.public.sqlserver.programming)
  • Re: Do all tables have to have a primary key?
    ... to look beyond Access/Jet's 'index' flavour of constraints (Jeff, ... required to ensure no duplicate data). ... Because PRIMARY KEY is implemented in Jet using an index, ... Appearing as bold text in the Relationships diagram; ...
    (microsoft.public.access.tablesdbdesign)
  • Naming conventions for special database objects
    ... I am currently doing a review of my personal database naming ... InsteadOfInsert_Orders for multi-purposed triggers. ... Default constraints ... How to name a primary key if not primary key? ...
    (comp.databases)
  • Re: Primary Key and Unique
    ... placing the two fields in the PRIMARY KEY was exactly wat I ... >> Thanks, but when I specify two primary keys, I get an error that is not ... > Constraints can be column constraints or table constraints: ... > A table constraint is declared independently from a column definition ...
    (microsoft.public.dotnet.framework.adonet)