October 20, 2009 at 11:46 am
I am using a linked server named ‘SQLServer’. The query runs perfectly and without errors but does not transfer unique employees from the source database and enter them into the destination database… any ideas?
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])
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
Regards,
blawrence
October 20, 2009 at 11:48 am
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
What is performance like on the base query, that you say is running? I hate to see INNER JOINs on linked servers. You might try moving data from SQLSERVER.SourceDatabase.dbo.Employee_Master into a #temp table and then join the #temp table to DestinationDatabase.dbo.Prempl. Alternatively, move just the fempnos from the Prempl table over to the SourceDatabase, and then do the INNER JOIN there.
While you're at it, you could convert the datatype in the temp table so that you can join without wrapping the columns in CONVERT() functions.
Both are pretty much guaranteed to slow you down.
However, taking you at your word that the SELECT query runs fine without the INSERT statement, and that the INSERT throws no errors, yet inserts no rows, I can only think that perhaps you have multiple environments and are perhaps pushing data to a different place from the one where you are checking for results.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 2:39 pm
performance on the base query is fast (less then one sec.,) against aprox 600 rows of data from the source database and aprox 400 in the destination...if it is working...still no errors and no results!
Another issue that has come up is when I run the SELECT statement in isolation most of the fdept data return as 'NULL' and the remainder as 'c1'... any ideas?
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
Regards,
blawrence
October 20, 2009 at 2:57 pm
It's a long shot only: are you sure that
SUBSTRING(Employee_Master.BasicEarnGL,16,3)
returns all values besides '110'? This the nulls and 'c1' would suggest that the substring is returning something not covered by your case statement in most cases.
Regards
Piotr
...and your only reply is slàinte mhath
October 21, 2009 at 8:12 am
What is the architecture of a temp table in this situation? Could you provide an explaination using the data provided?
Regards,
blawrence
October 21, 2009 at 8:22 am
If PREMPL table is large, I'd bring the employee master data over. But you said your join was running acceptably fast?
SELECT convert(int,Empno) as Empno, other columns.....
INTO #Employee_Master
FROM SQLSERVER.SourceDatabase.dbo.Employee_Master Employee_Master
CREATE CLUSTERED INDEX #IX_EmpNo on #employee_master (Empno)
SELECT columns...
FROM #Employee_Master Employee_Master
INNER JOIN DestinationDatabase.dbo.Prempl Prempl ON EmpNo = Convert(int,Prempl.fempno)
WHERE Prempl.fempno IS NULL
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 21, 2009 at 10:39 am
Is it possible that this is the issue:
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
What this was designed to do was grab employee numbers from:
SQLSERVER.SourceDatabase.dbo.Employee_Master.Empno
Then truncate all prefixed zeros (i.e. 000001202) to get this: (i.e. 1202)
Compare the result against employee numbers in:
DestinationDatabase.dbo.Prempl.fempno
If the number from:
SQLSERVER.SourceDatabase.dbo.Employee_Master.Empno being compared against:
DestinationDatabase.dbo.Prempl.fempno
are unique then populate:
DestinationDatabase.dbo.Prempl
Regards,
blawrence
October 21, 2009 at 10:46 am
Let's start over and go slowly.
Does your SELECT query display the results you expect when you run it without the INSERT statement?
If so, please post up some sample rows and what you would expect to see in the target table after these rows have been inserted.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 21, 2009 at 2:04 pm
Everthing worked correctly and transfered the data.
I changed the: INNER JOIN to: LEFT OUTER JOIN except it did not truncate the prefixed zeros from the source employee numbers.SQLSERVER.SourceDatabase.dbo.Employee_Master.Empno
Any ideas?
Regards,
blawrence
October 21, 2009 at 3:15 pm
This is what you are inserting.
SELECT
Employee_Master.Empno,
You aren't converting the data in your SELECT, just in your JOIN. You need to convert it here for it to be inserted as if it were an INT.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 22, 2009 at 6:30 am
Regarding the trucation and this statement:
SELECT
Employee_Master.Empno,
I'm not following you. Could you please show me how and where in the query I would place this reference?
Regards,
Blair
October 22, 2009 at 7:20 am
INSERT INTO [DestinationDatabase].[dbo].[prempl]
.
.
.
SELECT
-- Employee_Master.Empno,
CONVERT(int,Employee_Master.Empno),
Employee_Master.Surname,
Employee_Master.GivenName,
.
.
.
FROM SQLSERVER.SourceDatabase.dbo.Employee_Master Employee_Master
.
.
.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 22, 2009 at 7:57 am
In doing so do I continue with the convert functions in the joins?
October 22, 2009 at 9:07 am
Of course, if it is necessary to make the JOIN work correctly.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 22, 2009 at 2:07 pm
Hi Bob,
Thank you again for you help. All is working well.
Regards,
Blair
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply