How to insert into a table considering that table has a Primary Key already in it?

Question

I have two tables A and B and need to insert records (few columns not all) from A into B. Of course I guess I can do:

INSERT INTO B (col2)
    SELECT DISTINCT col2
    FROM A

However, Col1 in table B (named ID) has a type of INT so it is causing this error:

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

How can I make SQL Server ignore this column and just insert the data I need?

Thanks.

If ID is the primary key you need to provide a value for it, full stop! — Charleh, Sep 18 '14 at 23:54
Are you sure you've picked the correct column(s) to be the primary key in your table design? — hatchet - done with SOverflow, Sep 19 '14 at 00:00
May be what you are looking for is to set the ID column in table B to be auto increment by set the column identity — Islam El-Khayat, Sep 19 '14 at 01:02
>>If ID is the primary key you need to provide a value for it, full stop. THE ID COLUMN IN TABLE B IS NOT PK. I AM CONSIDER IT AS A FK. THE ONE COMING FORM TABLE A IS PK SO IT SHOULD NOT BE A PROBLEM. I HAVE A FEELING WE WILL TALKING ABOUT 2 DIFFERENT THINGS. CAN YOU PLEASE GIVE ME SOME HELP HERE? — amit kohan, Sep 22 '14 at 19:53

score 0 · Answer 1 · answered Sep 19 '14 at 00:00

0

A primary key must fulfill two conditions:

It must be unique in the table (that is, any row in the table can be identified by its primary key), and
The fields that are part of the primary key cannot be NULL.
That's because allowing NULL values in the primary key will make the uniqueness of the primary key impossible to hold, because a NULL value is non-equal to any other value, including another NULL.

Quoting from here:

A unique key constraint does not imply the NOT NULL constraint in practice. Because NULL is not an actual value (it represents the lack of a value), when two rows are compared, and both rows have NULL in a column, the column values are not considered to be equal. Thus, in order for a unique key to uniquely identify each row in a table, NULL values must not be used.

answered Sep 19 '14 at 00:00

Barranka

20,547
13
65
83

I hope I have described it clearly or maybe not. Yes, PK has to be unique no doubt about it. The value being inserted in B table is a varchar from A (source) and this target table already has an ID per each record which INT. I was expecting Sql Server would provide the value for the ID (INT NOT NULL) so this error is regarding the INT column. All I'm asking is how to fix this issue. – amit kohan Sep 19 '14 at 01:01
@amitkohan Include it in your `insert` statement – Barranka Sep 19 '14 at 04:30
@amitkohan your question specifically says: *"How can I make SQL Server ignore this column* (I understand you mean the `ID` column) *and just insert the data I need?"* The answer to that is plain and simple: If the `ID` column is a primary key, then it ***must*** have a non-null value. So either you insert the appropriate value in that column or you modify your table to remove the primary key (you can remove the `primary key` constraint without dropping the column) – Barranka Sep 19 '14 at 04:32
YES YES YES, AND YES YOU ARE RIGHT I'M NOT AGAINST THAT AND I UNDERSTAND IT. – amit kohan Sep 22 '14 at 19:57
@amitkohan then... how do you want to fill the `id` column? Of course you could define that column to be an auto-incremental value, but then, you'd loose any reference to the original values... which may be what you want, after all – Barranka Sep 22 '14 at 20:13
@amitkohan [Check this question and its answers](http://stackoverflow.com/questions/4862385/sql-server-add-auto-increment-primary-key-to-existing-table) – Barranka Sep 22 '14 at 21:13

score 0 · Answer 2 · answered Sep 19 '14 at 01:06

0

This should work assuming you don't insert duplicates into B:

INSERT INTO B (col2)
SELECT DISTINCT col2
FROM A
WHERE col2 IS NOT NULL

answered Sep 19 '14 at 01:06

Christian Maslen

1,100
9
13

NO i'M NOT INSERTING DUPLICATION. EVEN IF I DO WHY SHOULD IT MATTER? MY ID IN TABLE IS NOT PK. IT IS A FK TO THE ID OF TABLE A. ID IN TABLE A IS PK. – amit kohan Sep 22 '14 at 19:55
NO THIS WILL NOT SOLVE IT BUT THANKS – amit kohan Sep 22 '14 at 19:56
The reason I don't know if it matters is because you have not supplied any DDL and sample inserts simulating your problem. Looking at your error col2 in table B has a not null constraint. This is why you are getting the error. Either remove the constraint or filter the nulls form the original table as per my answer – Christian Maslen Sep 23 '14 at 04:37

score 0 · Answer 3 · answered Sep 19 '14 at 01:19

0

Set ID column in table B to "auto-increment".

answered Sep 19 '14 at 01:19

Kenan Zahirovic

1,587
14
24

score 0 · Answer 4 · edited May 23 '17 at 11:57

0

SQL Server will provide automatically unique values for ID column if you define it as IDENTITY

In your case you can calculate the maximum value of ID column and start IDENTITY from the value that exceeds that maximum.

See the accepted answer for SQL Server, How to set auto increment after creating a table without data loss? for such code.

edited May 23 '17 at 11:57

Community

1
1

answered Sep 19 '14 at 01:39

PM 77-1

12,933
21
68
111

score -1 · Answer 5 · answered Sep 19 '14 at 00:56

-1

You need to create a relationship between the two tables and do an update statement.

Update table b set valueb = valuea from table a where a.id = b.id

You also need to rethink your design a little bit it sounds like.

answered Sep 19 '14 at 00:56

NSAgentSmith

1
1

You can downvote me I guess. Are you saying then that your data from table a can go anywhere in table b? It doesn't matter what row? It doesn't map to the id already in table b? I'm sorry I guess I don't work with loosely coupled data like that. – NSAgentSmith Sep 19 '14 at 01:49

How to insert into a table considering that table has a Primary Key already in it?

5 Answers5