0

Need assistance on the following. How can I copy data from one SQL column table to another sql column table?

I have the following tables

dbo.Thecat62 and dbo.thecase6

inside dbo.Thecat62 , I need to copy the column Work_Order_Job_Start_Date values to dbo.thecase6 column Job_Start_Date. Currently there are null value in the Job_Start_Date column in dbo.thecase6.

I have tried using the following command

INSERT INTO dbo.thecase6 (Job_Start_Date) SELECT Work_Order_Job_Start_Date FROM dbo.thecat62

but received the error Cannot insert the value NULL into column 'CaseNo', table 'Therefore.dbo.TheCase6'; column does not allow nulls. INSERT fails. The statement has been terminated.

Any help will be great!

Thanks!

James
  • 1
  • 1

2 Answers2

2

Because on this table Therefore.dbo.TheCase6 for CaseNo you have specify Not NULL Constraints something like this

CaseNo int NOT NULL

But you did not select the CaseNo column from the dbo.thecat62 table, so you are explicitly trying to insert nulls into a non-nullable column.

You just need to select the CaseNo column, as well, presuming it does not contain any nulls in teh source table. 

INSERT INTO dbo.thecase6 (Job_Start_Date,CaseNo) 
SELECT Work_Order_Job_Start_Date,CaseNo FROM dbo.thecat62
Craig Tullis
  • 9,939
  • 2
  • 21
  • 21
Dgan
  • 10,077
  • 1
  • 29
  • 51
  • Thank you for your reply. I have added the caseno together with another column called Job_Number as without the job_number, it will also throw out the error Cannot insert the value NULL into column 'Job_Number', table 'Therefore.dbo.TheCase6'; column does not allow nulls. INSERT fails. Hence I have added the job_number but then it shows an error Violation of PRIMARY KEY constraint 'TheCase6PK'. Cannot insert duplicate key in object 'dbo.TheCase6'. Since this is the issue, will anupdate instead of an insert work? How would I do the update query? – James Nov 30 '14 at 09:17
  • Have a look here: http://stackoverflow.com/questions/1202075/advanced-mysql-query-update-table-with-info-from-another-table – Craig Tullis Nov 30 '14 at 09:21
1

The error says it has a column CaseNo which doesn't allow NULL.

Cannot insert the value NULL into column 'CaseNo', table 'Therefore.dbo.TheCase6';

You are inserting rows in the new table which will have just 1 column filled and rest of the columns will be empty

Either Alter the table in which you are inserting the data and allow the column to allow null values. Or if you don't want to allow null values, update the null values to some default values.

Danyal Sandeelo
  • 12,196
  • 10
  • 47
  • 78