You must Sign In to post a response.
  • Category: SQL Server

    Invalid column ID in my sql query

    My query as follows

    Insert INTO Accounts_Entity(ID,Entity,Address1,Address2,Address3,Address4,Address5,Signature)
    SELECT MAX(CAST(ID as INT)) + ,'HIMT','HIMT2','HIMT3','HIMT4','HIMT5','HIMT6','off'
    Accounts_Entity

    When i run the above sql query shows error as follows
    Invalid column name 'ID'

    Table structure as follows (Table Name Accounts_Entity)

    Field Datatype

    ID bigint
    Entity varchar(50)
    Address1 varchar(50)
    Address2 varchar(50
    Address3 varchar(50)
    Address4 varchar(50)
    Address5 varchar(50)
    Signature Varchar(50)

    please help what is the mistake in my above sql query.

    Regards,
    Narasiman P.
  • #761885
    Hi
    Rao

    2 Major Reason for this issue
    Invalid column name 'ID'

    1. Check in your table id column have or not check your table structure .

    2.your table you mention this

    ID bigint

    But your insert query

    SELECT MAX(CAST(ID as INT))

    int so check this 2 things

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #761886
    Hi
    Rao

    You have post more than questions but people search and reply.
    I asked more then time this statement to you.
    If you fixed in your issue means select as best answer thats helpful to others then grow in our site.
    if not fixed in your issue let me know.

    I have mention code follow them. This is working for me.
    Create Structure this

    Create Table Accounts_Entity
    (
    ID bigint,
    Entity varchar(50),
    Address1 varchar(50),
    Address2 varchar(50),
    Address3 varchar(50),
    Address4 varchar(50),
    Address5 varchar(50),
    Signature Varchar(50)
    )

    Your Query i changed this try this Query

    Insert INTO Accounts_Entity(ID,Entity,Address1,Address2,Address3,Address4,Address5,Signature)
    values((SELECT MAX(CAST(ID as INT)) from Accounts_Entity),'ENT1','ADRS1','ADRS2','ADRS3','ADRS4','ADRS5','off')

    I have attached image given below

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

    Delete Attachment

  • #761904
    Are you connecting correct database?
    Do you have the colum ID in Accounts_Entity?

    By Nathan
    Direction is important than speed

  • #761907
    Hi,
    As Jaykumar has shared a insert query for your database, you may use as it is.

    By the way, there were some mistakes in your Insert statement which is causing problem.

    Correct format of SQL Insert statement is like below:

    //Inserting within fixed columns only
    INSERT into tableName(Field1,Field2) VALUES (Value1, Value2);
    //Inserting in all columns
    INSERT into tableName values (Value1, Value2, Value3, Value4);

    You have missed VALUES keyword in your insert statement as well as while joining ID and other parameters you have not used correct formatting which is treating it as false query.

    -------------
    Glad to be,
    John Bhatt
    Editor - DNS Forums
    https://www.pyarb.com

  • #761913
    Hi,

    In your Query there is some issues in select Query, please recheck those.


    Insert INTO Accounts_Entity(ID,Entity,Address1,Address2,Address3,Address4,Address5,Signature)
    SELECT MAX(CAST(ID as INT)) + ,'HIMT','HIMT2','HIMT3','HIMT4','HIMT5','HIMT6','off'
    Accounts_Entity


    In the above query I found few issues please look in to those.

    1) MAX(CAST(ID as INT)) + is there, what it mean "+" sysmbol over there..?

    2) "Accounts_Entity" is your table after column declaration completed you need to use "from" keyword to call tablename.

    Syntax: select col1,col2... from tablename

    But in your case you missed from keyword. So, that is the reason your getting above error.

    Please execute your select query alone before insert it into database, if that is fine obviously it will insert into your new table without issues.

    Please recheck and let me know.

    Hope this will helpful to you..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #761927
    Hello Rao,

    Invalid Column ID means that the column name ID does not exists in the table. You must check that you have selected the correct database or table than try again.

    Hope this will help you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"


Sign In to post your comments