SELECT AS @Var - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT AS @Var

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-09-14 : 04:15:51

Hi All,

I am trying to SELECT something and assign that to a var, so that I can do something with it. Can you help me get it assigned?

I assumed (lol though I am wrong) it would be along the lines of;


SELECT 
  ID AS @ID 
FROM 
  Customer 
WHERE 
  (
   Sub Query
  )

"Impossible is Nothing"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-14 : 04:20:22

[code]
Declare @ID int
SELECT
@ID = ID
FROM
Customer
WHERE
(
Sub Query
)

[/code]

But Make sure that your query returns only one result set.

Chirag

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-09-14 : 06:50:01

Thanks for the help...

Can you help see my bug?

"Impossible is Nothing"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-14 : 07:31:24

Why first comma in list of values (check the red part):

Insert into SomeTable
Values (,@code,....)

You can't do that to skip providing value to first field. Specify fields explicitly for which you want to insert values like this:

Insert into SomeTable(col2, col4, col7)
values(@val2, @val4, @val7)

Harsh Athalye
India.
"Nothing is Impossible"

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-09-14 : 09:04:19

OK thanks harsh.

The first column is an ID field that is auto increment. I just want it to accept its next value.

Can somebody possibly add to this?

"Impossible is Nothing"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-14 : 09:07:08

Name the columns and exclude the id.

insert tbl (col1, col2, col3)
select aaaa, dddd, ssss

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 09:18:42

[code]INSERT INTO Correspondance
(
CodeColumn,
Col2,
Col3,
Col4,
Col5,
Col6,
ID
)
SELECT cc.Code,
'09/07/2006',
'Mail Shot',
cc.ContactName,
'Safety Works Mail Shot',
'Newsletter',
cc.ID
FROM CustCon cc
INNER JOIN Customers c ON c.Code = cc.Code
WHERE c.Status IN ('A', 'B', 'C')
AND cc.ContactType = 'A'[/code]
Peter Larsson
Helsingborg, Sweden

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-09-15 : 09:31:55

Ah I think I have my question wrong then.

The 'Mail Shot', 'Newsletter' etc values are just new values I want to add. They do not exist so I wouldnt be selecting them.
These values I need to add to certain customers contacts but due to the original DBA's schema I have to join the customers table in order to get the appropriate contact...

So do I name the colums? im not entirely sure I get the above 2 examples for my situation.

Off-T: our entire DB consists of about 120 tables and there are no foreign keys, and no relationships what so ever. period. I barely sleep at night, you should see the state of some of the tables they would make you laugh... well cry actually lol.

Anyway I really appreciate your help on this one.

"Impossible is Nothing"

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-09-29 : 04:35:10

@ Anyone for reference...

This is how I got it working, this was the second query I needed to run which is why the syntax is ever so slightly different.

One thing though, I always here how cursors are evil... I dont know what I would do without them.

"Impossible is Nothing"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 04:37:35

What is wrong with my suggestion posted 09/14/2006 : 09:18:42?
Here it is again, with slight modification to new critiera

INSERT		Correspondence
		(
			Code,
			Date,
			Type,
			Contact,
			Summary,
			Content
		)
SELECT		cc.Code,
		'09/07/2006',
		'Mail Shot',
		cc.Surname,
		'Safety Works Mail Shot',
		'Newsletter'
FROM		CustCon cc
INNER JOIN	Customer c ON c.Code = cc.Code
WHERE		cc.[Contact type] IN ('B', 'G')
		AND c.[Ind Sector] = 'DAEX-S'

Peter Larsson
Helsingborg, Sweden

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-09-29 : 05:07:12

Hey Peso,

I will confess, I have been away and while I was away I wrote the solution, I never actually ran yours as I wasnt back to view it, for that I apologise, I always like to tie things up on boards though, so if anyone searches sometime you can actual see a solution.

Thanks! =)

Pace

"Impossible is Nothing"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 05:44:50

As you can see, most thing are doable without cursors.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-29 : 07:35:19

Sadly too many things are also doable with cursors