Skipping identity column in SQL with insert statement

Posted on 2011-05-12
Last Modified: 2012-05-11
I am using the code below to insert into an SQL table, the first column is an identity field set to populate with an ID but the statement keeps failing(I have typed IDENTITY FIELD where the field is), it needs to be in the syntax below how can I make it skip this field, (I cant write insert into colum1,column2 values etc... as an application produces this code.
insert into dataCharges values(IDENTITY FIELD,'PRINT','2011-05-12 17:16:50',1,'AI80025','','PRIN''d/m/yyyy',0,'2011-05-12 14:37:09.000','2011-05-12 14:37:09.000')

Open in new window

Question by:sykotex
    LVL 1

    Accepted Solution

    First, explicitly name the columns in the INSERT

    INSERT INTO DATACHANGES(action,date,bit...)



    Do you expect this to insert



    Did you want


    which would be

    LVL 40

    Expert Comment

    You need to explicitly mention all the names except the identity column in the INSERT clause. Otherwise, use dynamic sql.
    LVL 3

    Expert Comment

    ya, i agree with above.. check the Identity column is set to running no.. if yes, write the query as below

    insert into dataCharges values('PRINT','2011-05-12 17:16:50',1,'AI80025','','PRIN','d/m/yyyy',0,'2011-05-12 14:37:09.000','2011-05-12 14:37:09.000')

    Open in new window

    some thing, i m not getting on this value


    whether, these are two different values ('PRIN','d/m/yyyy') or same value ?
    If same then place a comma like above...

    Note : 'd/m/yyyy' this value can be only inserted to other than Datetime datatype column
    LVL 1

    Author Comment

    I cannot change the statement just the values. So cannot explicitly mention the columns
    LVL 1

    Expert Comment

    The point is to make 100% sure you have specific the number of needed values to columns.

    We pointed out:


    Something looks wrong here, and we've posted potential solutions. Secondly, have a look at the DB again and make sure that "Identity" column is properly setup as an Identity:

    In SQL Server > Design Table > click column > column properites > Identity Specification = Yes, (Is Identity) = Yes
    LVL 1

    Expert Comment

    Try using dynamic SQL as follows:

    DECLARE strSQL AS Varchar(500)

    Set @strSQL = "insert into dataCharges(action, date, bit, <add other fields>)  values('PRINT','2011-05-12 17:16:50',1,'AI80025','','PRIN','d/m/yyyy',0,'2011-05-12 14:37:09.000','2011-05-12 14:37:09.000')"

    EXEC strSQL
    LVL 5

    Expert Comment

    As per my understanding to your question...

    You  are generating identity column field values (i.e. unique ID) and inserting from application.
    So, you cannot give IDENTITY "true" to Database table column in which you are going to insert identity value.

    Either you can insert via application or via database.

    Hope this helps!

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now