Invalid object name 'Address'. Help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Invalid object name 'Address'. Help

Author

Topic

StealthRider
Starting Member

13 Posts

Posted - 2009-05-20 : 13:09:31

Hello,
I'm trying to create my first table. I have created a table called Address with the following colums:

First Name, Last Name, Address, City, State, Zip.

I'm trying to insert the following Query into the table Address:

INSERT INTO Address (FirstName, LastName, Address, City, State, Zip)
VALUES ('John','Smith', '5005 waterrider Dr','San Diego','CA','92115')

Get the following Error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Address'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 13:13:53

does the table exist? are you sure you created it? also was this created in your default schema?

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-20 : 13:17:51

This may be a stupid question, but are you connecting to your database in Management Studio? Sometimes it will default to master.

Some days you're the dog, and some days you're the fire hydrant.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-05-20 : 13:19:48

Also, look for typos. Is the table name spelled correctly? Is the column spelled correctly? The error message does not indicate WHICH Address is invalid.

By the way, I recommend avoiding having columns and tables named the same (Address and Address). I would suggest something different such as naming the column StreetAddress instead.

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

StealthRider
Starting Member

13 Posts

Posted - 2009-05-20 : 14:36:52

This is what I have so far:

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-20 : 14:41:44

quote:
Originally posted by Skorch

This may be a stupid question, but are you connecting to your database in Management Studio? Sometimes it will default to master.

Some days you're the dog, and some days you're the fire hydrant.

So, are you connected to the correct database per Skorch's question?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 14:46:06

try like this and see


USE Addresses
GO

INSERT INTO Address (FirstName, LastName, Address, City, State, Zip)
VALUES ('John','Smith', '5005 waterrider Dr','San Diego','CA','92115')

StealthRider
Starting Member

13 Posts

Posted - 2009-05-20 : 14:48:44

Yes, all of a sudden it seems to be working I get:

(1 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 14:51:21

so that was your problem. you were not running it in correct db. in future either make sure you select correct db in top dropdown else include use as i suggested

StealthRider
Starting Member

13 Posts

Posted - 2009-05-20 : 14:59:56

OK, Thanks for all the help. I Guess I better ask this question now that I have this under my belt. I'm sure I will need to know this.

I have created 1 row in my table. Lets say I need to add a total of 10 people. How would i add the other 9 to this:

Lets say I wanted to add:

Brenda Jones 5005 waterrider Dr San Diego, CA. 92115

INSERT INTO Address (FirstName, LastName, StreetAddress, City, State, Zip)
VALUES ('Mike','Jones', '5005 waterrider Dr','San Diego','CA','92115')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 15:03:42

you need to add 9 more insert statements like:-


INSERT INTO Address (FirstName, LastName, StreetAddress, City, State, Zip)
VALUES ('Brenda','Jones', '5005 waterrider Dr','San Diego','CA','92115')
.... other 8 rows
 

or just use insert...select

INSERT INTO Address (FirstName, LastName, StreetAddress, City, State, Zip)
SELECT 'Mike','Jones', '5005 waterrider Dr','San Diego','CA','92115' UNION ALL
SELECT 'Brenda','Jones', '5005 waterrider Dr','San Diego','CA','92115' UNION ALL
...next 8 rows

StealthRider
Starting Member

13 Posts

Posted - 2009-05-20 : 15:34:04

I notice that there are no Columns Names and also Mike Jones don't show up. Can someone explain.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-21 : 12:42:01

Your syntax for inserting multiple rows in one statement is wrong. It should be like this:

INSERT INTO Address (FirstName, LastName, StreetAddress, City, State, Zip)
SELECT 'Mike','Jones', '5005 waterrider Dr','San Diego','CA','92115' UNION ALL
SELECT 'Brenda','Smith', '205 Nelson Ave','San Diego','CA','92020' UNION ALL
SELECT 'Tim','Gilliam', '104 Gambrell ST','Greemwood','SC','29646'

Note that after each SELECT you need the UNION ALL command, except for the last record.

Some days you're the dog, and some days you're the fire hydrant.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-21 : 12:43:50

Also another thing you may want to get into a habit of when creating tables is creating an ID column for each table such as Address_ID in this case. Research IDENTITY columns - it'll save you much pain later.

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources