September 29, 2009 at 12:53 pm
Hi again Lowell,
I have not tried this yet?
[Code]/*INSERT INTO [DestinationDatabase].[dbo].[prempl]
([fempno],
[fname],
[ffname],
[fmi],
[fbirthdate],
[fcity],
[fdept],
[fendate],
[fnshift],
[fpaytype],
[fphone],
[freghr],
[fssn],
[fstate],
[fzip],
[emgcontact],
[emgphone],
[fgross],
[fcountry],
[fcemail],
---[timestamp_column], --not included in any insert,but here for reference
---[identity_column], --not included in any insert,but here for reference
[faddress],
[fhiredate])
*/[/Code]
This works great!
[Code]SELECT
Employee_Master.Empno,
Employee_Master.Surname,
Employee_Master.GivenName,
ISNULL(Employee_Master.Initial,'A') AS fmi,
'1900-01-01 00:00:00.000' As fbirthdate,
Employee_Master.City,
Employee_Master.BasicEarnGL,-- = fdept
'1900-01-01 00:00:00.000' As fendate,
1 As fnshift,
'HR' As fpaytype,
Employee_Master.PhoneNumber2, -- = 1
0.00 As freghr,
'123456789' As fssn,
Employee_Master.Prov, --fstate
Employee_Master.Postal, --fzip
'A' As emgcontact,
'1' As emgphone,
0.00 As fgross,
'CANADA' As fcountry,
'A' As fcemail,
'a' As faddress,
'1900-01-01 00:00:00.000' As fhiredate
FROM SQLSERVER.SourceDatabase.dbo.Employee_Master Employee_Master
INNER JOIN DestinationDatabase.dbo.Prempl Prempl ON CONVERT(int,Employee_Master.Empno) = Convert(int,Prempl.fempno)
WHERE Prempl.fempno IS NULL[/Code]
I am not sure where to place this in the query or how it works?
[Code]SELECT exString,
SUBSTRING(exString,16,3) ,
CASE SUBSTRING(exString,16,3)
WHEN 110 THENc1
WHEN 120 THENc2
WHEN 130 THENc3
WHEN 140 THENc4
WHEN 150 THENc5
WHEN 152 THENc6
WHEN 154 THENc7
WHEN 156 THENc8
WHEN 160 THENe1
WHEN 162 THENe2
WHEN 164 THENe3
WHEN 170 THENe4
WHEN 180 THENe5
WHEN 210 THENs1
WHEN 220 THENs2
WHEN 222 THENs3
WHEN 224 THENs4
WHEN 230 THENs5
WHEN 240 THENs6
WHEN 250 THENs7
WHEN 260 THENs8
WHEN 265 THENs9
WHEN 270 THENs0
WHEN 280 THENsa
WHEN 290 THENsb
WHEN 300 THENp1
WHEN 315 THENp2
WHEN 305 THENp3
WHEN 310 THENp4
WHEN 320 THENp5
WHEN 325 THENp6
WHEN 330 THENp7
WHEN 335 THENp8
WHEN 340 THENpd
WHEN 345 THENpe
WHEN 350 THENpf
WHEN 355 THENpg
WHEN 360 THENph
WHEN 365 THENpi
WHEN 370 THENps
WHEN 375 THENpt
WHEN 380 THENpu
WHEN 385 THENpv
WHEN 390 THENpw[/Code]
Cheers,
blawrence
September 29, 2009 at 1:36 pm
In this example it appears as though you are explicitly selecting the substrings in code. How do I select unknown strings from the existing source table [Code]LinkedServer.SourceDatabase.Employee_Master.BasicEarnGL[/Code] and use the WHEN THEN...and where do I place this in the query to work and to insert the data into the destination table?
[Code]SELECT SUBSTRING('999-999-9999-99110-000000',16,3) --start at char 16, get 3 characters
Create TABLE #Example(exString varchar(30))
INSERT INTO #Example
SELECT '999-999-9999-99110-000000' UNION ALL
SELECT '999-999-9999-99120-000000' UNION ALL
SELECT '999-999-9999-99130-000000'[/Code]
Cheers,
blawrence
September 29, 2009 at 2:05 pm
as you get more familar with SQL, you'ss see that any column can bbe replaced with a hardcoded value, and vice vers in just about any SQL:
this is the enhanced peice you are looking for: see how exString, which was my example column, got replaced with Employee_Master.BasicEarnGL, your real column.
SELECT
Employee_Master.Empno,
Employee_Master.Surname,
Employee_Master.GivenName,
ISNULL(Employee_Master.Initial,'A') AS fmi,
'1900-01-01 00:00:00.000' As fbirthdate,
Employee_Master.City,
CASE SUBSTRING(Employee_Master.BasicEarnGL,16,3)
WHEN '110' THEN 'c1'
WHEN '120' THEN 'c2'
WHEN '130' THEN 'c3'
WHEN '140' THEN 'c4'
WHEN '150' THEN 'c5'
WHEN '152' THEN 'c6'
WHEN '154' THEN 'c7'
WHEN '156' THEN 'c8'
WHEN '160' THEN 'e1'
WHEN '162' THEN 'e2'
WHEN '164' THEN 'e3'
WHEN '170' THEN 'e4'
WHEN '180' THEN 'e5'
WHEN '210' THEN 's1'
WHEN '220' THEN 's2'
WHEN '222' THEN 's3'
WHEN '224' THEN 's4'
WHEN '230' THEN 's5'
WHEN '240' THEN 's6'
WHEN '250' THEN 's7'
WHEN '260' THEN 's8'
WHEN '265' THEN 's9'
WHEN '270' THEN 's0'
WHEN '280' THEN 'sa'
WHEN '290' THEN 'sb'
WHEN '300' THEN 'p1'
WHEN '315' THEN 'p2'
WHEN '305' THEN 'p3'
WHEN '310' THEN 'p4'
WHEN '320' THEN 'p5'
WHEN '325' THEN 'p6'
WHEN '330' THEN 'p7'
WHEN '335' THEN 'p8'
WHEN '340' THEN 'pd'
WHEN '345' THEN 'pe'
WHEN '350' THEN 'pf'
WHEN '355' THEN 'pg'
WHEN '360' THEN 'ph'
WHEN '365' THEN 'pi'
WHEN '370' THEN 'ps'
WHEN '375' THEN 'pt'
WHEN '380' THEN 'pu'
WHEN '385' THEN 'pv'
WHEN '390' THEN 'pw'
END As fdept,
'1900-01-01 00:00:00.000' As fendate,
1 As fnshift,
'HR' As fpaytype,
Employee_Master.PhoneNumber2, -- = 1
0.00 As freghr,
'123456789' As fssn,
Employee_Master.Prov, --fstate
Employee_Master.Postal, --fzip
'A' As emgcontact,
'1' As emgphone,
0.00 As fgross,
'CANADA' As fcountry,
'A' As fcemail,
'a' As faddress,
'1900-01-01 00:00:00.000' As fhiredate
FROM SQLSERVER.SourceDatabase.dbo.Employee_Master Employee_Master
INNER JOIN DestinationDatabase.dbo.Prempl Prempl ON CONVERT(int,Employee_Master.Empno) = Convert(int,Prempl.fempno)
WHERE Prempl.fempno IS NULL
Lowell
September 30, 2009 at 7:37 am
Hi Lowell,
All is perfection!
Thank you again and again, you have been an amazing help through this endeavor. You have been crystal clear in your explanations. Thank you again for opening a few more doors into the world of SQL. Do you have any suggestions on a course of study to accelerate my knowledge base?
Cheers,
blawrence
September 30, 2009 at 8:00 am
blawrence (9/30/2009)
Hi Lowell,All is perfection!
Thank you again and again, you have been an amazing help through this endeavor. You have been crystal clear in your explanations. Thank you again for opening a few more doors into the world of SQL. Do you have any suggestions on a course of study to accelerate my knowledge base?
Cheers,
blawrence
honestly, if you read the "Recent Posts" on this web site constantly, and copy and paste the examples here to SSMS, you will learn more by running the code than you ever would from a book,video, or other website.
reading the code, running it, and understanding the what it is doing is your #1 learning experience in my opinion.
Take this problem you had for example: by providing the actual table definitions,w e were able to build working sql stements that you could use against your data...not pseudocode you had to extrapolate from. when you see your data being manipulated, the learning is a lot easier.
good luck to you!
Lowell
October 21, 2009 at 12:04 pm
Hi Lowell,
Please follow this link and let me know what you think is the best plan of attack.
http://www.sqlservercentral.com/Forums/Topic805941-338-1.aspx#bm806666
Regards,
blawrence
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply