September 27, 2009 at 10:33 am
How do I insert selected data from a linked server and insert it into the destination server?
For example:
I have a Table called Employee_Table in a remote SQL database named DatabaseSource (Linked Server named LinkedServer).
The Columns in Employee_Table are named columns EmployeeID (Value Number, 4), Employee FirstName, EmployeeLastName, ColumnNameA, ColumnNameS, ColumnNameD, ColumnNameD.
So I have this:
SELECT EmployeeID , Employee FirstName, EmployeeLastName, ColumnNameA,ColumnNameS, ColumnNameD,ColumnNameD
FROM LinkedServer.DatabaseSource. Employee_Table
Now how do I insert using the same query using MERGE to insert only new data from LinkedServer.DatabaseSource. Employee_Table?
(The destination data base named DestinationDatabase, destination table named Employees.)
INSERT INTO DestinationDatabase. Employees
The columns in Employees are named EmployeeNum (value Number, (prefix of 000000)10), EmployeeName, ColumnName1, ColumnName2, ColumnName3.
Now how do I schedule this to run as a weekly Job on every Sunday at midnight?
September 27, 2009 at 1:03 pm
you cross posted across a few forums...the first being a SQL 2000 forum.
The MERGE keyword is for SQL 2008 only, but all the same process can be duplicated with INSERT and UPDATE.
..using MERGE to insert only new data from ...
if you only need to insert, no need to use MERGE...just us a normal insert and left join.
first, lets get a REAL working SQL from your linked server: linked servers have 4 part naming conventions, so this is invalid:
SELECT EmployeeID , Employee FirstName, EmployeeLastName, ColumnNameA,ColumnNameS, ColumnNameD,ColumnNameD
FROM LinkedServer.DatabaseSource. Employee_Table
Let us know what it REALLY is...a real sql statement that you can copy/paste/test will help you much more than pseudo code you need to tweak and adapt.
anyway, to insert only what is new in your local table, you are going to join on that table to auto filter it:
--seriously, i'm sure these are aliases and not the actual column names or database names...help yourself and put real examples
USE DestinationDatabase
INSERT INTO Employees(EmployeeNum, EmployeeName, ColumnName1, ColumnName2, ColumnName3)
--first update the existing values in case something changed:, you said you don't need this, just inserts, but i put it for reference/others to understand:
UPDATE Employees
SET
EmployeeName = MyAlias.EmployeeFirstName + ' ' + MyAlias.EmployeeLastName,
ColumnName1 = MyAlias.ColumnNameA,
ColumnName2 = MyAlias.ColumnNameS,
ColumnName3 = MyAlias.ColumnNameD
FROM LinkedServer.DatabaseSource.dbo.Employee_Table MyAlias
INNER JOIN Employees ON MyAlias.EmployeeID = Employees.EmployeeNum
WHERE EmployeeName <> MyAlias.EmployeeFirstName + ' ' + MyAlias.EmployeeLastName,
OR ColumnName1 <> MyAlias.ColumnNameA,
OR ColumnName2 <> MyAlias.ColumnNameS,
OR ColumnName3 <> MyAlias.ColumnNameD
--then insert the new stuff
SELECT
MyAlias.EmployeeID ,
MyAlias.EmployeeFirstName + ' ' + MyAlias.EmployeeLastName,
MyAlias.ColumnNameA,
MyAlias.ColumnNameS,
MyAlias.ColumnNameD
FROM LinkedServer.DatabaseSource.dbo.Employee_Table MyAlias
LEFT OUTER JOIN Employees ON MyAlias.EmployeeID = Employees.EmployeeNum
WHERE Employees.EmployeeNum IS NULL --if it is null, no join was found, therefor it is new.
Lowell
September 28, 2009 at 10:06 am
Real Example:
LINKED SERVER
SQLSERVER.SOURCEDataBase.dbo.Employee_Master
,[Empno] char(9)
,[Surname] char(30)
,[GivenName] char(30)
,[Initial] char(1)
,[City] char(30)
,[BasicEarnGL] char(60)
,[Prov] char(2)
,[Postal] char(10)
,[PhoneNumber2] char(30)
[DestinationDatabase].[dbo].[prempl]
,[fempno] char(9)
,[fname] char(20)
,[ffname] char(20)
,[fmi] char(1) *=A
,[fbirthdate]datetime *=1900-01-01
,[fcity] varchar(35)
,[fdept] char(2)
,[fendate] datetime *=1900-01-01
,[fnshift] int*=1
,[fpaytype] char(2) *=HR
,[fphone] char(20)
,[freghr] numeric(7, 2) *=0.00
,[fssn] char(15) *=123456789
,[fstate] char(20)
,[fzip]char(10)
,[emgcontact] varchar(30) *=A
,[emgphone] char(20) *=1
,[fgross] numeric(11, 2) *=0.00
,[fcountry] char(25) *=CANADA
,[fcemail] varchar(60) *=A
,[timestamp_column] timestamp *=Auto
,[identity_column] int *=Auto
,[faddress] text *=a
,[fhiredate] datetime *=1900-01-01
*=Default Values
September 28, 2009 at 11:11 am
a huge help, thanks for the layout!
try this query..it has all the defaults you were looking for. if the query works, uncomment the INSERT portion and confirm that that peice works the way you expect.
/*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
MyLinkedServer.Empno,
MyLinkedServer.Surname,
MyLinkedServer.GivenName,
ISNULL(MyLinkedServer.Initial,'A') AS fmi,
'1900-01-01' As fbirthdate,
MyLinkedServer.City,
MyLinkedServer.BasicEarnGL, -- = [fdept]?
'1900-01-01' As fendate,
1 As fnshift,
'HR' As fpaytype,
MyLinkedServer.PhoneNumber2 -- = [fphone] i'm guessing, not the emgphone,
0.00 As freghr,
'123456789' As fssn,
MyLinkedServer.Prov, --fstate
MyLinkedServer.Postal, --fzip
'A' As emgcontact,
'1' As emgphone,
0.00 As fgross,
'CANADA' As fcountry,
'A' As fcemail,
'a' As faddress,
'1900-01-01' As fhiredate
FROM SQLSERVER.SOURCEDataBase.dbo.Employee_Master MyLinkedServer
LEFT OUTER JOIN [DestinationDatabase].[dbo].[prempl] MyLocalServer
ON MyLinkedServer.Empno = MyLocalServer.fempno
WHERE MyLocalServer.fempno IS NULL
Lowell
September 28, 2009 at 2:14 pm
Great help! Thanks...but I'm still getting errors. note below.
/****Is the syntax and structure correct?
Also, ‘DestinationDatabase.dbo.prempl.fempno’ has up to only four digits I need to remove all prefixed ‘0’s from the source data column SQLSERVER.SourceDatabase.dbo.Employee_Master.Empno where there may be up to eight prefixed ‘0’s in this character string.****/
[Code]--Select from source database, Linked server.
SELECT
Empno
,Surname
,GivenName
,Initial
,City
,BasicEarnGL
,Prov
,Postal
,PhoneNumber2
FROM SQLSERVER.SourceDataBase.dbo.Employee_Master
/**** VALUES are default values if no data exists from source table I prefer to set the defaults here not in MSSM Studio > Table design****/
--Insert into destination database.
USE DestinationDataBase.dbo.prempl
INSERT INTO dbo.prempl(
fempno
,fname
,ffname
,fmi VALUES (‘A’)
,fbirthdate VALUES (‘1900-01-01 00:00:00.000’)
,fcity
,fdept
,fendate VALUES (‘1900-01-01 00:00:00.000’)
,fnshift VALUES (‘1’)
,fpaytype VALUES (‘HR’)
,fphone
,freghr VALUES (‘0.00’)
,fssn VALUES (‘123456789’)
,fstate
,fzip
,emgcontact VALUES (‘A’)
,emgphone VALUES (‘1’)
,fgross VALUES (‘0.00’)
,fcountry VALUES (‘CANADA’)
,fcemail VALUES (‘A’)
,timestamp_column
,identity_column
,faddress VALUES (‘a’)
,fhiredate VALUES (‘1900-01-01 00:00:00.000’))
--First update the existing values in case something changed
UPDATE dbo.prempl
SET
SELECT
Fname = Employee_Master.Surname
,Ffname = Employee_Master.GivenName
,fcity = Employee_Master.City
,fdept = Employee_Master.BasicEarnGL
,fstate = Employee_Master.Prov
,fzip = Employee_Master.Postal
,fphone = Employee_Master.PhoneNumber2
FROM SQLSERVER.SourceDataBase.dbo.Employee_Master Employee_Master
INNER JOIN
Prempl ON Employee_Master.Empno = Prempl.fempno
WHERE
Fname <> Employee_Master.Surname /***removed commas due to error***/
OR ffname <> Employee_Master.GivenName
OR fcity <> Employee_Master.City
OR fdept <> Employee_Master.BasicEarnGL
OR fstate <> Employee_Master.Prov
OR fzip <> Employee_Master.Postal
OR fphone <> PhoneNumber2
FROM SQLSERVER.SourceDataBase.dbo.Employee_Master Employee_Master /****error now at ‘FROM’***/
LEFT OUTER JOIN
Prempl ON Employee_Master.Empno = Prempl.fempno
WHERE Prempl.fempno IS NULL –- IF null no join found, therefore new.[/Code]
September 28, 2009 at 2:18 pm
Hi Lowell,
Thanks again for this great start! Another question how do you format your replies in the scroll box like that?
Regards,
blawrence
September 28, 2009 at 2:23 pm
[\ code] (Without the spaces)
I'm looking at your problem too... I can't see anything that's wrong with Lowell's statement... maybe i'm blind.
September 28, 2009 at 8:33 pm
[ code] --a sql comment [ /code ] <-- with no spaces makes your code become syntax highlighted
--a sql comment
if you are using preceeding zeros, when you are doing the update, you probably want to convert to integer in your joins:
FROM SQLSERVER.SourceDataBase.dbo.Employee_Master Employee_Master
INNER JOIN
Prempl ON CONVERT(int,Employee_Master.Empno) = Convert(int,Prempl.fempno)
now when you are inserting, and you want preceeding zeros, (which i don't recommend, but sometimes we inherit stuff we can't change right away)
SELECT '000000000000' + Employee_Master.Empno
--or
SELECT RIGHT('000000000000' + Employee_Master.Empno,12)
--or
SELECT RIGHT(REPLICATE('0',12) + Employee_Master.Empno,12)
if your source had upt to 4 prceeding zeros, but your destination has more, you build the append way too many zeros, then use the RIGHT function to get the length you want(12 i think? 4 digits plus up to 8 zeros?)
Lowell
September 29, 2009 at 8:50 am
Thankyou very much for your help so far... but the voyage continues.
My apology for not articulating the requirement in proper detail. I need to remove the leading zeros from the source data.
Also, another issue related to this distributed query is that I need to manipulate and convert data from the source database to the destination database in an interesting way and I am wondering if I should use IF THEN or CASE SELECT?
As an example:
Get only 16th,17th,18th number from the Source Column [BASICEarnGL]:
999-999-9999-99110-000000
999-999-9999-99120-000000
999-999-9999-99130-000000
Result:
110
120
130
Destination Coumn [fdept]
Convert 110 to become: c1
Convert 120 to become: c2
Convert 130 to become: c3
Etc…
September 29, 2009 at 9:06 am
Hi Lowell,
In this example you provided, I do not understand how, for instance ‘A’ becomes the default insert value for column [fmi] using an AS statement? I thought AS was only used for the creation of an alias?
[Code]SELECT
MyLinkedServer.Empno,
MyLinkedServer.Surname,
MyLinkedServer.GivenName,
ISNULL(MyLinkedServer.Initial,'A') AS fmi,
'1900-01-01' As fbirthdate,
MyLinkedServer.City,
MyLinkedServer.BasicEarnGL, -- = [fdept]?
'1900-01-01' As fendate,
1 As fnshift,
'HR' As fpaytype,
MyLinkedServer.PhoneNumber2 -- = [fphone] i'm guessing, not the emgphone,
0.00 As freghr,
'123456789' As fssn,
MyLinkedServer.Prov, --fstate
MyLinkedServer.Postal, --fzip
'A' As emgcontact,
'1' As emgphone,
0.00 As fgross,
'CANADA' As fcountry,
'A' As fcemail,
'a' As faddress,
'1900-01-01' As fhiredate[/Code]
Cheers,
blawrence
September 29, 2009 at 9:10 am
easy enough.
Stripping preceeding zeros, you just convert to int.
for the rest, here's an example:
--results:
exString Substr CaseResult
999-999-9999-99110-000000 110 c1
999-999-9999-99120-000000 120 c2
999-999-9999-99130-000000 130 c3
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'
SELECT exString,
SUBSTRING(exString,16,3) ,
CASE SUBSTRING(exString,16,3)
WHEN '110' THEN 'c1'
WHEN '120' THEN 'c2'
WHEN '130' THEN 'c3'
ELSE 'c4' --anything unaccounted for?
END
FROM #Example
Lowell
September 29, 2009 at 9:20 am
this one is specific to the data:
ISNULL(MyLinkedServer.Initial,'A') AS fmi,
your original data source has what i though is the middle initial of the name, right? (if that's wrong, let me know)
but it is possible that the data is NULL on the source server, so I assumed you wanted to put an 'A' only when the source was NULL.
as for the AS [fmi], it's just good practice to provide an alias for the columns, even if they are hardcoded...too easy for your eys to cross, and try to put the default date in the lastname field...so i aliased every value with what is supposed to be the destination column... makes reading it and identifying where it's supposed to go easier.
blawrence (9/29/2009)
Hi Lowell,In this example you provided, I do not understand how, for instance ‘A’ becomes the default insert value for column [fmi] using an AS statement? I thought AS was only used for the creation of an alias?
[Code]SELECT
MyLinkedServer.Empno,
MyLinkedServer.Surname,
MyLinkedServer.GivenName,
ISNULL(MyLinkedServer.Initial,'A') AS fmi,
'1900-01-01' As fbirthdate,
MyLinkedServer.City,
MyLinkedServer.BasicEarnGL, -- = [fdept]?
'1900-01-01' As fendate,
1 As fnshift,
'HR' As fpaytype,
MyLinkedServer.PhoneNumber2 -- = [fphone] i'm guessing, not the emgphone,
0.00 As freghr,
'123456789' As fssn,
MyLinkedServer.Prov, --fstate
MyLinkedServer.Postal, --fzip
'A' As emgcontact,
'1' As emgphone,
0.00 As fgross,
'CANADA' As fcountry,
'A' As fcemail,
'a' As faddress,
'1900-01-01' As fhiredate[/Code]
Cheers,
blawrence
Lowell
September 29, 2009 at 9:26 am
Hi Lowell,
Thank you very much for your amazing help and willingness to explain.
Cheers,
blawrence
September 29, 2009 at 10:49 am
Error on this line of code. I have tried many variations...just not getting it.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '='.
[Code]MyLinkedServer.PhoneNumber2 = [fphone] [/Code]
Error in my previous explaination.
I believe it is because [fphone] field should only accept '1' as a value.
So how do I force (in T-SQL) not a default value, but all insert values in this column to = '1'?
September 29, 2009 at 10:58 am
instead of using the column name [fphone], simply change it to the static value of '1':
MyLinkedServer.PhoneNumber2 = '1'
your code seems to be doing stuff differently....are you updating the Linkedserver now, or your local copy of it? previously you were selecting from the linked server.
i think you mean for it to be this partial snippet: you removed the comment:
SELECT....
'HR' As fpaytype,
'1' As fphone, -- = [fphone] i'm guessing, not the emgphone,
0.00 As freghr,
....
now if you want to change any current values, it's a seperate update:
UPDATE YOURTABLE SET fphone='1'
Lowell
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply