CODEWITHSUNDEEP
sqlsql-server
BA82283
BA82283

Reputation: 251

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

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

Upvotes: 3

Views: 1631

Answers (3)

sunysen
sunysen

Reputation: 2351

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

Upvotes: 4

CodingIntrigue
CodingIntrigue

Reputation: 78525

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

Upvotes: 3

juergen d
juergen d

Reputation: 204756

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.

Upvotes: 0

Related Questions