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

Question

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

J M-314995

SSCrazy

Points: 2975
More actions
September 5, 2007 at 3:18 pm

#121452

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.

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

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

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 1

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...

Kevin Galbreath-373973 Right there with Babe Points: 759 More actions · Answer 2

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>

J M-314995 SSCrazy Points: 2975 More actions · Answer 3

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.

J M-314995 SSCrazy Points: 2975 More actions · Answer 4

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?

J M-314995 SSCrazy Points: 2975 More actions · Answer 5

J M-314995

SSCrazy

Points: 2975

September 5, 2007 at 4:36 pm

#731641

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

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 6

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...

J M-314995 SSCrazy Points: 2975 More actions · Answer 7

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

Vladan SSC-Insane Points: 22072 More actions · Answer 8

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".

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

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)
Intro to Tally Tables and Functions