CODEWITHSUNDEEP
sqlsql-serverdatabase
nikta
nikta

Reputation: 79

insert from linked server table in SQL server into local table

I wrote a query in SQL Server that select from linked table (using OPENQUERY) and inserting data into the local table. I need to insert only the new data that have been added to the linked table.So every time that I run the query doesn’t insert the previous data that have already inserted. I used this query:

 SET IDENTITY_INSERT DocumentCenter.dbo.tbl_Webhook ON;

insert into dbo.tbl_Webhook ( 
id,
event,
ip,
city,
domain,
campaignId,
deviceType,
clientType,
region,
clientName,
userAgent,
clientOS,
country,
messageId,
recipient,
campaignName,
timestamp,
token,
signature,
tag,
url,
mailingList,
my_var_1,
my_var_2,
messageHeaders,
attachmentX,
code,
error,
notification,
reason,
description,
CurrentTimestamp)
SELECT *  FROM OPENQUERY([162.241.181.144], 'SELECT * from tbl_Webhook') 
Where id > (Select max(id) From DocumentCenter.dbo.tbl_Webhook)

the part of SELECT * FROM OPENQUERY([162.241.181.144], 'SELECT * from tbl_Webhook') is working as I can see data but when I run the insert statment it give me (0 row(s) affected) and no error. I have already created same table in my local database. that the name of local database is DocumentCenter and table name is tbl_Webhook.

What did I miss?

I found the answer: I found id is null (Select max(id) From DocumentCenter.dbo.tbl_Webhook) because table is empty as the beginning. So I have added one row and then execute query and it works perfect.

Upvotes: 0

Views: 3908

Answers (1)

nikta
nikta

Reputation: 79

ok I found the answer: I found id is null )(Select max(id) From DocumentCenter.dbo.tbl_Webhook) because table is empty as the beginning. So I have added one row and then execute query and it works perfect.

Upvotes: 1

Related Questions