Insert into select error

Hello,
I have a temporary table #temp that i created using bulk insert from a csv file.
I have another database table called dbo.Plan with the same columns as the temp table.

I am trying to insert the data from the temp table into my actual database table.

IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL DROP TABLE #temp;

declare @M_Name varchar(255)
declare @M_Date datetime
declare @M_Author varchar(255)
declare @M_Dev varchar(255)
declare @M_Location varchar(255)
declare @M_Rev int
declare @sql_mp_BULK varchar(max)

-- Create a temporary table #temp to hold the data
CREATE TABLE #temp
(M_Name varchar(255),
M_Date datetime,
M_Author varchar(255),
M_Dev varchar(255),
M_Location varchar(255),
MISC varchar(255))

-- Read the text file into the #temp table
SET @sql_mp_BULK='BULK INSERT #temp FROM '''+@path+''' WITH (FIELDTERMINATOR ='','' ,ROWTERMINATOR = ''\n'' , FIRSTROW = 1, LASTROW=1)';

EXEC (@sql_mp_BULK)

--SELECT * FROM #temp

SET @M_Name= (SELECT TOP 1 M_Label from #temp)
SET @M_Date =(SELECT TOP 1 M_Date from #temp)
SET @M_Author= (SELECT TOP 1 M_Author from #temp)
SET @M_Dev =(SELECT TOP 1 M_Dev from #temp)
SET @M_Location=(SELECT TOP 1 M_Location from #temp)

declare @M_ID uniqueidentifier

--Check if the M_Name already exists, If it does then increment Rev by 1

SET @M_Rev=(SELECT [M_Name],
ROW_NUMBER() OVER(PARTITION BY [M_Name]
ORDER BY [M_Rev] ) as M_Rev
FROM dbo.plan)

--Insert into database table Plan

INSERT INTO .dbo.Plan
( [M_ID]
,[M_Name]
,[M_Dev]
,[M_Location]
,[M_Date]
,[M_Author]
,[M_Rev])

SELECT distinct @M_ID as M_ID,
@M_Name AS M_Name,
@M_Dev as M_Dev,
@M_Location AS M_Location,
@M_Date AS M_Date,
@M_Author AS M_Author,
@M_Rev as M_Rev
FROM #temp)

when I try to run it I am getting this error : Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can someone please help me with this.

Thanks!

remove the () from

(SELECT distinct @M_ID as M_ID,
@M_Name AS M_Name,
@M_Location AS M_Location,
@M_Date AS M_Date,
@M_Author AS M_Author,
@M_Rev as M_Rev
FROM #temp)

Hi Thanks for the reply.

I removed () from the select statement. I am still getting the same error

please post all of your code that is causing error?

I could post the query in the reply box.. it was giving an error.
I added the entire query to my main post on the top

This is wrong as it can produce multiple values, not to mention you have two columns (M_Name and M_Rev) going to one.

M_Name M_Rev
xyz 1
xyz 2
xyz 3
abc 1
abc 2
abc 3
abc 4

If the m_name already exists i want the rev to be incremented by 1
could you suggest a better way to increment rev values for the m_name column

...
SET @M_Rev = ISNULL((SELECT MAX(M_Rev) FROM dbo.[plan] WHERE M_Name = @M_Name), 0) + 1
...

Thanks for the reply.
I changed the query as you have mentioned
I ran the sp and it executed successfully without errors.
I am trying to give the text file path now

EXEC [dbo].[sp_Insert_M] '\folder location\test1.txt'
I am getting an error

Msg 8169, Level 16, State 2, Procedure sp_Insert_M, Line 69
Conversion failed when converting from a character string to uniqueidentifier.