January 15, 2010 at 12:21 pm
hi,
I am writing a stored proc where I query a table for a distinct list of values. & then for each row returned, i need to insert a record into a temp table and i'm having trouble.
--presently, 2 rows are returned when i do this:
SELECT DISTINCT Name + '_' + Type + '_' + CONVERT(CHAR(8),GETDATE(), 112) AS [expression]
FROM records WHERE processed = false
--create temp table
CREATE TABLE #Temp(FileId INT, filePath VARCHAR(50))
--declare variable to hold expression
DECLARE @expression VARCHAR(1000)
SET @expression = (SELECT DISTINCT Name + '_' + Type + '_' + CONVERT(CHAR(8),GETDATE(), 112) AS [expression] FROM table1 WHERE processed = false)
--insert value from variable into tempTable
INSERT INTO #Temp EXEC(@expression)
when i execute the proc, i get this error:
.... returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.' message
I know that this problem is because more than one row is returned and the variable can't store multiple rows, but i'm stuck on how to fix it.
How can i dynamically assign row by row to the variable?
January 15, 2010 at 12:36 pm
ragie (1/15/2010)
hi,I am writing a stored proc where I query a table for a distinct list of values. & then for each row returned, i need to insert a record into a temp table and i'm having trouble.
How can i dynamically assign row by row to the variable?
Why?
insert into #Temp(filePath)
select distinct
Name + '_' + Type + '_' + CONVERT(CHAR(8),GETDATE(), 112)
from
dbo.records
where
processed = false;
January 16, 2010 at 6:47 pm
Thanx Lynn. i don't know why i didn't see that!
quick question, is it possible to do an INSERT SELECT like this:
INSERT INTO Files (Dir, filePath)
SELECT('O', filePath) FROM #Temp
it's a bit different than the regular cuz i have a hardcoded 'O' in there. SSMS is telling me that i have bad syntax.
January 16, 2010 at 7:45 pm
ragie (1/16/2010)
Thanx Lynn. i don't know why i didn't see that!quick question, is it possible to do an INSERT SELECT like this:
INSERT INTO Files (Dir, filePath)
SELECT('O', filePath) FROM #Temp
it's a bit different than the regular cuz i have a hardcoded 'O' in there. SSMS is telling me that i have bad syntax.
You need to post the table defs (CREATE TABLE statements), and some sample data (INSERT INTO statements) for each of the tables. Only about 3 or 4 records for each.
January 16, 2010 at 9:02 pm
sorry bout that Lynn.
i'm writing a sp where the goal is to get two output parameters: the fileid, and filepath. as you saw from the first post, i populate a temp table. next, i want to insert those values into a real table as well as add an additional hardcoded column into the real table. after that, i will do an inner join btwn the temp and real table, and get what i need. here you go:
--create proc with params
CREATE PROCEDURE [dbo].[procName]
(
@fileID INT OUTPUT,
@filepath varchar(500) OUTPUT
)
--create temp table and populate it
CREATE TABLE #Temp(fileID INT, filePath VARCHAR(50))
INSERT INTO #Temp (filePath)
)
SELECT DISTINCT Name + '_' + Type + '_' + CONVERT(CHAR(8),GETDATE(), 112)
FROM Records WHERE Processed = False
)
--insert values from temp into real table
(HERE IS WHERE ssms is complaining about syntax & it obviously has to do with the 'O')
INSERT INTO Files (Dir, FilePath)
(
SELECT('O', filePath) FROM #Temp
)
SELECT * FROM #Temp INNER JOIN Files ON fileID = fileID
RETURN @fileID
RETURN @filepath
DROP TABLE #Temp
END
January 16, 2010 at 9:19 pm
ragie (1/16/2010)
sorry bout that Lynn.i'm writing a sp where the goal is to get two output parameters: the fileid, and filepath. as you saw from the first post, i populate a temp table. next, i want to insert those values into a real table as well as add an additional hardcoded column into the real table. after that, i will do an inner join btwn the temp and real table, and get what i need. here you go:
--create proc with params
CREATE PROCEDURE [dbo].[procName]
(
@fileID INT OUTPUT,
@filepath varchar(500) OUTPUT
)
--create temp table and populate it
CREATE TABLE #Temp(fileID INT, filePath VARCHAR(50))
INSERT INTO #Temp (filePath)
)
SELECT DISTINCT Name + '_' + Type + '_' + CONVERT(CHAR(8),GETDATE(), 112)
FROM Records WHERE Processed = False
)
--insert values from temp into real table
(HERE IS WHERE ssms is complaining about syntax & it obviously has to do with the 'O')
INSERT INTO Files (Dir, FilePath)
(
SELECT('O', filePath) FROM #Temp
)
SELECT * FROM #Temp INNER JOIN Files ON fileID = fileID
RETURN @fileID
RETURN @filepath
DROP TABLE #Temp
END
I need the table defintion for the table Files. Please provide it as a CREATE TABLE statement.
January 16, 2010 at 9:33 pm
ok.
CREATE TABLE [Files]
(
[fileid] [int] IDENTITY(1,1) NOT NULL,
[dir] [char](1) NOT NULL, --this is 'O'
[filepath] [varchar](8000) NULL, --this is from #temp
[createdon] [datetime] NULL, --this is getDate
(
January 16, 2010 at 9:36 pm
What is the exact error message you are getting.
January 16, 2010 at 9:45 pm
incorrect syntax near ','.
when i take out the 'O', i don't have it, so....
January 16, 2010 at 10:15 pm
one more thing.......i want to learn sql really well. i know it will come with experience, but i wanna learn it quicker than that:)
do you suggest books, online course, tutorials....??? I will dive into whatever you suggest, so please give me your thought on this.
thanks!
Ragie - thirsty for sql
January 16, 2010 at 10:28 pm
Should have seen this to begin with, here is what you need to do:
Change:
INSERT INTO Files (Dir, FilePath)
(
SELECT('O', filePath) FROM #Temp
)
To:
INSERT INTO Files(Dir, FilePath)
SELECT 'O', filePath FROM #Temp;
January 18, 2010 at 7:40 am
THX again.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply