SQL Server : copy the whole column to another column error

Question

I am trying to move all the data from fælge column in the test table into the fælgen column in table test to with this query

 INSERT INTO [dbo].[test2] ([Fælgen]) SELECT Fælge FROM [dbo].[test];

but I am getting a error with it saying that it cant insert the null into column ET which is not that column i am trying to insert my data to

Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'ET', table 'OminiData.dbo.test2'; column does not allow nulls. INSERT fails.

You need to provide data for all non-null columns (that do not have a default value) in the table — James Z
– James Z, Commented Jun 18, 2016 at 15:20
Do you really mean to INSERT new rows with just that one column filled into the table (which obviously doesn't work)?? Or did you rather mean to UPDATE existing rows with a new value for that one column?? Also, word of advice: you should NOT use special characters like æ in your table / column names - stick to 7-bit ASCII for those names. Otherwise, sooner or later, you'll run into problems ..... — marc_s
– marc_s, Commented Jun 18, 2016 at 15:43

marc_s · Accepted Answer · 2016-06-18 18:06:05Z

While you are going to Insert data to table you need to make sure that all the constraints are passed and nothing violate them.

You need to make sure that all Non-Null columns have value while inserting the new record. In fact you can not Insert value to single column in a table without considering the constraints and providing value for non-null-able columns.

Finally to overcome this error you have two choice:

Update the destination column(If there is a one-one or one-many relation between Test and Test2, This will need using Update based on Join, other wise a simple Update will be the solution)

Provide non-null value for ET

 INSERT INTO [dbo].[test2] ([Fælgen], [ET]) SELECT Fælge , '' as ET FROM [dbo].[test];

gofr1 · Accepted Answer · 2016-06-18 17:47:18Z

Press right mouse button on the table, chose Script table as... -> INSERT. There will be a script template for insertion. All columns mentioned in this script should have values determined by there datatypes.

Community · Accepted Answer · 2017-05-23 12:14:46Z

I was working on same error . found this from this link SQL Server: copy data from one column to another column?

Using Merge I was able to copy data from one column to another in two different table. Make sure data type for both columns need to get copied, and row count for both tables.

korolar · Accepted Answer · 2016-06-18 15:25:27Z

You cannot insert values to a single DB columns. You always insert entire rows. So your statement doesn't really mean insert some values to this column. What it really means is insert some values to this column and null/default values to all other columns. If any of those other columns does not allow nulls and doesn't have a non-null default, insert will fail.

Collectives™ on Stack Overflow

SQL Server : copy the whole column to another column error

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related