How I solve the error, 'column name in table is specified more than once' in a SQL query

Question

0.00/5 (No votes)

See more:

Hi All,

I have same column name in two different tables. And I'm inserting data in a third table where i got the error. Please suggest.

SQL

DECLARE	@CLIENT_ID INT = 152,
@ReportDate DATETIME = '2022-08-05'
DECLARE @COMPLETEDSTATUS NVARCHAR(MAX)
SELECT @COMPLETEDSTATUS = Batch_status from [dbo].[Tran_Prod_Entry] WITH(NOLOCK) where Batch_Status IN ('For-QC')

IF OBJECT_ID('tempdb..##TempProductionData') IS NOT NULL  
   DROP TABLE ##TempProductionData  

SELECT 
	BM.Batch_id ,
	TPP.Ntlg,
	(SELECT ((CAST(CAM.Emp_ID AS VARCHAR(10))) + ' - ' + NAME) FROM CLIENT_ACCESS_MASTER CAM WHERE CAM.Ntlg = TPP.Ntlg)[User Name],
	TPP.Batch_id ,
	(CASE WHEN DATEPART(hour,TPP.Entered_date) = 0 THEN (CAST((23) AS Varchar(10))) ELSE (CAST((DATEPART(hour,TPP.Entered_date)-1) AS Varchar(10))) END + '-' + CAST((DATEPART(hour,TPP.Entered_date)) AS Varchar(10))) [Hour]
	INTO ##TempProductionData
	FROM Batch_Master BM WITH(NOLOCK)
	INNER JOIN Tran_Prod_Entry TPP WITH(NOLOCK) ON BM.Batch_id = TPP.Batch_Id
	AND TPP.Batch_Status IN (@COMPLETEDSTATUS)
	AND Client_id = @CLIENT_ID  AND  TPP.Entered_Date =  @ReportDate

Error

Column names in each table must be unique. Column name 'Batch_id' in table '##TempProductionData' is specified more than once.

What I have tried:

How I solve the error,'Column name in table is specified more than once' in a SQL query

Some one pls suggest.

Posted 15-Sep-22 22:31pm

Jaichithra

Updated 16-Sep-22 0:28am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2022-09-15T23:30:00

Solution 1

The message is pretty specific: you cannot have two columns in teh smae table with the same name:

SQL

SELECT 
	BM.Batch_id ,
...
	TPP.Batch_id ,
...
	INTO ##TempProductionData

You need to use AS on at least one of the columns so SQL can tell which you are referring to.

Posted 15-Sep-22 23:30pm

OriginalGriff

Comments

Jaichithra 16-Sep-22 6:31am

Thank you for the suggestion, however post using AS I'm getting below error

Error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT
BM.Batch_id AS B_ID,
TPP.Ntlg,
(SELECT ((CAST(CAM.Emp_ID AS VARCHAR(10))) + ' - ' + NAME) FROM CLIENT_ACCESS_MASTER CAM WHERE CAM.Ntlg = TPP.Ntlg)[User Name],
TPP.Batch_id ,
(CASE WHEN DATEPART(hour,TPP.Entered_date) = 0 THEN (CAST((23) AS Varchar(10))) ELSE (CAST((DATEPART(hour,TPP.Entered_date)-1) AS Varchar(10))) END + '-' + CAST((DATEPART(hour,TPP.Entered_date)) AS Varchar(10))) [Hour]
INTO ##TempProductionData
FROM Batch_Master BM WITH(NOLOCK)
INNER JOIN Tran_Prod_Entry TPP WITH(NOLOCK) ON BM.Batch_id = TPP.Batch_Id
AND TPP.Batch_Status IN (@COMPLETEDSTATUS)
AND Client_id = @CLIENT_ID AND TPP.Entered_Date = @ReportDate

OriginalGriff 16-Sep-22 6:47am

That's a different problem: the inner SELECT is returning multiple results, and you can't do that.