SQL Server - Insert into table, values from another table

Question

Firstly, I have read through other posts on this page regarding similar issues but I cannot figure out where I am going wrong. I want to populate a column of [table1] with values from another column in [table2].

When I run,

insert into Staged.dbo.factSales(Date)
    select SaleDate 
    from Staged.dbo.SaleDates

I get the following error

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'SaleValue', table 'Staged.dbo.factSales'; column does not allow nulls. INSERT fails.

SaleValue is the column in Staged.dbo.factSales and design-wise, comes after 'Date' - SaleID, OrderID, ProductID, BranchID, EmployeeID, Date, SaleValue.

Does anyone have any idea where I am going wrong? Thanks in advance!

Marcin

Someone thought it wise to put a not null restriction on the factSales table for the column SaleValue. Change your insert to provide the appropriate saleVale! Askyourself why would that restriction be there, and why are you trying to insert a record without one. Are you doing something wrong by omiting the value or is the restriction no longer valid? Both of these are business questions that only you can really answer. — xQbert
– xQbert, Commented Nov 11, 2016 at 17:04

John Cappelletti · Accepted Answer · 2016-11-11 16:57:04Z

1

SalesValue does not allow nulls, so supply a zero.

Insert into Staged.dbo.factSales (Date,SaleValue)
Select SaleDate,0 from Staged.dbo.SaleDates

answered Nov 11, 2016 at 16:57

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Juan Carlos Oropeza Over a year ago

Or supply '0' if is a string, You also can change your table to allow nulls for that field.

John Cappelletti Over a year ago

@JuanCarlosOropeza Correct. I made an assumption.

MPol Over a year ago

Why do I have to include SaleValue if I want to only want to affect a specific column? Also, SaleValue already contains valuable data so I cant just override it with a meaningless 0

John Cappelletti Over a year ago

@MPol because your fact table does not accept nulls in SalesValue. Without it, you are trying to add records which would have a null value in SalesValue.

MPol Over a year ago

Okay, so my sql command replaces all data, not just insert it?

MPol · Accepted Answer · 2016-11-11 17:41:47Z

0

Fixed using

update Staged.dbo.factSales set Staged.dbo.factSales.Date = Staged.dbo.SaleDates.SaleDate from Staged.dbo.factSales inner join Staged.dbo.SaleDates on Staged.dbo.factSales.SaleID = Staged.dbo.SaleDates.SaleID

answered Nov 11, 2016 at 17:41

MPol

33 bronze badges

Collectives™ on Stack Overflow

SQL Server - Insert into table, values from another table

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related