459,293 Members | 1,673 Online
Need help? Post your question and get tips & solutions from a community of 459,293 IT Pros & Developers. It's quick & easy.

# Create variables with same format in one single step?

 P: n/a I would need to create multiple variables with the same format for future update, what I did is listing them separately, is there an easy way to combine them in one step? please see query below. Thanks a lot! --Current Query-- SELECT cast(0.0 as money) as balance_1 ,cast(0.0 as money) as balance_2 ,cast(0.0 as money) as balance_3 ,cast(0.0 as money) as balance_4 ,cast(0.0 as money) as balance_5 ,cast(0.0 as money) as balance_6 ,cast(0.0 as money) as balance_7 ,cast(0.0 as money) as balance_8 ,account_no ,XXX INTO table1 FROM account Can I do something like this? This one didn't work. SELECT balance_1 to balance_8 (cast 0.0 as money) ,account_no ,XXX INTO table1 FROM account Jul 23 '05 #1
5 Replies

 P: n/a It's not a single step but this might work for you: This might work for you: declare @balance_1 money, @balance_2 money, @balance_3 money, @balance_4 money, @balance_5 money, @balance_6 money, @balance_7 money, @balance_8 money set @balance_1 = 0 set @balance_2 = 0 set @balance_3 = 0 set @balance_4 = 0 set @balance_5 = 0 set @balance_6 = 0 set @balance_7 = 0 set @balance_8 = 0 SELECT @balance_1, @balance_2, @balance_3, @balance_4, @balance_5, @balance_6, @balance_7, @balance_8, account_no, XXX INTO table1 FROM account Jul 23 '05 #2

 P: n/a I'm confused. There are no variables in the code you posted - you are creating a table with eight columns. There is only one step in a query - the whole query operates as one logical unit. What you seem to be looking for is a syntax shortcut that saves you typing out the column names. The best shortcut is probably to click and drag a list of names from the Object Browser in Query Analyzer then search and replace the rest. I expect you could do that in much less time than it took to write out this question. :-) Your query itself looks a little strange. Are you sure you can't calculate the balances with a single query rather than SELECT followed by an UPDATE / INSERT? -- David Portas SQL Server MVP -- Jul 23 '05 #3

 P: n/a (ro******@gmail.com) writes: I would need to create multiple variables with the same format for future update, what I did is listing them separately, is there an easy way to combine them in one step? please see query below. Thanks a lot! --Current Query-- SELECT cast(0.0 as money) as balance_1 ,cast(0.0 as money) as balance_2 ,cast(0.0 as money) as balance_3 ,cast(0.0 as money) as balance_4 ,cast(0.0 as money) as balance_5 ,cast(0.0 as money) as balance_6 ,cast(0.0 as money) as balance_7 ,cast(0.0 as money) as balance_8 ,account_no ,XXX INTO table1 FROM account Can I do something like this? This one didn't work. SELECT balance_1 to balance_8 (cast 0.0 as money) ,account_no ,XXX INTO table1 FROM account You could build an string with the SQL statement by iterating from 1 to 8, and use EXEC() to execute that statement. However, this is definnitely not recommendable. I have no idea what you are up to, but columns are usually distinct entities. If you find that you need balance_1 to balance_8, maybe you should make them rows instead. That is how you work with array data in SQL, rows with pairs (index, value). -- Erland Sommarskog, SQL Server MVP, es****@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp Jul 23 '05 #4

 P: n/a To answer the question I think you are trying to ask: No, there are not arrays in SQL. You create a table with a CREATE TABLE statement and you have to know what it looks like before you type in the command. You need to put the column names in the SELECT list. Using the SELECT * option is a really bad idea for production code. You need to give every column a meaningful name from your data model. But fromthe look of this, you do not have a data model and are writing code on the fly. Jul 23 '05 #5

 P: n/a Thank you ALL so much!! I do have a bunch of other variables in the table (about 20), I only listed account_no as an example of the variables. Doing the calculation in a single step instead of using update is a great idea, but for some reason, it created duplicates. I haven't found out if it is due to the database duplicates or because of my query. I will do further research... Thanks again... --CELKO-- wrote: To answer the question I think you are trying to ask: No, there are not arrays in SQL. You create a table with a CREATE TABLE statement and you have to know what it looks like before you type in the command. You need to put the column names in the SELECT list. Using the SELECT * option is a really bad idea for production code. You need to give every column a meaningful name from your data model. But fromthe look of this, you do not have a data model and are writing code on the fly. Jul 23 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.