John W. Colby
jwcolby at colbyconsulting.com
Wed Sep 22 16:29:31 CDT 2004
Haaa... That is it. The not exists clause.
I'll try it out and let you know if that does what I need.
Thanks,
John W. Colby
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco
Tapia
Sent: Wednesday, September 22, 2004 4:09 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Iif in SQL Server
A solution is for your APPEND query (SELECT statement) to include a clause
that excludes the PK's
Something like SELECT FIeld1, Field2, ...
FROM table
WHERE PK NOT EXISTS(SELECT PK From PrimaryTable)
On Wed, 22 Sep 2004 15:34:21 -0400, John W. Colby
<jwcolby at colbyconsulting.com> wrote:
> It would work IF I could get a second set of records to append to the
> table. ATM the second append query immediately bombs with the
> "violation of primary key constraint" error. It appears that the
> second append (correctly) attempts to append SOME records with a PK
> already in the table. What I want is for the append query to silently
> move on to the next record when that happens until it finds records
> where the PK ISN'T already in the table, then it appends THOSE
> records. What happens is that the entire query halts because of the
> first PK collision.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
> Francisco Tapia
> Sent: Wednesday, September 22, 2004 3:20 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Iif in SQL Server
>
> So is that gonna work for you?
>
> On Wed, 22 Sep 2004 14:48:10 -0400, John W. Colby
> <jwcolby at colbyconsulting.com> wrote:
> > No, I don't want a report, at least not atm. I need the ability to
> > count these things over and over and over. Thus it has to be FAST.
> > Clients call and ask "how many addresses in these 230 zip codes?
> > That kind of stuff. The zips can come from two different places,
> > thus I want to pull them out and place them in a single table where
> > I can tell where they came from if I need to but I only have to do a
> > count on a single indexed column.
> >
> > John W. Colby
> > www.ColbyConsulting.com
> >
> >
> >
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
> > Francisco Tapia
> > Sent: Wednesday, September 22, 2004 2:06 PM
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: Re: [dba-SQLServer] Iif in SQL Server
> >
> > You want a report right? ie a resultset... yes a sproc John.. you
> > don't have to CREATE a sproc, but doing so will store the
> > optimizations for the sproc in the server and possibly even caching
> > parts of the report (since the data hasn't changed).
> >
> > to create a sproc
> >
> > CREATE PROCEDURE stp_MyNewSprocNamingConvention
> >
> > AS
> >
> > SELECT FIELD1, Field2, Field3, Case...
> > FROM Table1
> > WHERE ClauseoptionsHere
> >
> > On Wed, 22 Sep 2004 08:39:44 -0400, John W. Colby
> > <jwcolby at colbyconsulting.com> wrote:
> > > Are we talking about code in an Sproc here? I am not talking
> > > about AccessVB. I am looking for something to work with 165
> > > million records and return results in a few minutes.
> > >
> > > In the end I built a table with a PK field, a ZIPCode field and a
> > > Src field. I made the PK field a PK so that it has a unique index.
> > > I then wrote two queries, one that pulls data from one field plus
> > > the PK plus a 1 as Src and appends it to my new table. The other
> > > query pulls data from the other field plus the PK plus a 2 as the
> > > Src and appends to the table. Run the first query. All records
> > > that have anything in the ZIP code are put in the table. Run the
> > > second query. All the records that have something in the second
> > > ZIP field but aren't already in the table get put in the table.
> > >
> > > Now I have a table with ONE field, with data from one or the other
> > > field, with a column which tells me which source field it came
> > > from, with a PK to join back up to the main table.
> > >
> > > Of course my second query is failing to append at all because the
> > > PK already exists in the new table.
> > >
> > > John W. Colby
> > > www.ColbyConsulting.com
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: dba-sqlserver-bounces at databaseadvisors.com
> > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
> > > Francisco Tapia
> > > Sent: Wednesday, September 22, 2004 1:30 AM
> > > To: dba-sqlserver at databaseadvisors.com
> > > Subject: Re: [dba-SQLServer] Iif in SQL Server
> > >
> > > JOHN!
> > >
> > > I know you're busy these days but it works like this
> > >
> > > SELECT CASE WHEN FIELD = 'value' THEN FieldWhenTrue
> > > WHEN Fieldothercase = 'othervalue THEN
> > > FieldWhenOtherValue
> > > ELSE FieldwhenELSE
> > > END AS AliasName,
> > > Next Field
> > >
> > > >From TableName
> > >
> > > On Wed, 22 Sep 2004 11:45:36 +1000, Stuart McLachlan
> > > <stuart at lexacorp.com.pg> wrote:
> > > > On 21 Sep 2004 at 21:38, John W. Colby wrote:
> > > >
> > > > > Sorry, my Outlook is totally screwed up
> > > >
> > > > That's tautology. <grin>
>
> --
> -Francisco
> http://ft316db.VOTEorNOT.org
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
--
-Francisco
http://ft316db.VOTEorNOT.org _______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com