[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Insert using a Select Statement error

Posted on 2007-08-03
5
Medium Priority
?
175 Views
Last Modified: 2010-08-05
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'






0
Comment
Question by:tracimcp
  • 3
5 Comments
 

Author Comment

by:tracimcp
ID: 19628458
I'm having a problem with the first 5 fields.  The select from the other table is fine.

Thanks!
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 19628611
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
 

Author Comment

by:tracimcp
ID: 19628658
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
 
LVL 4

Assisted Solution

by:bamboo7431
bamboo7431 earned 1000 total points
ID: 19628789
First two fields are reversed: in the field list you have "(PKId, VerTS", in the select you have "SELECT
GetDate(), 1"

0
 

Author Comment

by:tracimcp
ID: 19628834
Thanks guys - guess it has been a long week!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question