SQL Server Performance Forum – Threads Archive

Variable as argument for INTO clause

Hi, I’m a bit frustrated; I have some code that works which I want to put in a loop, where a
variable changes in the WHERE claue, and a variable changes as the file name argument for the INTO clause. It fails on the INTO statement, saying "syntax error". Here’s a stripped down version of the code:
DECLARE @MgrName varchar(30)
DECLARE @MgrFile varchar(30)
DECLARE @MgrCount smallint
SET @MgrCount = 1
WHILE @MgrCount < 3
BEGIN
IF @MgrCount = 1
BEGIN
@MgrName = ‘MgrOne’
@MgrFile = ‘dbo.tblMgrOne’
END
IF @MgrCount = 2
BEGIN
@MgrName = ‘MgrTwo’
@MgrFile = ‘dbo.tblMgrTwo’
END
SELECT
REP.RepName INTO
@MgrFile FROM
dbo.tblRep AS REP WHERE
REP.MgrName = @MgrName SET @MgrCount = @MgrCount + 1
END Obviously, I want the reps from MgrOne into tblMgrOne, and the reps from MgrTwo into tblMgrTwo. I have about 50 managers to deal with, I need to update this regularly, and I thought this would be a more elegant solution than cutting and pasting the code 50 times. Eventually, to get the job started, I did that for the first five teams, and that code executed flawlessly, so I know my SELECT, WHERE, etc. code is correct. I actually SELECT a bunch of other columns as well, but that’s not where my problem is. I do a lot of coding in Visual Basic, and this type of structure works well. So, I’m confused that when I look into my manual, and all it says for the syntax of INTO is INTO table_name Am I missing something spectacularly obvious here? Or is this just forbidden? Thanks in advance for any help.

You dont insert into variables. You SET them.
SET @Foo = (SELECT Foo)

Haywood, Thanks for the prompt reply, but I just want to clarify: it’s NOT the SELECT clause
that’s causing the error, it’s the INTO clause. So are you suggesting that I should
change it to: SET @MgrFile = ‘INTO tblMgrFileOne’ instead of what I had?

Oh, dang.. In the code in my original post (I’m doing this from home by memory),
I posted:
If @MgrCount = 1
BEGIN
@MgrName = ‘MgrOne’
@MgrFile = ‘dbo.tblMgrOne’
END What I actually have coded at work was: If @MgrCount = 1
BEGIN
SET @MgrName = ‘MgrOne’
SET @MgrFile = ‘dbo.tblMgrOne’
END
So I just wanted to clarify I was using the correct syntax to SET
the variables.

I’ve worked with people who have transitioned from VB to SQL in the past, and there is one key thing to understand. SQL server is SET based in it’s processing of queries and data. It is not a row-by-row walk type of system as you would do in VB. In SQL you work with data in sets. The server is written to work in sets of data and not row-by-row comparison. Your examples lead me to believe that you are thinking in the VB ‘mentality’ and that does not work in SQL. Unfortunatly the examples don’t work and are actually not executable. I suggest that you look for a tsql tutorial online or in Books Online. It will help make your transition into SQL much easier. I’m not trying to be rude, or elitist, but your missing some of the basics of SQL and set based processing. It’s imperative that you learn the correct methods in order for you to succeed.

Why don’t you just create 3 tables and then execute 3 insert commands. I don’t see any reason for a loop here.

