Insert & Select

Question

I am trying to create a column EAC into Projectdetails. Table 1 : [Projectdetails] Table 2 : ['RAC']

 (SELECT b.[EAC]  FROM [dbo].[Projectdetails] a 
  INNER JOIN [dbo].['RAC'] b ON a.[ProjectKey]= b.[Project _ID])

works fine and return me 421 rows.

However the following query does not work :

  Insert into [dbo].[Projectdetails]([EAC])
  (SELECT b.[EAC]  FROM [dbo].[Projectdetails] a 
   INNER JOIN [dbo].['RAC'] b ON a.[ProjectKey]= b.[Project _ID])

returns me Cannot insert the value NULL into column ProjectKey

Just to be sure I understand - you're attempting to add and populate the `EAC` column to your existing rows, right? — Mureinik, Dec 27 '14 at 16:10
Try running `(SELECT b.[EAC] FROM [dbo].[Projectdetails] a INNER JOIN [dbo].['RAC'] b ON a.[ProjectKey]= b.[Project _ID] WHERE b.EAC IS NULL)`. If you find at least one record then there is your issue — dotnetom, Dec 27 '14 at 16:10

score 0 · Accepted Answer · answered Dec 27 '14 at 16:10

0

I suspect you really want an update:

update pd
    set EAC = r.EAC
    from [dbo].[Projectdetails] pd join
         [dbo].['RAC'] r
         on pd.[ProjectKey] = r.[Project _ID]);

By the way, putting single quotes in a table name seems like a really bad idea.

answered Dec 27 '14 at 16:10

Gordon Linoff

1,242,037
58
646
786

Msg 8152, Level 16, State 2, Line 1 String or binary data would be truncated. The statement has been terminated. – SunnyKP Dec 27 '14 at 16:23
@SunnyKP . . . You need to check the data types for the two `EAC` columns. It would appear that they are different. You have to decide what to do when the value from one table doesn't fit in the other table. – Gordon Linoff Dec 27 '14 at 17:01
This code helped. The data types were different and when I put both of them to same and it fixed. – SunnyKP Dec 27 '14 at 17:15

score 0 · Answer 2 · edited May 23 '17 at 10:26

I guess, ProjectKey is a primary key as it's NOT NULL. So when you are trying to insert new row in this table without providing any specific value for this column, so sqlserver is trying to add NULL as a default value. But as this is NOT NULL column it's giving you error.

The best solution to make it work is make this column as auto-increment column. Like this

Sql Server add auto increment primary key to existing table

SQL Server, How to set auto increment after creating a table without data loss?

Insert & Select

2 Answers2