Post reply

create table from existing table

  • January 6, 2006 at 12:46 pm

    #166547

    Hello

    I want to create new table from existing table with all related constarints, rule etc. I know I can do this with following

    select *

    into <other table name>

    from <tablename>

    where 1=2

    But it only copy structure it wont any key constraints liek primary lkey/foreign key, defaults and rules etc.

    I know I can create/generate script from Enteprise manager, But I wnat to ceate dynamically bypassing table name.

    Is this possible?

    Ashsih

     

  • January 6, 2006 at 1:35 pm

    #613387

    Unless the table definition changes a lot, script out the table creation using EM. Then dynamically build a SQL statement, except replace the table name with a variable. Then execute the SQL statement. For instance (this isn't in a stored procedure but it could be:

    DECLARE @tablename sysname
DECLARE @returnstatus int
SET @tablename = 'MyTable' -- name table what you want

DECLARE @sql nvarchar(4000)
SET @sql = 'CREATE TABLE [' + @tablename + '] (MyID int ...)'  -- put your scripted create table here
EXEC @returnstatus = sp_executesql @sql

IF (@result = 0)
  BEGIN
    PRINT 'Table [' + @tablename + '] created successfully.'
  END
ELSE
  BEGIN
    PRINT 'Error! Table [' + @tablename + '] was not created.'
  END

    K. Brian Kelley
    @kbriankelley

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply