SELECT INTO statement

Question

Post reply

SELECT INTO statement

Eunice-245470

SSC-Addicted

Points: 487
More actions
September 16, 2005 at 2:36 am

#89052

Hi,
I have a SELECT statement stored into a variable @sql.
Which looks something like this 'Select col1, col2, col3 from myTable'
I would like to do something like this:
set @sql = 'select * into #mytemp ' + @sql + '; SELECT * from ##mytemp'
exec (@sql)
BUT, I am getting an error of "Server: Msg 263, Level 16, State 1, Line 1
Must specify table to select from."
Am I doing something wrong? Or missing anything?
Please advise. Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Note that #myTemp and ##myTemp are two different tables.

I can't see anything obviously wrong.

I recomend you replace the exec(@sql) with print @sql to see exactly what the server's going to execute. It could be the most obvious of problems. If you get the sql statement and still can't see anything wrong, please post it here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 2

From the looks of things you're not specifying the table that you want to select from

select * into #mytemp from <mytable>;

Basically you have missed off the second bit in red then you're following up by selecting from a different version of mytemp (global temp table rather than the local one you've specified), you're not going to get anything back unless you fix the first bit and loose one of the #'s in the second table name.

David Burrows SSC Guru Points: 65142 More actions · Answer 3

As already mentioned, different table names and syntax error

you need something like this

set @sql = replace(@sql,' from ',' into ##mytemp from ') + '; SELECT * from ##mytemp'

And I presume you have a reason to do this other than executing the original sql ?

Far away is close at hand in the images of elsewhere.
Anon.

Eunice-245470 SSC-Addicted Points: 487 More actions · Answer 4

Well, yes, I don't know if I have any other choice than to do this.

David, do you remember that you helped me with the sql statement with the date columns previously (http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=206752&p=3)?

I encountered another problem. I need to retrieve the data from a few tables.

The JobHeader and JobLines tables will be named something like 'CoAJobHeader', 'CoAJobLines', 'CoBJobHeader', 'CoBJobLines'. These Company names will be kept in a separate table called 'Company'. I was contemplating on retrieving the data based on the script you helped me with by adding the 'union' command and looping the Company table. So that I will get all the data from all the companies even if new companies are added in future.

However, the 'union' command will add more characters to the sql statement when I am running the quarterly view (90 days). Which is why, I figured, the next best thing is to retrieve the data company by company and adding it into a temp table and I just need to retrieve all the data from the temp table.

If there is a better way to do this, please enlighten me.

Thanks.

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Simply, if possible I would add the Company key to the select statement, wrap the whole statement as a subquery and join the Company table at that point.

I think you are reaching the bounds of a single query and most people would suggest that this is suited for client processing.

However, my suggestion would be break up the query into smaller queries and use a temp table (I presume why you posted the original question).

So roughly put

1. Create temp table with Company Key,ResourceDesc,JobNo and columns numbered 1 to 31 (for months in the case of my query)

2. Populate the table with Company Key,ResourceDesc,JobNo

3. Update each column, one at a time

4. Select data from temp table (use dynamic sql for column names)

If possible can you post full ddl and some sample input / output

Far away is close at hand in the images of elsewhere.
Anon.

Eunice-245470 SSC-Addicted Points: 487 More actions · Answer 6

David, when you said 'Update each column one at a time', does that mean that I will have to do a loop?

David Burrows SSC Guru Points: 65142 More actions · Answer 7

Yes. As I said before I think you are pushing the limits of sp_executesql which has a limit of nvarchar(4000). EXEC allows you to concatenate several strings together e.g EXEC(@string1+@string2+@string3) where each string can be a max of varchar(8000).

You could break the query in to sections (select/joins/where etc) providing you do not exceed 8000 for each section.

It is best to experiment with performance between one single complex query vs simplified updates (with loop) and select.

Far away is close at hand in the images of elsewhere.
Anon.