Re: copy table to another database

Danny,

You can create a table with all of its contents by the following command:

SELECT * INTO NewDatabase.dbo.NewTable FROM OldDatabase.dbo.OldTable

However, this will not recreate indexes, constraints, and so forth. So, a
more complete method would be to:

First, in the Object Explorer, right click on the table in question and
Script Table as Create. This will give you a create script wtih indexes,
constraints, etc. (If you do not get all the components you need, check
your scripting options to make sure that nothing you need is turned off.)

Second, use that script to create the table in your new database.

Third, run code like this:

INSERT INTO NewDatabase.dbo.NewTable (Column1, Column2, ... etc )
SELECT Column1, Column2, ... etc
FROM OldDatabase.dbo.OldTable

If you have an IDENTITY column defined, then consider whether you want to
keep the old values (SET IDENTITY_INSERT ON) or generate new values (leave
the identity column our of the column lists).

RLF

"Danny" <lane.dj@xxxxxxxxx> wrote in message
news:uUWaCxHJIHA.4584@xxxxxxxxxxxxxxxxxxxxxxx
Hi folks

Trying to copy a single table from database (contains many tables) to
another database (new DB) with just that one table. Trying to do this with
sql statements if possible?

Any help would be appreciated.

Dan



.

Relevant Pages

  • Re: Change field collation
    ... constraints and statistics on the columns before you can alter ... There are quite a lot of caveats and you use this script at your own risk. ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.programming)
  • Re: Changing collation -- URGENT
    ... You can use the following script to change the collations of your columns, ... duplicate primary key constraints create statements, ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.server)
  • Re: moving indexes
    ... I can easily write the code to generate all the "add constraints" and "create unique index" statements. ... and letting a unique index be added by the second sql file. ... Is there a better way to do this without dropping the database? ... instead of running dostats you can have myschema generate an update statistics script to duplicate the existing level of stats. ...
    (comp.databases.informix)
  • Re: moving indexes
    ... I can easily write the code to generate all the "add constraints" and "create unique index" statements. ... and letting a unique index be added by the second sql file. ... Is there a better way to do this without dropping the database? ... instead of running dostats you can have myschema generate an update statistics script to duplicate the existing level of stats. ...
    (comp.databases.informix)
  • [NEWS] IBM Informix Web DataBlade Local Root by Design
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... that ease development of "intelligent", interactive, Web-enabled database ... person who has access to change the Perl script. ...
    (Securiteam)
Loading