How do Insert into a table when one the column in the empty table is NULL

Question

I'd like to insert the results of this query to the table I created below.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Entity]') AND type in (N'U'))
DROP TABLE [Entity]
Go
Create Table Entity

([EntCode] [nvarchar](8) NOT NULL,
    [Name] [nvarchar](80) NOT NULL,
    [CompanyRegistration] [nvarchar](80) NULL,
    [Active] [int] NOT NULL,
    [AccessLevel] [int] NOT NULL ,
    [SiteURN] [nvarchar](128) NOT NULL,
    [CompanyURN] [nvarchar](128) NOT NULL,
    [SiteName] [nvarchar](30) NOT NULL,
    [SiteDesc] [nvarchar](60) NULL,
    [SiteURL] [nvarchar](512) NOT NULL)

And I'd like to insert the Data from this query using this, however I get this error "Insert Error: Column name or number of supplied values does not match table definition"

because I have an extra column [CompanyRegistration] nvarchar NULL, which I decalred NULL upon creation of the table. How can I insert these query results with my [CompanyRegistration] column tagged as NULL in the table??

insert into ResourceTaskFact.dbo.Entity

Select
    e.EntCode,
    e.Name,
    e.Active ,
    e.AccessLevel, 

    ss.SiteURN,
    ss.CompanyURN, 
    ss.SiteName ,
    ss.SiteDesc ,
    ss.SiteURL 
from SMECSite ss, SMECLegalEnt e
where ss.localsiteflag = 1
and e.active = 1

How do I solve this? I need your help Guys. Thank you in Advance!

Beau

sunysen · Accepted Answer · 2013-09-23 10:35:05Z

4

insert into ResourceTaskFact.dbo.Entity
  (e.EntCode,
   e.Name,
   e.Active,
   e.AccessLevel,
   ss.SiteURN,
   ss.CompanyURN,
   ss.SiteName,
   ss.SiteDesc,
   ss.SiteURL)

  Select e.EntCode,
         e.Name,
         e.Active,
         e.AccessLevel,
         ss.SiteURN,
         ss.CompanyURN,
         ss.SiteName,
         ss.SiteDesc,
         ss.SiteURL
    from SMECSite ss, SMECLegalEnt e
   where ss.localsiteflag = 1
     and e.active = 1

answered Sep 23, 2013 at 10:35

sunysen

2,3711 gold badge15 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CodingIntrigue · Accepted Answer · 2013-09-23 10:32:53Z

3

You need to explicitly set CompanyRegistration to NULL in your SELECT statement:

insert into ResourceTaskFact.dbo.Entity

Select
    e.EntCode,
    e.Name,
    NULL AS CompanyRegistration, -- Put column name in to demonstrate why you're selecting NULL here
    e.Active ,
    e.AccessLevel, 
    ss.SiteURN,
    ss.CompanyURN, 
    ss.SiteName ,
    ss.SiteDesc ,
    ss.SiteURL 
from SMECSite ss, SMECLegalEnt e
where ss.localsiteflag = 1
and e.active = 1

answered Sep 23, 2013 at 10:32

CodingIntrigue

79k32 gold badges176 silver badges178 bronze badges

Comments

juergen d · Accepted Answer · 2013-09-23 10:32:04Z

0

It is a good habit to name the columns you want to insert into

insert into ResourceTaskFact.dbo.Entity 
(
    [EntCode], 
    [Name], 
    [Active],
    [AccessLevel],
    [SiteURN],
    [CompanyURN]L,
    [SiteName],
    [SiteDesc],
    [SiteURL]
)    
Select
    e.EntCode,
    e.Name,
    e.Active ,
    e.AccessLevel,     
    ss.SiteURN,
    ss.CompanyURN, 
    ss.SiteName ,
    ss.SiteDesc ,
    ss.SiteURL 
from SMECSite ss, SMECLegalEnt e
where ss.localsiteflag = 1
and e.active = 1

That way you don't have to fill columns you don't have a value for.

answered Sep 23, 2013 at 10:32

juergen d

205k40 gold badges304 silver badges377 bronze badges

Collectives™ on Stack Overflow

How do Insert into a table when one the column in the empty table is NULL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related