Create variables with same format in one single step?

[rong.guo]

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

[bdooley123]

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

[David Portas]

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
--

[Erland Sommarskog]

(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

[--CELKO--]

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.

[rong.guo]

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.