November 9, 2012 at 11:04 am
Hello -
I have a situation where my only means of importing some badly needed data into one of my databases can only be achieved via my own local instance, and a linked server setup.
When I initially tried to do this via the following T-SQL, it gives the following error...
SELECT [ShipmentID],[CarrierID],[InvoiceID],[Reference1],[Reference1Type],[Reference2],[Reference2Type],[WarehouseID],[ShipperID],[PackageCount],[ShipDate],[BillToName],[ShipToName],[ShipToCompanyName],[ShipToAdd1],[ShipToAdd2],[ShipToAdd3],[ShipToCity],[ShipToStateProv],[ShipToZip],[ShipToCountry],[ShipToCountryCode],[ShipToResidential],[ShipToValidated],[ShipToPhone],[ShipToEmail],[FlatRate],[ShipmentTrackingNumber],[CurrencyCode],[ChargeTypeID],[ShipmentItemTotal],[ShippingCost],[ShipmentTotal],[WeightUOM],[DimensionsUOM],[NotificationTypeList],[NotificationEmailList],[InsuranceTypeID],[SpecialInstructions],[IntlInvoiceDeclaration],[IntlInvoiceComments],[IntlDocTypeList],[UserID],[Ready2Transmit],[Transmitted],[ManifestTestSuccess],[ManifestTestResponse],[DateTimeCreated],[ShippingLabel]
INTO [LinkedServer].[LinkedDatabase].[dbo].[LinkedTable]
FROM [dbo].[MyLocalTable]
GO
Msg 117, Level 15, State 1, Line 3
The object name 'LinkedServer.LinkedDatabase.dbo.LinkedTable' contains more than the maximum number of prefixes. The maximum is 2.
So I created a Synonym on my local instance to attempt to get around this problem, but now I get the following error with that method...
SELECT *
INTO [dbo].[AMSP]
FROM [dbo].[MyLocalTable]
GO
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'AMSP' in the database.
I went over to the LinkedServer, but there is no newly created, or already existing LinkedTable as the error is suggesting.
Is there a way that I can get this to work? Or is what I am trying to do here just so ridiculous that it's just not doable?
Thank you all in advance for any and all help!
SQL_ME_RICH
November 9, 2012 at 11:18 am
Select Into is used to create a table. Insert Select is used to insert data into an existing table. You're using Select Into, and getting errors from that, because the object already exists.
Should look something like:
INSERT INTO [LinkedServer].[LinkedDatabase].[dbo].[LinkedTable]
SELECT [ShipmentID],[CarrierID],[InvoiceID],[Reference1],[Reference1Type],[Reference2],[Reference2Type],[WarehouseID],[ShipperID],[PackageCount],[ShipDate],[BillToName],[ShipToName],[ShipToCompanyName],[ShipToAdd1],[ShipToAdd2],[ShipToAdd3],[ShipToCity],[ShipToStateProv],[ShipToZip],[ShipToCountry],[ShipToCountryCode],[ShipToResidential],[ShipToValidated],[ShipToPhone],[ShipToEmail],[FlatRate],[ShipmentTrackingNumber],[CurrencyCode],[ChargeTypeID],[ShipmentItemTotal],[ShippingCost],[ShipmentTotal],[WeightUOM],[DimensionsUOM],[NotificationTypeList],[NotificationEmailList],[InsuranceTypeID],[SpecialInstructions],[IntlInvoiceDeclaration],[IntlInvoiceComments],[IntlDocTypeList],[UserID],[Ready2Transmit],[Transmitted],[ManifestTestSuccess],[ManifestTestResponse],[DateTimeCreated],[ShippingLabel]
FROM [dbo].[MyLocalTable]
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2012 at 11:23 am
Hey G!
Thank you so much for getting back to me so quickly. Yes - I am needing to do a SELECT INTO because the tables does not exist over on the linked server (as I am creating it and porting over the data...then I will be reapplying constraints and FKeys there after).
Is that not going to work for needing to create the table with?
November 9, 2012 at 11:28 am
P.S. The table not existing on the LinkedServer is by design...If the only way for me to get this to work is to create a blank copy of the table, less constraints/Fkeys, I can do that I suppose.
November 9, 2012 at 11:31 am
Taken from BOL, INTO Clause:
http://msdn.microsoft.com/en-us/library/ms188029.aspx
You cannot create new_table on a remote server; however, you can populate new_table from a remote data source.
November 9, 2012 at 11:32 am
Thank you! Sorry I did not find this myself in BOL.
November 9, 2012 at 11:34 am
I found it because I knew it was there, but it's not in the obvious place. 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply