insert from linked server table in SQL server into local table

Question

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.

does it return any rows when you execute only the select statement without insert statement ??? — M.Ali
– M.Ali, Commented Dec 14, 2013 at 1:26
I would avoid using select * in your select statements when this becomes production code as any future changes to the remote table schema are likely to cause problems. I would list the column names again. — sahmeepee
– sahmeepee, Commented Dec 14, 2013 at 2:08
What does Select max(id) From DocumentCenter.dbo.tbl_Webhook on its own yield? — Aaron Bertrand
– Aaron Bertrand, Commented Dec 14, 2013 at 3:25

nikta · Accepted Answer · 2013-12-16 19:16:45Z

1

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.

answered Dec 16, 2013 at 19:16

nikta

793 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

insert from linked server table in SQL server into local table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related