Post reply

Can not insert into a table

  • April 27, 2009 at 7:51 am

    #208877

    I get the message: Ambiguous column name 'SSN' when I execute the following SQl statement. what is wrong?

    Declare @EMAILID uniqueidentifier

    Declare @EmailTypeID int

    Set @EmailTypeId = 1

    Set @EmailID = '823FDE61-5348-4C29-884A-38093A9C6DD4'

    SELECT SSN

    into #tblRecords

    FROM dbo.MyTable

    --insert missing SSNs into tblUserInfo

    INSERT INTO dbo.tblLoans_EmailLog (NotificationID, SSN, EmailTypeID, EmailSentDate, EmailAddressMissing)

    SELECT @EmailID, SSN, @EmailTypeId, getdate(), OSBDate, AnniversaryDate, CASE WHEN ISNULL(U.AKO_ID.ID, '')='' THEN '1' ELSE NULL END

    FROM #tblRecords R

    Left join dbo.tblUserInfo U on R.SSN = U.SSN

  • April 27, 2009 at 8:10 am

    #984364

    select @EmailID, r.ssn, @EmailTypeId,........

    or

    select @EmailID,u.ssn. @EmailTypeId,........

    not just select ssn

  • April 27, 2009 at 8:15 am

    #984371

    Thanks . It worked.

  • April 27, 2009 at 8:18 am

    #984375

    Faye Fouladi (4/27/2009)

    I get the message: Ambiguous column name 'SSN' when I execute the following SQl statement. what is wrong?

    Declare @EMAILID uniqueidentifier

    Declare @EmailTypeID int

    Set @EmailTypeId = 1

    Set @EmailID = '823FDE61-5348-4C29-884A-38093A9C6DD4'

    SELECT SSN

    into #tblRecords

    FROM dbo.MyTable

    --insert missing SSNs into tblUserInfo

    INSERT INTO dbo.tblLoans_EmailLog (NotificationID, SSN, EmailTypeID, EmailSentDate, EmailAddressMissing)

    SELECT @EmailID, SSN, @EmailTypeId, getdate(), OSBDate, AnniversaryDate, CASE WHEN ISNULL(U.AKO_ID.ID, '')='' THEN '1' ELSE NULL END

    FROM #tblRecords R

    Left join dbo.tblUserInfo U on R.SSN = U.SSN

    In your very last select statement, it is joined to two tables. Each of which have a SSN column. In the column list of the select statement, you reference the column name of SSN. To sql, this is ambiguous... it doesn't know from which table you want to get this column.

    Since you are doing a LEFT JOIN, if you get it from #tblRecords, then there will always be a value in this column. However, if you get it from tblUserInfo, and there isn't a record in this table for the corresponding SSN, then your result will have a NULL value instead.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 4 posts - 1 through 3 (of 3 total)

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