CODEWITHSUNDEEP
sqlsql-server
user1632718
user1632718

Reputation: 183

Insert query with Auto number

Hi I have a query as fallows

INSERT INTO CPQ
            (BackLog_ID,
             Priority,
             Category,
             Type,
             Country,
             Region,
             TN,
             [Date Entered],
             Source,
             Brand,
             [Remote #],
             [Target #],
             Device,
             Status,
             [Capture Type],
             Comment,
             Processed)
SELECT BackLog_ID,
       Priority,
       CASE
         WHEN Type = 'I' THEN 'Category-1'
         WHEN TYPE = 'P' THEN 'Category-1'
         WHEN TYPE = 'B' THEN 'Category-2'
       END AS Category,
       Type,
       Country,
       Region,
       TN,
       [Date Entered],
       Source,
       Brand,
       [Remote #],
       [Target #],
       Device,
       Status,
       [Capture Type],
       Comment,
       Processed
FROM   BackLog
WHERE  ( Processed = 0 )
       AND ( Type <> 'Z' )
       AND ( Region = 'Asia' )
       AND ( Country = 'China'
              OR Country = 'Japan' )

In above Query The Table CPQ has PK As CPQ_ID and its not auto number. Now the above query is giving error as follows

Error :

Cannot insert the value NULL into column 'CPQ_ID', table 'Capture_Manager.dbo.CPQ'; column does not allow nulls. INSERT fails.

Plz. Help ME

Upvotes: 1

Views: 2841

Answers (3)

Kas
Kas

Reputation: 3923

The problem is you are trying to insert null value into CPQ_ID column, The best way is to set CPQ_ID column to auto increment ,

Sample table create code with Auto Increment enabled 

CREATE TABLE [dbo].[tablename](
    [CPQ_ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

This code " IDENTITY(1,1) NOT NULL" always insert auto incremented number to the column whenver you insert a record

However if you need not auto-increment value then you have to parse a value to CPQ_ID column or you have to make it nullable which means you cann't use it as a primary key.

Upvotes: 0

oerkelens
oerkelens

Reputation: 5161

1) You have a PK column, which is not auto-increment

2) You try to insert something to that table without assigning a value to your PK

3) you get an error that says you cannot do that

I am not really sure what more you need for a solution, but since 3) is the result of 1) and 2), you will have to change 1) or 2).

So either:

1) Make your PK column auto-increment (or otherwise fill it automatically on DB level)

or

2) Insert a (unique!) value for you PK when executing your insert statement.

You do either of the two, your error will disappear.

Upvotes: 2

Damodaran
Damodaran

Reputation: 11047

If the field is PK and since it not auto increment you should specify values for that field.

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

Check SQL PK and sql_autoincrement

Upvotes: 0

Related Questions