SELECT INTO .... ? from one db to another on the same server

  • I already have a script...several actually that pull information for reporting purposes. If I create a db, how do I put the results into that db and the columns keep the same data types?

    I thought about doing a SELECT INTO but the example in books online is less than desirable. I've searched around and I'm not finding the same example (or even a combination of examples) that gives me any sort of help.

    Is SELECT INTO the way to do this?

    I was trying to figure out how to do this with an absolutely blank db I created called cicp and the Northwind db. I know that the following code doesn't do anything with copying the same data type (I don't know how to copy data types). I'm a newbie so go easy on me.

    create table cicp.dbo.NewShippers (Phone NVARCHAR PRIMARY KEY)

    SELECT Northwind.dbo.Shippers.Phone

    INTO cicp.dbo.NewShippers

    FROM Northwind.dbo.Shippers,

    cicp.dbo.NewShippers

    Do I have to manually look up the table properties information to create the same data types for each column or is there an easier way than what I did?

    Before I run the above code I make sure there isn't a table named NewShippers in the cicp db but I'm getting the following error...

    Server: Msg 2714, Level 16, State 6, Line 3

    There is already an object named 'NewShippers' in the database.

    Any suggestions?

    T.I.A.

  • Select into isn't the correct way to insert data if you are going to manually create the table. So in your example either create the table using the first statement then do an 'insert into' or use the 'select into' to automatically create and populate the table.


    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • What you want to do is script the table(s) first.  In Enterprise Manager or SSMS (not sure whether you're using 2000 or 2005), right click on the table you want to move over to your new database.  You'll be able to generate a script to create the table from there ... run the script in the new db and you'll have your table to INSERT INTO.

    As a general rule of thumb, creating a table by selecting into it is a bad idea. 

    So your new insert will look something like..

     

    INSERT INTO <New Table>  (ColA, ColB, etc.) SELECT ColA, ColB, etc. FROM <Existing Table>

     

  • Thanks for the response Ben.

    When I use...

    SELECT Northwind.dbo.Shippers.Phone

    INTO cicp.dbo.NewShippers

    FROM Northwind.dbo.Shippers,

    cicp.dbo.NewShippers

    ...by itself without the create table statement, I get...

    Server: Msg 208, Level 16, State 1, Line 1

    Invalid object name 'cicp.dbo.NewShippers'.

    ------------------------------------

    Thanks for the response Kevin.

    I'm on SQL 2000 and I'm not using SSMS. I haven't had the time to look into 2005 tools yet and because I don't know anything about them, I wasn't sure if it would read SQL 2000 tables without messing anything up. So EM and QA are my tools for now.

    Thanks for your example for the INSERT INTO. It worked great.

  • Here is my last foreseeable question for now...

    When I run these statements....

    DROP TABLE cicp.dbo.NewShippers

    GO

    create table cicp.dbo.NewShippers (Phone NVARCHAR(15) PRIMARY KEY)

    INSERT INTO cicp.dbo.NewShippers (Phone) SELECT Northwind.dbo.Shippers.Phone FROM Northwind.dbo.Shippers

    ....I get this message....

    Server: Msg 3701, Level 11, State 5, Line 1

    Cannot drop the table 'cicp.dbo.NewShippers', because it does not exist in the system catalog.

    (3 row(s) affected)

    How do I drop a User type table? Seems like if this was a system table it would drop just fine....or is that a bad guess?

  • ...and yes that table does exist in the cicp db.

  • give this one a go...

    -----------------------------------------------------------------------------

    USE CICP

    GO

    IF OBJECT_ID(N'dbo.NewShippers') IS NOT NULL

    DROP TABLE dbo.NewShippers

    GO

    CREATE TABLE dbo.NewShippers (

    Phone NVARCHAR(15) PRIMARY KEY

    )

    GO

    INSERT INTO dbo.NewShippers (Phone)

    SELECT Northwind.dbo.Shippers.Phone FROM Northwind.dbo.Shippers

    GO


    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • Thanks for the reply Ben.

    That script worked great after I changed CICP from upper case to lower case.

    Please excuse my ignorance on this question....

    What does the N do in...

    IF OBJECT_ID(N'dbo.NewShippers') IS NOT NULL

  • The "N" means that the string following it should be treated as NCHAR. I think it isn't necessary here (makes no difference), because BOL says:

    "object is either char or nchar. If object is char, it is implicitly converted to nchar".

  • Your database is "case sensitive"?????

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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