Search code examples
sqljoinlinked-serverinsert-into

SQL Linked Tables, INSERT INTO & JOINs

I am new so please be gentle.

Been using SQL server 2008r2 to create a working database based on information pulled from a larger, proprietary SQL database on another server. I am able to pull info into the new table using an INSERT INTO function, but when I attempt to add a linked field from another table on the linked database via a join, I get nowhere. Without further ado, here is my query in all its newbish glory:

INSERT INTO [Cust_Info].[dbo].[form_data]
        (CustNo
        ,LastName
        ,FirstName
        ,Add1
        ,Add2
        ,City
        ,State
        ,Zip
        ,Phone1
        ,Phone2
        ,Email)
SELECT      [CustNo]
        ,[LastName]
        ,[FirstName]
        ,[Add1]
        ,[Add2]
        ,[City]
        ,[State]
        ,[Zip]
        ,[Phone1]
        ,[Phone2]
        ,[Email]

FROM [XYZ-SERVER-1\ETC].[Service].[dbo].[Customer]
JOIN [XYZ-SERVER-1\ETC].[Service].[dbo].[EmailInv] 
ON [Cust_Info].[dbo].[form_data].[CustNo]=[XYZ-SERVER-1\ETC].[Service].[dbo].
[EmailInv].[CustNo]

I am sure I am making a very noob mistake, but I continue to get "Msg 4104"s on the "ON" line, and a "Msg 209" (Ambiguous column name 'CustNo') on the first line of the select statement.

I have sorted through many related posts on different forums, and by my account this should work. Can one of you vets please show this noob the light?

Thank you in advance.

Solution

  • Well, one thing I notice is you are attempting to INSERT INTO your table [Cust_Info].[dbo].[form_data] but you are not JOINing on this table in the JOIN statement. So your JOIN syntax does not appear to be correct. I am guessing you are trying to JOIN this way, please notice that I used aliases:

    INSERT INTO [Cust_Info].[dbo].[form_data]
(
    CustNo
    ,LastName
    ,FirstName
    ,Add1
    ,Add2
    ,City
    ,State
    ,Zip
    ,Phone1
    ,Phone2
    ,Email
)
SELECT [CustNo] -- which table is this from select the alias? 
    ,[LastName]
    ,[FirstName]
    ,[Add1]
    ,[Add2]
    ,[City]
    ,[State]
    ,[Zip]
    ,[Phone1]
    ,[Phone2]
    ,[Email]

FROM [XYZ-SERVER-1\ETC].[Service].[dbo].[Customer] c
JOIN [XYZ-SERVER-1\ETC].[Service].[dbo].[EmailInv] e
    ON c.[CustNo]=e.[CustNo]

    but you also need to specify which table, each of the columns is coming from. For example, the [CustNo] in the SELECT is in two tables, which one do you need?