How to insert the data from one column into anothe

问题:

I have been struggling with this issue for some time at my job and I need some help.

I have 2 tables, one with a ton of information and other where part of the information should be, i need to move a single column to a table already created with some codes to identify every row that is going to be inserted (mostly users) and it throws an error because it says that I must insert an id also, but the id already are there.

let's say I have these tables:

table_information
admin_name|admin_address|admin_phone|admin_mail
john      |home         |111111111  |[e-mail]
pete      |house        |222222222  |[e-mail]
mike      |crib         |333333333  |[e-mail]
ryan      |someplace    |444444444  |[e-mail]
daniel    |somewhere    |555555555  |[e-mail]
bruce     |anywhere     |666666666  |[e-mail]
dave      |everywhere   |777777777  |[e-mail]


table_admin
admin_id|admin_name
1       |
2       | 
3       |
4       |
5       |
6       |
7       |

and I need to move the column of the user names to the table users that has the codes so the result must be something like this:

table_information
admin_name|admin_address|admin_phone|admin_mail
john      |home         |111111111  |[e-mail]
pete      |house        |222222222  |[e-mail]
mike      |crib         |333333333  |[e-mail]
ryan      |someplace    |444444444  |[e-mail]
daniel    |somewhere    |555555555  |[e-mail]
bruce     |anywhere     |666666666  |[e-mail]
dave      |everywhere   |777777777  |[e-mail]

table_admin
admin_id|admin_name
1       | john
2       | pete
3       | mike
4       | ryan
5       | daniel
6       | bruce
7       | dave

the information is coming from a big database (around 1500 rows) where the admin names are repeated, therefore I have been using the select distinct to filter some of the duplicate rows.

The way I've been doing it is like this:

INSERT INTO table_admin (admin_name)
SELECT DISTINCT admin_name FROM table_information

And i obtain this:

Cannot insert the value NULL into column 'admin_id' column does not allow nulls. INSERT fails.

Also important is that the column admin_id from the table table_admin could not be identity (or auto increment) so the id's could not be automatically generated (I would have done it this way, but it's the way they work here).

Am I lacking something? doing something wrong? Let me know some suggestions, or solutions for this problem that is driving me crazy please.

Thanks in advance for reading.

回答1:

Cannot insert the value NULL into column 'admin_id' column does not allow nulls. INSERT fails.

The reason you get this error is because admin_id is not NULLABLE meaning you must have an admin_id for every row. When you perform an INSERT you must explicitly insert the admin_id since it isn't an IDENTITY property column.

Your expected results show that you want to do an UPDATE, not an INSERT. A UPDATE would UPDATE the admin_id currently in your table_admin table instead of inserting new rows. The one thing that is unclear is how the table_admin relates to table_information. How does john get assigned admin_id = 1?

Once you define that, here is the UPDATE

update t 
set t.admin_name = i.admin_name
from table_admin t
inner join table_information i on i.someColumn = t.someColumn

If you don't care which admin_name gets what admin_id, then you can create a surrogate key and update your table. It would look like this:

select distinct 
    admin_name
    ,admin_id = row_number() over (order by (select null))
into #tempInformation
from table_information

--this is going to show the admin_id that will be updated in the table_admin table
select * from #tempInformation

update t 
set t.admin_name = i.admin_name
from table_admin t
inner join #tempInformation i on i.admin_id = t.admin_id

EDIT

If you don't have any admin_name currently populated in the table_admin then I suggest you just truncate that table and insert your distinct admin_name. Again, I would also make admin_id an identity property

truncate table table_admin

insert into table_admin (admin_id, admin_name)
   select distinct 
        admin_id = row_number() over (order by (select null))
        ,admin_name

I should highlight that in most schemas, the admin_id would mean something. It would be a PRIMARY KEY for this table and be used to relate this table to others in the database. Thus, not knowing which admin_id goes to which admin_name means you have a real mess on your hands and assigning them at random should break downstream processes... but that may not be the case since you haven't been keeping it up to begin with.

the column admin_id from the table table_admin could not be identity

I'd ask why? This would allude to you have a mapping of the admin_name to admin_id already... but you haven't shown that.