CODEWITHSUNDEEP
sqljoin
user2934274
user2934274

Reputation: 1

"Invalid Column" when using column from table variable

I'm trying to declare a table variable and then join it to a table I created in the database. Every time I try to insert my "NAME" field into my table, I get the error 'Invalid Column Name "NAME"', even though the GNAME field works fine. What am I doing wrong, and how can I join me NAME column?

DECLARE @Names TABLE
(
    ID INT,
    NAME VARCHAR(100),
    GNAME VARCHAR(100)
)

INSERT INTO @Names
(
    ID,
    NAME,
    GNAME
)

SELECT
    CName.ID,
    Ref.NAME,
    Ref.GNAME
FROM
    @CurrentPositions AS CName
LEFT OUTER JOIN
    dbo.NameField AS Ref
ON 
    CName.ID = Ref.ID

    IF ( OBJECT_ID('dbo.ReportTable', 'U') IS NOT NULL)
    DROP TABLE dbo.ReportTable

CREATE TABLE [dbo].[ReportTable]
(
    [ID_NUMBER] [INT],
    [NAME] [VARCHAR](150)
    [GNAME] [VARCHAR](150)
)

INSERT INTO [dbo].[ReportTable]
(
    ID_NUMBER,
    NAME,
    GNAME
)

SELECT
    C.ID_NUMBER,
    N.NAME,
    N.GNAME
FROM
    @Names AS N
INNER JOIN
    @CurrentPositions AS C
ON  N.ID_NUMBER = C.ID_NUMBER

Upvotes: 0

Views: 811

Answers (2)

Timothy Walters
Timothy Walters

Reputation: 16884

It is quite possible that all you need to do is wrap your field names in square brackets, e.g.

INSERT INTO @Names
(
    [ID],
    [NAME],
    [GNAME]
)

SELECT
    CName.[ID],
    Ref.[NAME],
    Ref.[GNAME]
FROM
    @CurrentPositions AS CName
LEFT OUTER JOIN
    dbo.NameField AS Ref
ON 
    CName.[ID] = Ref.[ID]

If that doesn't fix it, please post the schema of your @CurrentPositions and dbo.NameField tables.

Upvotes: 0

Fuzzy
Fuzzy

Reputation: 3810

Try using a Temp table :

CREATE TABLE #Names 
(
    ID INT,
    NAME VARCHAR(100),
    GNAME VARCHAR(100)
)

INSERT INTO #Names
(
    ID,
    NAME,
    GNAME
)
SELECT
    CName.ID,
    Ref.NAME,
    Ref.GNAME
FROM
    #CurrentPositions AS CName
LEFT OUTER JOIN
    dbo.NameField AS Ref
ON 
    CName.ID = Ref.ID

    IF ( OBJECT_ID('dbo.ReportTable', 'U') IS NOT NULL)
    DROP TABLE dbo.ReportTable

CREATE TABLE [dbo].[ReportTable]
(
    [ID_NUMBER] [INT],
    [NAME] [VARCHAR](150)
    [GNAME] [VARCHAR](150)
)

INSERT INTO [dbo].[ReportTable]
(
    ID_NUMBER,
    NAME,
    GNAME
)

SELECT
    C.ID_NUMBER,
    N.NAME,
    N.GNAME
FROM
    #Names AS N
INNER JOIN
    #CurrentPositions AS C
ON  N.ID_NUMBER = C.ID_NUMBER

I've assumed that you will also change the table variable @CurrentPositions to a temp table

Just remember to drop the tables after you use them.

Upvotes: 1

Related Questions