Like Haywood hinted, you cannot use variables in T-SQL to refer to objects. If you have to use variables for object names, you have to use dynamic SQL, which is basically constructing the query statement with fixed text parts, concatenating the variable object names, into a string-type variable. You can then execute the string that contains the complete statement. DECLARE @SQL VARCHAR(200)
DECLARE @MgrName varchar(30)
DECLARE @MgrFile varchar(30)
DECLARE @MgrCount smallint
SET @MgrCount = 1
WHILE @MgrCount < 3
BEGIN
IF @MgrCount = 1
BEGIN
@MgrName = ‘MgrOne’
@MgrFile = ‘dbo.tblMgrOne’
END
IF @MgrCount = 2
BEGIN
@MgrName = ‘MgrTwo’
@MgrFile = ‘dbo.tblMgrTwo’
END SET @SQL = ‘SELECT REP.RepName INTO [‘ + @MgrFile + ‘] FROM dbo.tblRep AS REP ‘
+ ‘WHERE REP.MgrName = ”’ + @MgrName + ”” EXEC (@SQL) SET @MgrCount = @MgrCount + 1
END The ”’ and ”” bits are to make sure that the final string has single quotes around the @MgrName string. However, your SELECT … INTO … statement is actually creating a new table, called the same name that is defined by @MgrName, with a single column called RepName, and with all the RepNames that meet the criteria you have set. And it does this 3 times in succession. It’s quite likely not producing the result that you were looking for – but that in itself isn’t really clear to begin with. ****************** Anyway, you don’t need to move data around between objects just to see extracts from a table – you just query the table with criteria: SELECT REP.RepName FROM dbo.tblRep REP WHERE REP.MgrName = ‘<mgr_name>’ This returns the list you need.

"Anyway, you don’t need to move data around between objects just to see extracts from a table – you just query the table with criteria: SELECT REP.RepName FROM dbo.tblRep REP WHERE REP.MgrName = ‘<mgr_name>’ This returns the list you need" Hi Adriaan, thanks for your detailed reply; I do appreciate it. But what I’m trying to do is write that list into a file – I have no problems getting the results on screen – so that it can be queried by an Excel file which the individual managers use. I actually extract much more than RepName – we get a bunch of performance indicators, which managers use to coach and evaluate performance. I want it set up so I just run the SQL in the morning, and the managers can then open their Excel reports at leisure. And I can’t run it all into one giant file (we have 50 managers, each with 10-20 reps) because it then takes too long for the Excel queries to happen. I didn’t want to re-write the entire list of code, but I’m querying for about 40 indicators, and for some reason, I thought 256 characters was the limit for a varchar variable, so I never thought about trying your EXEC (@SQL) method. (Probably a hangover from Visual Basic!) But seeing as varchars can be 8,000 chars, I’ll give it a try. As I said, using the "brute force" approach of copying my code 4 times, and just changing manager name and output file name into each of the four sections works fine, but I think that’s inelegant and more difficult to maintain. I wanted to find the better way to do it. And Haywood, I don’t think you’re being rude or elitist to point out the differences between Set based approaches, and procedural approaches, but 1) I already understand that (my instructor stressed it quite heavily as he knew my background was procedural languages), and 2) my query doesn’t do any line-by-line stuff; it’s the control code outside that does, and if SQL doesn’t work that way, why does my text have a whole section about flow of control? Clearly, there are some elements of procedural computing involved with SQL as well. The answer to my question was simply that SQL doesn’t let you use a variable as the argument to the INTO clause. That wasn’t documented anywhere, and that’s why I was confused. And I appreciate everyone taking the time to respond. Thanks!

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by KevinB55 And Haywood, I don’t think you’re being rude or elitist to point out the differences between Set based approaches, and procedural approaches, but 1) I already understand that (my instructor stressed it quite heavily as he knew my background was procedural languages), and 2) my query doesn’t do any line-by-line stuff; it’s the control code outside that does, and if SQL doesn’t work that way, why does my text have a whole section about flow of control? Clearly, there are some elements of procedural computing involved with SQL as well. <hr height="1" noshade id="quote"></blockquote id="quote"> I’m glad you have a good understanding of set theory then. However, your examples could be interpreted a couple ways (as evidenced by some of the other responses). That was why I dropped the examples and went straight for the ‘psst, buddy….’ approach. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Why don’t you use SQL 2005’s equivalent of DTS (as it was known in SQL 7.0 and 2000) to export the data to Excel sheets?

quote:Originally posted by KevinB55 The answer to my question was simply that SQL doesn’t let you use a variable as the argument to the INTO clause. That wasn’t documented anywhere, and that’s why I was confused.

This article might be helpful to you:
http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.html Thanks, Name
———
Dilli Grg (1 row(s) affected)

]]>