Showing posts with label lookup. Show all posts
Showing posts with label lookup. Show all posts

Sunday, February 19, 2012

Doing the lookup on the server while inserting

I have these 2 tables:
CREATE TABLE tbl_ft_testsegments (
SegmentID int(10) AUTO_INCREMENT NOT NULL,
TestID smallint(6) NOT NULL DEFAULT '0',
CarrierFreq smallint(6) NOT NULL DEFAULT '0',
BeatFreq smallint(6) NOT NULL DEFAULT '0',
BeatFreqDelta smallint(6) NOT NULL DEFAULT '0',
Duration tinyint(4) NOT NULL DEFAULT '0',
AmplitudeRampID tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY(SegmentID)
)
CREATE TABLE tbl_ft_amplituderamps (
AmplitudeRampID tinyint(4) AUTO_INCREMENT NOT NULL,
Name char(12) NOT NULL,
PRIMARY KEY(AmplitudeRampID)
)
tbl_ft_amplituderamps is constant and will not change.
On the client, I load a text file that has string values that match what is
in amplituderamps, or atleast they are supposed to!
I store this data in a business object that has the string representation of
an "amplitude ramp"
When I insert them, I am currently getting the AmplitudeRampID from
tbl_ft_amplituderamps where <text file value> = tbl_ft_amplituderamps.Name
THis isn't *too* bad cause I have a local copy of the whole table in memory.
I am considering instead of doing the insert with string instead of the ID
and doing the lookup on teh server side.
Something like:
@.myString varchar(20)
INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
SELECT tbl_ft_amplituderamps.AmplitudeRampID
FROM tbl_ft_amplituderamps WHERE tbl_ft_amplituderamps.Name = myString
Being far from a professional, I'm wondering if this is the *preferred*
method? Is it better to do a little lookup on the client or should I do
this on the server?
Any suggestions would be much appreciated. I have MANY cases where I need
to do this and I would like to get teh best method sorted out before I add
the sprocs for all of the different tables.
Thanks for reading!
Steve"sklett" <sklett@.mddirect.com> wrote in message
news:uddCEzXZGHA.1204@.TK2MSFTNGP04.phx.gbl...
>I have these 2 tables:
> CREATE TABLE tbl_ft_testsegments (
> SegmentID int(10) AUTO_INCREMENT NOT NULL,
> TestID smallint(6) NOT NULL DEFAULT '0',
> CarrierFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreqDelta smallint(6) NOT NULL DEFAULT '0',
> Duration tinyint(4) NOT NULL DEFAULT '0',
> AmplitudeRampID tinyint(4) NOT NULL DEFAULT '0',
> PRIMARY KEY(SegmentID)
> )
> CREATE TABLE tbl_ft_amplituderamps (
> AmplitudeRampID tinyint(4) AUTO_INCREMENT NOT NULL,
> Name char(12) NOT NULL,
> PRIMARY KEY(AmplitudeRampID)
> )
>
> tbl_ft_amplituderamps is constant and will not change.
> On the client, I load a text file that has string values that match what
> is in amplituderamps, or atleast they are supposed to!
> I store this data in a business object that has the string representation
> of an "amplitude ramp"
> When I insert them, I am currently getting the AmplitudeRampID from
> tbl_ft_amplituderamps where <text file value> =
> tbl_ft_amplituderamps.Name
> THis isn't *too* bad cause I have a local copy of the whole table in
> memory. I am considering instead of doing the insert with string instead
> of the ID and doing the lookup on teh server side.
> Something like:
> @.myString varchar(20)
> INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
> SELECT tbl_ft_amplituderamps.AmplitudeRampID
> FROM tbl_ft_amplituderamps WHERE tbl_ft_amplituderamps.Name = myString
>
> Being far from a professional, I'm wondering if this is the *preferred*
> method? Is it better to do a little lookup on the client or should I do
> this on the server?
> Any suggestions would be much appreciated. I have MANY cases where I need
> to do this and I would like to get teh best method sorted out before I add
> the sprocs for all of the different tables.
> Thanks for reading!
> Steve
>
One approach that works well with large data sets is to insert the entire
file into a temporary "staging" table in the database, then do the whole
thing with a join:
INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
SELECT DISTINCT A.AmplitudeRampID
FROM tbl_ft_amplituderamps AS A
JOIN staging AS S
ON A.Name = S.String ;
For loading the file itself you have a number of options. DTS (SQL Server
2000) or Integration Services (2005) or BCP for example.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,

