Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Posts
    11

    Unanswered: [SQL SERVER 2005] Copy data from one database to another on same server

    Ok, here is my dilemma
    I have an application that has many sites. Each site has it's own database. The databases have common tables (ie the name and fields are the same) What I want to be able to do is when creating a new database, I want to be able to copy certain common table data from one database to another. I have run into an error because the table have an IDENTITY so this is not working

    INSERT INTO Containers SELECT * FROM ADMS2_Indian_Point.dbo.Containers

    I also tried
    USE ADMS2_RSCS
    GO
    SET IDENTITY_INSERT Containers ON
    GO
    INSERT INTO Containers SELECT * FROM ADMS2_Indian_Point.dbo.Containers
    GO
    SET IDENTITY_INSERT Containers OFF
    GO

    I got an error saying that I need to have a column list. I am trying to use this for any tables, so my question is this..
    Is there any way to get around using a column list or is there a way to dynamically create the column list?
    Or, Is there a better way that I should be doing this?

    Please keep in mind I am not a dba and everything I have learned about SQL is from my good pal Google

    Thanks for any help

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Why can't you just write out the column list?

    using select * in anything but throw away code is a no-no.

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    just write the column list as jezemine suggests
    OR
    use "select into" instead of "insert into". remember the table will be created fresh (should not already exists) and will be created without index etc.
    OR
    create necessary common tables with data, index everything in "model" database. every new database u create thereafter will have all those objects ready immediately after creation. no sql needed.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If your server is dedicated to these databases, then I would suggest copying all of the objects and data that you need to the Model database. After that, every database you create will be a copy of model and will have everything you need.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •