Bruce,
My first assumption would be that the Nurses table has changed. I put
comments inline.
--THIS SELECTS DISTINCT RECORDS INTO TEMPORARY TABLE NURSESDISTINCT
SELECT
DISTINCT(nurseID),
fname,
lname,
careerLadder,
bcon,
ssn,
domainAccount
INTO #nursesDistinct
FROM nurses;
--THIS CLEARS THE NURSES TABLE
DELETE FROM nurses;
/*THIS INSERTS THE DISTINCT RECORDS BACK INTO THE NURSES TABLE
INSERT nurses SELECT * FROM #nursesDistinct;
IT IS POSSIBLE THAT THERE WAS A CHANGE TO THE NURSES TABLE THAT IS CAUSING
THE ERROR
I WOULD GO AHEAD AND LIST OUT THE COLUMNS TO ELIMINATE ANY AMBIGUITY
*/
INSERT INTO NURSES(NURSEID, FNAME, LNAME, CAREERLADDER, BCON, SSN,
DOMAINACCOUNT)
SELECT NURSEID, FNAME, LNAME, CAREERLADDER, BCON, SSN, DOMAINACCOUNT
FROM #NURSESDISTINCT
--THIS DROPS THE TEMP TABLE
DROP TABLE #nursesDistinct;
Is the nurseid an identity field? If it is then you need to shut it off
before the insert takes place if you need to keep the value or dont insert
the field.
Look up SET IDENTITY_INSERT in the books online for more information on
inserting into an identity field.
Feel free to contact me offline.
Good Luck
Matthew
[EMAIL PROTECTED]
>From: Steven Monaghan <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: SQL <[EMAIL PROTECTED]>
>Subject: RE: Remove Duplicates Query
>Date: Wed, 27 Nov 2002 09:24:12 -0500
>
>My guess is that somewhere in the declaration section of the DTS package,
>#nursesDistinct is defined.
>
>But, I don't think that is the insert that is failing. I think the insert
>back into the nurses table is failing. Check the definition of that table
>and see if all of the columns in that table are included in the select
>statement.
>
>Steve
>-------------------------------------
>Steven Monaghan
>Oracle DBA / Cold Fusion Developer
>MSC Industrial Direct Co., Inc.
>http://www.mscdirect.com
>-------------------------------------
>
>-----Original Message-----
>From: Bruce Sorge [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, November 27, 2002 9:23 AM
>To: SQL
>Subject: Re: Remove Duplicates Query
>
>
>Well let me ask you this real quick. In looking at the database tables, I
>see that there is not a table called nursesDistinct. However, that is the
>table that the code is trying to enter information into. So I naively
>thought that the line INTO #nursesDistinct would create some sort of temp
>table. Is this not the case? Do I have to create the table?
>
>Thanks
>----- Original Message -----
>From: "Steven Monaghan" <[EMAIL PROTECTED]>
>To: "SQL" <[EMAIL PROTECTED]>
>Sent: Tuesday, November 26, 2002 4:18 PM
>Subject: RE: Remove Duplicates Query
>
>
> > This probably means that your select distinct does not include all of
>the
> > columns in the table. You would have to either explicitly list the
>columns
> > in the insert statement, or select all of the columns, in the correct
>order,
> > in your select statement.
> >
> > I personally would recommend both!
> >
> > Steve
> > -------------------------------------
> > Steven Monaghan
> > Oracle DBA / Cold Fusion Developer
> > MSC Industrial Direct Co., Inc.
> > http://www.mscdirect.com
> > -------------------------------------
> >
> > -----Original Message-----
> > From: Bruce Sorge [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, November 26, 2002 4:53 PM
> > To: SQL
> > Subject: Remove Duplicates Query
> >
> >
> > I have inherited an application that has a DTS package. One of the
>functions
> > in this package is to remove duplicates from a table. The query looks
>like
> > this:
> >
> > SELECT
> > DISTINCT(nurseID), fname, lname, careerLadder, bcon,
> > ssn, domainAccount
> > INTO #nursesDistinct
> > FROM nurses;
> > DELETE FROM nurses;
> > INSERT nurses SELECT * FROM #nursesDistinct;
> > DROP TABLE #nursesDistinct;
> >
> > Problem is that this does not work. When I run this I receive an error
> > message:
> >
> > Error Description: Insert Error: Column name or number of
>specified
> > values does not match table definition.
> >
> > What does this mean?
> >
> > Thanks,
> >
> >
> > Bruce
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
Get the mailserver that powers this list at http://www.coolfusion.com