> Being far from a professional, I'm wondering if this is the *preferred*
> method? Is it better to do a little lookup on the client or should I do
> this on the server?
Yes. Centralise as much of your logic on the server as possible. Keep
your client-side software as light-weight as you can - ideally, limit it to
presentation only. The processing work belongs on the server(s). Golden
rule, end of story :)
Robert.
"sklett" <sklett@.mddirect.com> wrote in message
news:uddCEzXZGHA.1204@.TK2MSFTNGP04.phx.gbl...
>I have these 2 tables:
> CREATE TABLE tbl_ft_testsegments (
> SegmentID int(10) AUTO_INCREMENT NOT NULL,
> TestID smallint(6) NOT NULL DEFAULT '0',
> CarrierFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreqDelta smallint(6) NOT NULL DEFAULT '0',
> Duration tinyint(4) NOT NULL DEFAULT '0',
> AmplitudeRampID tinyint(4) NOT NULL DEFAULT '0',
> PRIMARY KEY(SegmentID)
> )
> CREATE TABLE tbl_ft_amplituderamps (
> AmplitudeRampID tinyint(4) AUTO_INCREMENT NOT NULL,
> Name char(12) NOT NULL,
> PRIMARY KEY(AmplitudeRampID)
> )
>
> tbl_ft_amplituderamps is constant and will not change.
> On the client, I load a text file that has string values that match what
> is in amplituderamps, or atleast they are supposed to!
> I store this data in a business object that has the string representation
> of an "amplitude ramp"
> When I insert them, I am currently getting the AmplitudeRampID from
> tbl_ft_amplituderamps where <text file value> =
> tbl_ft_amplituderamps.Name
> THis isn't *too* bad cause I have a local copy of the whole table in
> memory. I am considering instead of doing the insert with string instead
> of the ID and doing the lookup on teh server side.
> Something like:
> @.myString varchar(20)
> INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
> SELECT tbl_ft_amplituderamps.AmplitudeRampID
> FROM tbl_ft_amplituderamps WHERE tbl_ft_amplituderamps.Name = myString
>
> Being far from a professional, I'm wondering if this is the *preferred*
> method? Is it better to do a little lookup on the client or should I do
> this on the server?
> Any suggestions would be much appreciated. I have MANY cases where I need
> to do this and I would like to get teh best method sorted out before I add
> the sprocs for all of the different tables.
> Thanks for reading!
> Steve
>

Friday, February 17, 2012

Doing a lookup between tables

I have two tables, one is a list of offered classes (class_offer) and a list of avalaible classes (course).

I need to find which courses are not being offered.

This is what I have now. It returns all the course rows if they match or not. I don't understand why this does not work. I am comparing the primary key of the course to equivalent values in the class_offer table.

select distinct course.course_name from
course join class_offer
on
course.course_no != class_offer.course_no
and
course.dept_no != class_offer.dept_no

DDL's:

CREATE TABLE COURSE
(
COURSE_NO CHAR(3) not null,
DEPT_NO CHAR(3) not null,
COURSE_NAME VARCHAR2(30),
CREDIT_HOUR NUMBER(3),
CONSTRAINT COURSE_PK PRIMARY KEY (COURSE_NO,DEPT_NO)
);

ALTER TABLE COURSE
ADD CONSTRAINT FK_COURSE
FOREIGN KEY (DEPT_NO)
REFERENCES DEPARTMENT(DEPT_NO);

CREATE TABLE CLASS_OFFER
(
COURSE_NO CHAR(3) not null,
DEPT_NO CHAR(3) not null,
SECTION_NO CHAR(3) not null,
YEAR NUMBER(4) not null,
TIME CHAR(8),
LOCATION VARCHAR2(10),
FAC_NO CHAR(11),
CONSTRAINT CLASS_OFFER_PK PRIMARY KEY (COURSE_NO,DEPT_NO,SECTION_NO,YEAR)
);

ALTER TABLE CLASS_OFFER
ADD CONSTRAINT FK_OFFER_C
FOREIGN KEY
(
COURSE_NO,
DEPT_NO
)
REFERENCES COURSE
(
COURSE_NO,
DEPT_NO
);
ALTER TABLE CLASS_OFFER
ADD CONSTRAINT FK_OFFER_FAC
FOREIGN KEY (FAC_NO)
REFERENCES FACULTY(FAC_ID);you want a LEFT OUTER JOIN with a test in the WHERE clause for NULL in the join column of the right table|||This works, but it's the opposite of what I am looking for.

select distinct course.course_name from
course left outer join class_offer
on
course.course_no = class_offer.course_no and course.dept_no = class_offer.dept_no
where
class_offer.course_no is not null and class_offer.dept_no is not null;

When I replace the = with != it does not give me the correct response.|||you want IS NULL, not IS NOT NULL

and you don't need the DISTINCT|||It works now, thank you for your help.

Thing is, I am not highly proficiant in sql. I would like to be, since I do a lot of JDBC programming. So I am interested in how this query works. As a C++\Java programmer, I think in terms of multiple method calls to get an answer. In sql, all the steps are in one call. So I do not fully grasp how to build a sql call.|||practice, practice, practice

:cool: