CODEWITHSUNDEEP
sqlsql-serversql-insert
n000bster
n000bster

Reputation: 1

New to SQL - Why is my Insert into trying to insert NULL into primary key?

What I want to do is insert a range of dates into multiple rows for customerID=1. I have and insert for dbo.Customer(Dates), specifying my that I want to insert a record into the Dates column for my Customer table, right? I am getting error:

Cannot insert the value NULL into column 'CustomerId', table 'dbo.Customers'

Sorry if I am way off track here. I have looked at similar threads to find out what I am missing, but I'm not piecing this together. I am thinking it wants to overwrite the existing customer ID as NULL, but I am unsure why exactly since I'm specifying dbo.Customer(Dates) and not the existing customerID for that record.

declare @date_Start datetime = '03/01/2011'
declare @date_End datetime = '10/30/2011'
declare @date datetime = @date_Start

while @date <= @date_End
begin
insert into dbo.Customer(Dates) select @date
if DATEPART(dd,@date) = 0
    set @date = DATEADD(dd, -1, DATEADD(mm,1,@date))
else
    set @date = DATEADD(dd,1,@date)
end

select * from dbo.Customer

Upvotes: 0

Views: 1924

Answers (3)

n000bster
n000bster

Reputation: 1

thank you for the feedback. I think I'm scrapping this and going to go with creating a separate table to JOIN. Not sure why I didn't start doing that before

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

The primary key is customerId, but you are not inserting a value.

My guess is that you declared it as a primary key with something like this:

customerId int primary key,

You want it to be an identity column, so the database assigns a value:

customerId int identity(1, 1) primary key

Then, you don't need to assign a value into the column when you insert a new row -- the database does it for you.

Upvotes: 1

Hasan Mahmood
Hasan Mahmood

Reputation: 978

Your Customer table has a column named CustomerId and which column is NOT Nullable so you have to provide that column value as well. If your column type is Int try the bellow code:

declare @date_Start datetime = '03/01/2011'
declare @date_End datetime = '10/30/2011'
declare @date datetime = @date_Start
DECLARE @cusId INT

SET @cusId = 1

while @date <= @date_End
begin
insert into dbo.Customer(CustomerId, Dates) select @cusId, @date
if DATEPART(dd,@date) = 0
    set @date = DATEADD(dd, -1, DATEADD(mm,1,@date))
else
    set @date = DATEADD(dd,1,@date)

    SET @cusId = @cusId + 1;
end

select * from dbo.Customer

Upvotes: 0

Related Questions