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

General SQL Server Forums

New to SQL Server Programming

Need help

Author

Topic

norty911
Starting Member

41 Posts

Posted - 2007-04-23 : 12:38:37

Hey guys,
Is there a way to combine the following two queries:

SELECT tbl_store.fk_storeid into #temp
FROM tbl_store INNER JOIN
tbl_user ON tbl_store.fkid = tbl_user.uid
WHERE (tbl_user.Card_Type = 'xxx') AND (tbl_store.ValidationType = 'xxx') AND (tbl_store.date_created BETWEEN CONVERT(DATETIME, '2007-04-20 00:00:00', 102) AND CONVERT(DATETIME, '2007-04-21 00:00:00', 102))
GROUP BY tbl_user.fk_storeid

SELECT(*) FROM #temp

Into 1 query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-23 : 12:44:51

SELECT tbl_store.fk_storeid into #temp
FROM tbl_store INNER JOIN
tbl_user ON tbl_store.fkid = tbl_user.uid
WHERE tbl_user.Card_Type = 'xxx' AND tbl_store.ValidationType = 'xxx' AND tbl_store.date_created >= '20070420' AND tbl_store.date_created < '20070422'
GROUP BY tbl_user.fk_storeid

union all

SELECT * FROM #temp

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-23 : 12:49:15

Peso

not sure if you can do it in one statement. note, there's a SELECT INTO. ==> the temp table does not exist prior to this statement.

norty911

You are inserting the records into a table (and creating it at the same time). can you tell is what is the need to get the 2 queries into one?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-23 : 12:50:39

Why do you want to combine both queries? One is inserting into temp table and the other is selecting from it. If you want the output from temp table, it should be a seperate query.

If you try to combine both, you'll get an error, since you are trying to insert into #temp, selecting from the same #temp.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-23 : 12:54:58

SELECT fk_storeid into #temp
FROM (select * from tbl_store INNER JOIN
tbl_user ON tbl_store.fkid = tbl_user.uid
UNION ALL SELECT * FROM #temp) AS f
WHERE Card_Type = 'xxx' AND ValidationType = 'xxx' AND date_created >= '20070420' AND date_created < '20070422'
GROUP BY fk_storeid

Peter Larsson
Helsingborg, Sweden

norty911
Starting Member

41 Posts

Posted - 2007-04-23 : 13:15:22

Well I'm using .net 2.0 to fill a SqlDataReader and instead of using two command objects, I'd like to use one.

Thanks guys for the replies so far.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-23 : 13:21:32

It is best to put the SQL Statements in a stored proc and call the proc from your application. The proc can have multiple T-SQL statements.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

norty911
Starting Member

41 Posts

Posted - 2007-04-23 : 13:45:22

Thanks man, appreciate it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources