Insert using a Select Statement error

I have a table that I'm trying to insert data into from another table.  When I try to insert I get the following error and I think it because of the primary key or defaults.

There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Here is the table I'm trying to insert into:
create table tbHallmarkInvoice
(
PKId int not null identity constraint pkHallmarkInvoice primary key nonclustered,
VerTS timestamp not null,
Uuid uniqueidentifier not null constraint dfHallmarkInvoice_Uuid default newid() constraint uqHallmarkInvoice_Uuid unique nonclustered,
Created      datetime      not null constraint dfHallmarkInvoice_Created default GetDate(),
Updated      datetime      not null constraint dfHallmarkInvoice_Updated default GetDate(),
Flags int      not null constraint dfHallmarkInvoice_Flags default 0,
EGrpId int      not null constraint fkHallmarkInvoice_EGrpId foreign key references tbEmployerGroup(PKId),
InvoiceID      varchar(32)      not null constraint uqHallmarkInvoice_InvoiceID unique nonclustered,
InvoiceDate      datetime      not null,
Company      varchar(48)      not null constraint dfHallmarkInvoice_Company default '',
Address1      varchar(48)      not null constraint dfHallmarkInvoice_Address1 default '',
Address2      varchar(48)      not null constraint dfHallmarkInvoice_Address2 default '',
City      varchar(48)      not null constraint dfHallmarkInvoice_City default '',
State      varchar(2)      not null constraint dfHallmarkInvoice_State default '',
ZIPCode      varchar(12)      not null constraint dfHallmarkInvoice_ZIPCode default '',
Redemptions      float not null constraint dfHallmarkInvoice_Redemptions default 0,
Fees      float not null constraint dfHallmarkInvoice_Fees default 0,
Processing      float not null constraint dfHallmarkInvoice_Processing default 0,
AccountNo      varchar(12)      not null constraint dfHallmarkInvoice_AccounNo default '',
Terms      varchar(64)      not null constraint dfHallmarkInvoice_Terms default '',
PaymentDate       datetime      not null,
StartDate      datetime      not null,
EndDate      datetime not null
)
go
create clustered index ixHallmarkInvoice_EGrpId on tbHallmarkInvoice(EGrpId)
go

And the insert:
SET IDENTITY_INSERT tbHallmarkInvoice ON
INSERT INTO tbHallmarkInvoice
(

      PKId,
      VerTS,
      Uuid,
      Created,
      Updated,
      Flags,
      EGrpId,
      InvoiceID,
      InvoiceDate,
      Company,
      Address1,
      Address2,
      City,
      State,
      ZIPCode,
      Redemptions,
      Fees,
      Processing,
      AccountNo,
      Terms,
      PaymentDate,
      StartDate,
      EndDate      
)
SELECT
GetDate(),
1,
      InvoiceID,      
      EmployerGroup,            
      InvoiceDate,
      Company,
      Address1,
      Address2,
      City,
      State,
      ZIPCode,                  
      Redemptions,
      Fees,
      Processing,
      AccountNo,
      Terms,
      PaymentDate,                  
      StartDate,
      EndDate      
      
 FROM tbHallmarkInvoiceStaging

where pkid = 'H'






tracimcpAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
In your:

INSERT INTO tablename (<columns>) SELECT ...

you have 23 columns mentioned by you only have 19 columns in your SELECT.  You must
have the same number in each.  In addition, do not attempt to insert into an identity
column.  Because it is an identity column, it will increment itself automatically.
0
 
tracimcpAuthor Commented:
I'm having a problem with the first 5 fields.  The select from the other table is fine.

Thanks!
0
 
tracimcpAuthor Commented:
Thanks.  How do I fill the default columns?  Do they automatically fill since they have default values or do I need to put something in the select?

Such as:
 Uuid = NewID(),
Created = GetDate(),
Updated = GetDate(),

The other question I have is the timestamp field - is that filled with a GetDate() also?
0
 
bamboo7431Connect With a Mentor Commented:
First two fields are reversed: in the field list you have "(PKId, VerTS", in the select you have "SELECT
GetDate(), 1"

0
 
tracimcpAuthor Commented:
Thanks guys - guess it has been a long week!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.