Thank you so much!
Along with that the program I'm working on needs to do a lot of data
transformations.. IE. Change field1 into field1+field2 or map a value
from A to Apple. Also much more complex operations on many different
fields. I might normally accomplish this using the case statement in
SQL or using mapping tables to find values. I worry that the size and
complexity of the Sequel statement would become extremely long and
difficult to control.
Does anyone know of examples of code where this has been done using
Sequel or have any recommendations for doing field by field mappings?
Thank you!
Adam
Example T-SQL Code:
SELECT
, CASE WHEN ps_pers_nid.country = 'USA' THEN
LTRIM(RTRIM((National_ID))) ELSE '**Foregin ID**' ENd
, REPLICATE('0',6-LEN(convert(varchar,(J1.Emplid)))) +
convert(varchar,(J1.Emplid)) --4Clock
, CASE
WHEN empl_status = 'A' THEN 'A' --Active
WHEN empl_status = 'D' THEN 'T' --Deceased
WHEN empl_status = 'L' THEN 'I' --Leave of Absence
WHEN empl_status = 'P' THEN 'A' --Leave with Pay
WHEN empl_status = 'Q' THEN 'A' --Retired With Pay
WHEN empl_status = 'R' THEN 'T' --Retired
WHEN empl_status = 'S' THEN 'I' --Suspended
WHEN empl_status = 'T' THEN 'T' --Terminated
WHEN empl_status = 'U' THEN 'A' --Terminated With Pay
ELSE '*'
END --5Status
, ''
, REPLICATE('0',4- LEN(LEFT(ISNULL(DEPTID,''),4))) +
LEFT(ISNULL(DEPTID,''),4) --6Division
, RTRIM(LTRIM(CAST(ISNULL(N1.FIRST_NAME ,'') as
char(15)))) --8FirstName
, RTRIM(LTRIM(CAST(ISNULL(N1.MIDDLE_NAME,'')as
char(15)))) --9MiddleName
, RTRIM(LTRIM(CAST(ISNULL(N1.LAST_NAME,'')as char(23)))) --10LastName
, RTRIM(LTRIM(CAST(ISNULL(replace(N1.NAME_SUFFIX,' .','') ,'')as
char(23)))) --11Suffix
,CASE
WHEN ADDY.country = 'USA' THEN
CASE
WHEN LEN(ADDY.AD1) + LEN(ADDY.AD2) >=
22 AND ADDY.AD2 ='' THEN
''
WHEN LEN(ADDY.AD1) + LEN(ADDY.AD2) >=
22 AND ADDY.AD2 != '' THEN
RTRIM(LTRIM(CAST(ADDY.AD1 as char(22))))
ELSE ''
END
ELSE ''
END -- 13ADdress2
On Mar 23, 6:26 pm, Jeremy Evans <[email protected]> wrote:
> On Mar 23, 4:07 pm, Adam Perkins <[email protected]> wrote:
>
> > Hello,
>
> > I've been searching for the most appropriate and quickest way using
> > Sequel to insert records in a table, from another table on the same
> > server (even the same database at the moment). Number of rows could
> > be from 5K-50K at a time.
>
> > I wasn't sure if just creating a dataset, looping and creating/saving
> > a new object would be the fastest?
>
> It's fastest to do it on the server in a single query:
>
> DB[:to_table].insert([:to_column1, :to_column2, ...],
> DB[:from_table].select(:from_column1, :from_column2, ...))
>
> This does:
>
> INSERT INTO to_table (to_column1, to_column2, ...)
> SELECT from_column1, from_column2, ... FROM from_table
>
> Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
