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
>