I am trying to add the results of this query to a new table using the
procedure below:

INSERT into db.TempCheckHistGN (EmpID,GrossPay,NetPay)
SELECT EmployeeID, SUM(GrossPay), as Expr1, Sum(NetPay) as Expr2 FROM
dbo.tblPACheckHist
WHERE (CheckDate Between '11/1/2005' AND '12/31/2010') AND (EmployeeID >=''
AND EmployeeID <='zzzzzz')
GROUP BY EmployeeID

I get a duplicate key error (EmpID is the key) but I shouldn't since the
records are being summed by EmployeeID

Thanks

Is the key on EmpID only?  Are you sure there aren't already rows in the
"new" table?

A

Show quote

Hide quote

Maybe there is already data in the TempCheckHistGN  table??

Denis the SQL Menace
http://sqlservercode.blogspot.com/


MarkT wrote:

Show quote

Hide quote

MarkT wrote:

Show quote

Hide quote

Is the table empty?  Have you confirmed that there are no unique indexes
or constraints on the table?

First off, is this even correct syntax?

I think you should remove the comma from SUM(GrossPay), as Expr1 to result
in this...

If there is a primary key on the EmployeeID on the TempCheckHistGN table you
will need to make sure that there are not duplicate employeeID's in your
select statement.

Show quote

Hide quote

In addition to the above comments, I noticed that you have one too many
commas in the SELECT statement.
"SUM(GrossPay), as Expr1," should not have the comma between the ending
parenthesis and 'as' -actually, it doesn't need the entire "as Expr1," part
of the statement.

Show quote

Hide quote

Arnie Rowland wrote:
Good eyes...

Sorry about the syntax I didn't cut and paste - I retyped it.

Show quote

Hide quote

WHY?  Don't ever do that.

If this is the EXACT syntax of your SQL statement, the following snip is incorrect:
SUM(GrossPay), as Expr1
remove the comma after SUM(GrossPay)

MarkT wrote:

Show quote

Hide quote

Are the two data elements emp_id and employee_id actually the same data
element?  If so, then use the same name for them.  If not, check that
they converted properly on insertion. Let's clean up the code a bit
- ISO-8601 dates, consistent use of BETWEEN and removal of silly
prefixes, etc:

INSERT INTO db.TempCheckHistgn (emp_id, gross_pay, net_pay)
SELECT emp_id, SUM(gross_pay), SUM(net_pay)
  FROM dbo.PayCheckHistory
WHERE check_date BETWEEN '2005-11-01' AND '2010-31-2010'
   AND emp_id BETWEEN '' AND 'ZZZZZZ' -- is this redundant?
GROUP BY emp_id;

Making a guess at the DDL that you did not post, I would assume that
the INSERT INTO has been run twice.  I would also guess that emp_id
BETWEEN '' AND 'ZZZZZZ' covers all the personnel identifiers, but
there might ones with numerics and perhaps the all-blank one is
special. It looks weird, tho.   That predicate looks more like a
CHECK() than a search condition.

You talked about "records"; you materialize and store computed
data; this is a symptom of still having a file system mindset.  This
problem used to happen in file systems when the same transaction tape
was hung by the next shift.

In the relational world, we avoid temp tables of all kinds by using
virtual tables, such as a VIEW, which is guaranteed to be up-to-date
when it is invoked:

CREATE VIEW PaycheckHistorySummary (emp_id, gross_pay_tot, net_pay_tot)

AS
SELECT emp_id, SUM(gross_pay), SUM(net_pay)
  FROM dbo.PaycheckHistory
WHERE check_date BETWEEN '2005-11-01' AND '2010-31-2010'
   AND emp_id BETWEEN '' AND 'ZZZZZZ' -- is this redundant?
GROUP BY emp_id;

Thanks everyone - there was a record already in the table.  I knew it had to
be something obvious.

They usually are.  Sometimes it just takes a second set of eyes to see what
is in front of our faces.

Show quote

Hide quote

Little bug in sql2005?
Need Consecutive Query
**SORT THE PHYSICAL ORDER ON A TABLE'S COLUMNS**
INSTEAD OF TRIGGERS DO NOT SUPPORT DIRECT RECURSION
SQL 2005 slower than 2000?