insert into using a select statement
-
Hello all,
I'm new to SQL Server and I'm in charge of converting all of our SPs from Sybase ASA to SQL Server 2000 and I've run across insert (into a temp table) statements in a SP that don't compile in 2000. Sample below:
insert into #mat_sample(select * from mat_sample where mat_sample_id = @temp_matsampleid)
SQL Server gives me the following error:
Incorrect syntax near the keyword 'select'.
I've looked for examples of select statements associated with insert statements, but have not found any. Any help is appreciated.
-
Insert into #mat_samples (col1, col2, id) select col1, col2, id from mat_sample where...
-
insert into #mat_sample select * from mat_sample where mat_sample_id = @temp_matsampleidNote that the temp table #mat_sample must already be created.
-
an example from 'Books Online':
INSERT author_sales
SELECT 'SELECT', authors.au_id, authors.au_lname, SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id LIKE '8%'
GROUP BY authors.au_id, authors.au_lname
I'd recommend against doing the 'select *'. It's easier to debug and maintain the code if the columns are listed.
And then again, I might be wrong ...
David Webb -
Also note that it's a best pratice to always specify the target columns.
-
Thanks for the quick responses. Problem solved. I just needed to take out the parentheses around the select and it worked fine. Just looked in the wrong section of the help. Thanks again.
-
I'd suggest two things:
1. make sure #mat_sample has already been created.
2. The parenthesis generally are used to name the specific fields within the table, (in this case, #mata-sample) which are to recieve data. Hence, if #mat_sample and mat_sample are identical, (and do not contain iterated numbers for a Primary Key) then you should just be able to drop the parenthesis and this should work.
insert into #mat_sample
select * from mat_sample where mat_sample_id = @temp_matsampleid
OMG! I had no idea there were that many posts before I finished typing... Forget everything I just said....
I wasn't born stupid - I had to study.
-
Yep, and a very good best practice. My bad for not writing correct sample code, Remi is correct in that we should always try to be as correct as we can in our answers.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply