0

I am working on SQL Server. I have different databases and I am trying to insert values of one database table columns value into other database table columns and along with that there are few more columns which are not null and no data is inserted into those columns, while doing this I am getting error, 

INSERT INTO dbo.DocComponent(MIMETYPE) 
    SELECT MIMETYPE
    FROM Test67.dbo.PO_MIMETYPE;

Error:

Cannot insert the value NULL into column 'ID', table 'FullTextDB.dbo.DOCCOMPONENT'; column does not allow nulls. INSERT fails.

Could someone has solution for this...?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
gupta k
  • 23
  • 1
  • 9
  • Well, the error seems crystal clear - you have a column `ID` in your table `DocComponent` which is set to `NOT NULL` and obviously doesn't have a default value (or an `IDENTITY` spec), and therefore you **must provide** a value for that `ID` column in your `INSERT` .... use `INSERT INTO dbo.DocComponent(ID, MimeType) SELECT xxxx, Mimetype FROM ......` – marc_s Feb 11 '16 at 11:28
  • there is one more problem ID of DocComponent doesn't belong to ID po_MIMETYPE table both are diffferent.. – gupta k Feb 11 '16 at 11:37
  • Whatever - you just **MUST** provide a value for `ID` ! – marc_s Feb 11 '16 at 11:38
  • I have a solution that droping all contraints like primary key and not null and then I can insert the data easily, but do we have any better solution than this..???? – gupta k Feb 11 '16 at 11:38

1 Answers1

0

That clearly infers that your ID column is not an IDENTITY column and as well it's a primary key column and so NULL is not allowed. You will have provide some value for the ID column as well.

INSERT INTO dbo.DocComponent(ID, MIMETYPE) 
    SELECT 1, MIMETYPE
    FROM Test67.dbo.PO_MIMETYPE;

You better make your table column ID an IDENTITY column. See Adding an identity to an existing column

Community
  • 1
  • 1
Rahul
  • 76,197
  • 13
  • 71
  • 125