How to insert data into table from linked server

Question

How to insert data into table from linked server

SpeedSkaterFan

SSCrazy

Points: 2716
More actions
November 19, 2013 at 11:57 am

#282651

Hi all,
I wonder if it possible to move data from tables on a linked server to a "normal database"?
I am struggling with the code and can't work it out. What am I doing wrong?
Maybe some extra information,
Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName
Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country
This is just a test setup. I figure that if I get this working the rest will be easier.
My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')
Thanks in advance
Hein

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Maybe an SSIS package would be a better option if your doing a big load.

To insert to a "local" table using a linked server, just use the insert into as with any other table but using the fully qualified name.

INSERT INTO Destination_Table

SELECT *

FROM [LinkedServer].[LinkedServerDB].[LinkedServerSchema].[LinkedServerTable]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 2

Hi Louis,

Thanks for your reply

I have changed the code to:

select * from openquery(COVAS,'

INSERT INTO [CovasCopy].[dbo].[Countries].[Country]

SELECT [cntNameISU] FROM [Covas].[db_covas].[tblCountries]')

This gives me the error message:

Msg 7357, Level 16, State 2, Line 2

Cannot process the object "

INSERT INTO [CovasCopy].[dbo].[Countries].[Country]

SELECT [cntNameISU] FROM [Covas].[db_covas].[tblCountries]". The OLE DB provider "MSDASQL" for linked server "COVAS" indicates that either the object has no columns or the current user does not have permissions on that object.

I am sure that both tables have columns (I have made the "normal"table an hour ago and to be sure that it exists I have manually added 1 record.

Another thing: I want to do this excercise with ± 15 tables, the biggest having 2.5 million rows.

I understand that going an other way (SSIS) is a better option?

Hein

Lowell SSC Guru Points: 323504 More actions · Answer 3

the openquery for a simple select is what you are after, it's just what you do witht eh results(sticking them into your local table instead of displaying)

INSERT INTO [CovasCopy].[dbo].[Countries].[Country] (ColumnList?)

select * from openquery(COVAS,'

SELECT [cntNameISU] FROM [Covas].[db_covas].[tblCountries]')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 4

Hi Lowell

Thanks for putting me on the right track (SQL Server is new to me, let alone a linked server).

You made me realise that only the query part concerning the linked server needs the openquery part.

The (working) code I have now looks like:

USE [CovasCopy]

GO

INSERT INTO [dbo].[Countries]

([Country])

select * from openquery(COVAS,'select cntNameISU from db_covas.tblcountries')

GO

Maybe I am permitted an extra question: Louis mentioned using SSIS for larger amounts of data.

What is considered a large amount of data?

The biggest table I want to do this excercise with has now 2.5 million rows. Is this to much for this approach?

Anyway, thanks for your help

Hein

Lowell SSC Guru Points: 323504 More actions · Answer 5

my personal rule of thumb, based on my own experiences, is that if a table has more than 50K rows, i'd rather use SSIS (or The Import/Export Wizard, technically still SSIS), or maybe bcp.exe /out and in.

SSIS and BCP handle huge numbers of rows with ease using SQL Bulk Insert.

I've personally exported/imported a 14 gig table (zip+4 database) in just a few minutes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

A "large amount of data" might vary depending on the infrastructure of your company.

If you right click on your database and then choose Tasks-> Import Data... you will get a wizard to import your data using SSIS and you can later review the package if you choose to save it.

Within my environment 2.5 million rows are not considered as a large amount of data but maybe you have more restriction on your hardware.

If you found that copying the query works for you, then probably that's the best option for you now.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 7

Hi Louis

Thanks for the update!

As said SQL Server is new to me.

I have found the Import and export data (64 bit) option in SQL Server

I understand that this is not "real SSIS" but its getting close.

I will dig into this and hopefully am able to work things out

Thanks to you and Lowell for helping me out!

Hein

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

The Import/Export Wizard creates a "real SSIS" package, which you can save and open to edit or just review it and maybe run it again.

It's a great tool for starters but it's limited in many ways. Try it and choose whatever you prefer.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2