September 5, 2007 at 3:18 pm
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.
September 5, 2007 at 3:36 pm
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.
Ben Sullins
bensullins.com
Beer is my primary key...
September 5, 2007 at 3:37 pm
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>
September 5, 2007 at 4:31 pm
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.
September 5, 2007 at 4:35 pm
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?
September 5, 2007 at 4:36 pm
...and yes that table does exist in the cicp db.
September 5, 2007 at 4:39 pm
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
Ben Sullins
bensullins.com
Beer is my primary key...
September 5, 2007 at 6:26 pm
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
September 6, 2007 at 1:03 am
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".
September 6, 2007 at 6:45 am
Your database is "case sensitive"?????
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply