SQLTeam.com | Weblogs | Forums

Insert into select error

tsql

#1

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!


#2

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)


#3

Hi Thanks for the reply.

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


#4

please post all of your code that is causing error?


#5

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


#6

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


#7

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


#8

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


#9

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.