November 18, 2016 at 5:45 am
Hello everyone,
It seems that I get stuck with updating column data from Linked Server.
It would be great if someone could assist. 🙂
So, this is the updated script from Linked Server:
UPDATE [DATA STORE LINKED]...['ET NEW$']
SET [Data] = <Data, VarChar,>
,[Ot] = <Ot, VarChar,>
,[Year] = <Year, Double,>
,[Region] = <Region, VarChar,>
,[Country] = <Country, VarChar,>
,[Div] = <Div, VarChar,>
,[Type] = <Type, VarChar,>
,[Parts Used] = <Parts Used, VarChar,>
,[Jan] = <Jan, Double,>
,[Feb] = <Feb, Double,>
,[Mar] = <Mar, Double,>
,[Apr] = <Apr, Double,>
,[May] = <May, Double,>
,[Jun] = <Jun, Double,>
,[Jul] = <Jul, Double,>
,[Aug] = <Aug, Double,>
,[Sep] = <Sep, Double,>
,[Oct] = <Oct, Double,>
,[Nov] = <Nov, Double,>
,[Dec] = <Dec, Double,>
WHERE <Search Conditions,,>
GO
and now I want to update column "Parts" in this table (used Script table as ---> Create to):
CREATE TABLE [dbo].[Server_FINAL](
[Ot] [nvarchar](50) NULL,
[Region] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[Div] [nvarchar](50) NULL,
[Type] [nvarchar](50) NULL,
[Year] [int] NULL,
[Month] [nvarchar](50) NULL,
[ET] [float] NULL,
[ET NEW] [float] NULL,
[Hr] [float] NULL,
[Pred] [float] NULL,
[Red] [float] NULL,
[Rev] [float] NULL,
[SR] [float] NULL,
[TP] [float] NULL,
[Parts] [float] NULL
) ON [PRIMARY]
To conclude, table Server_FINAL.Parts need to update with [Parts Used] data from [DATA STORE LINKED]...['ET NEW$'].
Txh!
November 18, 2016 at 6:06 am
I've read your post a couple of times, but don't understand the problem. What do you mean by 'column data'?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 18, 2016 at 6:12 am
I mean update (fill with some values) one table column from another table column.
the trick is here I don't know how to connect Linked Server e.g.
update Server_FINAL
set Server_FINAL.Parts = [DATA STORE LINKED]...['ET NEW$'].[Parts Used]
and this doesn't work for me.
Make sense now?
November 18, 2016 at 6:15 am
tocy1980 (11/18/2016)
I mean update (fill with some values) one table column from another table column.the trick is here I don't know how to connect Linked Server e.g.
update Server_FINAL
set Server_FINAL.Parts = [DATA STORE LINKED]...['ET NEW$'].[Parts Used]
and this doesn't work for me.
Make sense now?
Try
[linkedserver].[database].[schema].[tablename]
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 18, 2016 at 6:26 am
I have tried this:
update Server_FINAL
set Server_FINAL.Parts = [Linked Servers].[DATA STORE LINKED].['ET NEW$'].[Parts Used]
and it doesn't work.
Get this error: he multi-part identifier "Linked Servers.DATA STORE LINKED.'ET NEW$'.Parts Used" could not be bound.
November 18, 2016 at 6:45 am
tocy1980 (11/18/2016)
I have tried this:update Server_FINAL
set Server_FINAL.Parts = [Linked Servers].[DATA STORE LINKED].['ET NEW$'].[Parts Used]
and it doesn't work.
Get this error: he multi-part identifier "Linked Servers.DATA STORE LINKED.'ET NEW$'.Parts Used" could not be bound.
Is there a link between the target table and the source table? The usual syntax would be something like this:
update t
set Parts = s.[parts used]
from Server_Final t
join [servername].[dbname].[schema].
s on t.col1 = s.col1
I suggest that you get a SELECT query working first, just to be sure that you have the connection working OK:
select top (10) *
from [servername].[dbname].[schema].
Is the name of your Linked Server really 'Linked Servers'? If yes, I suggest you change it to something more meaningful. If no, use the actual server name: you cannot refer to collections like this in T-SQL.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 18, 2016 at 6:53 am
Phil Parkin (11/18/2016)
tocy1980 (11/18/2016)
I have tried this:update Server_FINAL
set Server_FINAL.Parts = [Linked Servers].[DATA STORE LINKED].['ET NEW$'].[Parts Used]
and it doesn't work.
Get this error: he multi-part identifier "Linked Servers.DATA STORE LINKED.'ET NEW$'.Parts Used" could not be bound.
Is there a link between the target table and the source table? The usual syntax would be something like this:
update tset Parts = s.[parts used]
from Server_Final t
join [servername].[dbname].[schema].
s on t.col1 = s.col1
I suggest that you get a SELECT query working first, just to be sure that you have the connection working OK:
select top (10) *
from [servername].[dbname].[schema].
Is the name of your Linked Server really 'Linked Servers'? If yes, I suggest you change it to something more meaningful. If no, use the actual server name: you cannot refer to collections like this in T-SQL.
Sorry, still don't understand this as I am the newbie in SQL.
The name of my Linked Server is "DATA STORE LINKED". This linked server is actually excel file which has 20 sheets and I need the data/values from sheet "ET NEW" ---> column name "Parts Used" to put/update into Table "Server_FINAL" ---> column name "Parts"
November 22, 2016 at 11:10 am
Some news regarding this as I still haven't resolved this?
November 22, 2016 at 11:19 am
tocy1980 (11/22/2016)
Some news regarding this as I still haven't resolved this?
I stopped responding at the point where you mentioned that the linked server is an Excel spreadsheet, as I have no experience whatsoever of connecting to Excel like that ... I always just use SSIS to get info out of Excel.
Good luck.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 22, 2016 at 11:25 am
What is SSIS?
November 22, 2016 at 11:36 am
tocy1980 (11/22/2016)
What is SSIS?
SQL Server Integration Services is an ETL (Extract/TransformLoad) tool which is packaged with SQL Server.
It's good for moving data from A to B, where A and B are different data 'repositories'.
'Different' has a number of meanings:
Different databases
Different types of data storage (SQL Server, Access, CSV file, Oracle, etc)
Different physical locations (eg, transfers over FTP, HTTP)
As the 'Transform' bit may suggest, the data can be manipulated en route, to suit the target.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 22, 2016 at 11:40 am
November 22, 2016 at 11:51 am
tocy1980 (11/18/2016)
I have tried this:update Server_FINAL
set Server_FINAL.Parts = [Linked Servers].[DATA STORE LINKED].['ET NEW$'].[Parts Used]
and it doesn't work.
Get this error: he multi-part identifier "Linked Servers.DATA STORE LINKED.'ET NEW$'.Parts Used" could not be bound.
you have to get the linked server working first before you can use it for updates and joins
does this work at all?
why did you create a linked server with spaces in the name?
EXECUTE sp_tables_ex [DATA STORE LINKED]
SELECT mySheet.*
FROM [DATA STORE LINKED]...[ET NEW$] mySheet
Lowell
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply