INSERT INTO - Help - "invalid object name"

  • quote:


    Try SELECT COUNT(*) FROM TABLEB and SELECT COUNT(*) FROM TABLEA and see if either gives you an invalid object return, that should narrow which it is.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)


    ISSUE 1 - ANTARES686: Your suggestion is getting close to solving the issue. It does appear to be a path issue as I stated in past responses. When I highlight the "specific" table and access the QA for that specific table, and run the Select Count(*), it does count the columns. This procedure works "positively" for either table, as long as I highlight the specific table and run the QA from that specific table. In order for me to receive the column count, I must be in that specific table. In addition, if I run the INSERT INTO statement for the specific highlighted table, only the table that I am now working-in with the QA returns no message error, and the table I am NOT working-in pertaining to the QA returns the "invalid object name." That leads me down the "path issue." If I open the QA under databases, then I receive an "invalid object name" for both tables pertaining to the INSERT INTO statement. Hopefully, I have made the following statements clear. If anything is not clear, then I will further define my interpretation.

    ISSUE 2: EL.C: The bracket [] issue did not resolve the problem.

    ISSUE 3: ALLEN_CUI: I do not see where deleting or renaming a table will resolve anything, unless I have a corrupted table. All test indicate the table is not corrupted. Further, I cannot delete or rename the tables at this stage, since that would create more problems in the future. I would use this only as a last resort when all other avenues have failed, and I would probably just seek other alternatives.

    Roy A. Day (royaday@hotmail.com)

  • quote:


    Try either delete or rename the target table and run select * into targettable from originaltable.


    ISSUE 1 - ANTARES686: Your suggestion is getting close to solving the issue. It does appear to be a path issue as I stated in past responses. When I highlight the "specific" table and access the QA for that specific table, and run the Select Count(*), it does count the columns. This procedure works "positively" for either table, as long as I highlight the specific table and run the QA from that specific table. In order for me to receive the column count, I must be in that specific table. In addition, if I run the INSERT INTO statement for the specific highlighted table, only the table that I am now working-in with the QA returns no message error, and the table I am NOT working-in pertaining to the QA returns the "invalid object name." That leads me down the "path issue." If I open the QA under databases, then I receive an "invalid object name" for both tables pertaining to the INSERT INTO statement. Hopefully, I have made the following statements clear. If anything is not clear, then I will further define my interpretation.

    ISSUE 2: EL.C: The bracket [] issue did not resolve the problem.

    ISSUE 3: ALLEN_CUI: I do not see where deleting or renaming a table will resolve anything, unless I have a corrupted table. All test indicate the table is not corrupted. Further, I cannot delete or rename the tables at this stage, since that would create more problems in the future. I would use this only as a last resort when all other avenues have failed, and I would probably just seek other alternatives.

    Roy A. Day (royaday@hotmail.com)

  • quote:


    Hi

    Try to put brackets [ and ] around your table name. If the name contains spaces or is a special word, this error may occur.

    INSERT INTO [dstTable] (col1, col2, ...) SELECT col1, col2, ... FROM [dstTable]...

    Best regards

    el.c.

    myLittleTools.net :: web-based applications

    http://www.mylittletools.net


    ISSUE 1 - ANTARES686: Your suggestion is getting close to solving the issue. It does appear to be a path issue as I stated in past responses. When I highlight the "specific" table and access the QA for that specific table, and run the Select Count(*), it does count the columns. This procedure works "positively" for either table, as long as I highlight the specific table and run the QA from that specific table. In order for me to receive the column count, I must be in that specific table. In addition, if I run the INSERT INTO statement for the specific highlighted table, only the table that I am now working-in with the QA returns no message error, and the table I am NOT working-in pertaining to the QA returns the "invalid object name." That leads me down the "path issue." If I open the QA under databases, then I receive an "invalid object name" for both tables pertaining to the INSERT INTO statement. Hopefully, I have made the following statements clear. If anything is not clear, then I will further define my interpretation.

    ISSUE 2: EL.C: The bracket [] issue did not resolve the problem.

    ISSUE 3: ALLEN_CUI: I do not see where deleting or renaming a table will resolve anything, unless I have a corrupted table. All test indicate the table is not corrupted. Further, I cannot delete or rename the tables at this stage, since that would create more problems in the future. I would use this only as a last resort when all other avenues have failed, and I would probably just seek other alternatives.

    Roy A. Day (royaday@hotmail.com)

  • Are you sure you are in the correct database.

    If you run Query analyser whilst the databases node is selected in QA then the database you will be connected to will be your default database (master is default). If you want to run it in master then you have to qualify the table with the databasename i.e MyDB.dbo.MyTable

    Or change the database to your database in the combo box on the toolbar

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • quote:


    Are you sure you are in the correct database.

    If you run Query analyser whilst the databases node is selected in QA then the database you will be connected to will be your default database (master is default). If you want to run it in master then you have to qualify the table with the databasename i.e MyDB.dbo.MyTable

    Or change the database to your database in the combo box on the toolbar

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    ISSUE 4: simonsabin: I am running the QA in my database file and not the master database file. However, your suggestion of MyDB.dbo.MyTable excited some neurons in my cerebrum pertaining to SQL "nomenclature," as associated with the SQL "path," and I have now resolved the problem. It was a SQL "nomenclature" and SQL "path" problem.

    Thank you, and all the sqlservercentrial.com individuals, including but not limited to, antares686, who sought to assist me on solving this problem.

    Once again, thank you all. YOU MADE MY DAY!

    WE HAVE LIFT-OFF; ALL SYSTEMS ARE GO!

    Thank you ALL!

    Roy A. Day (royaday@hotmail.com)

  • CASE CLOSED. THANK YOU.

    Roy A. Day (royaday@hotmail.com)

  • Just for kicks, in Query Analyzer, navigate to the database where these tables reside, and execute the following script, but before doing so, replace the text between the single quotes of the two SET statements with the name of your two tables (i.e. the table you're inserting into, and the table you're inserting from):

    ---

     
    
    DECLARE @object1 SYSNAME
    DECLARE @object2 SYSNAME
    SET @object1 = 'Insert name of first table'
    SET @object2 = 'Insert name of second table'
    SET NOCOUNT ON
    SELECT USER_NAME([UID]) + ' owns ' + @object1
    FROM SYSOBJECTS
    WHERE [NAME] = @object1
    UNION
    SELECT USER_NAME([UID]) + ' owns ' + @object2
    FROM SYSOBJECTS
    WHERE [NAME] = @object2
    SET NOCOUNT OFF

    ---

    What are your results?

    Matthew Burr

    Edited by - mdburr on 10/14/2002 09:44:59 AM

    Edited by - mdburr on 10/14/2002 09:45:38 AM

  • Nevermind then. 🙂 Glad you've resolved it.

    Matthew Burr

    Edited by - mdburr on 10/14/2002 09:46:42 AM

Viewing 8 posts - 16 through 22 (of 22 total)

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