| Author |
Topic |
|
kthakore
Starting Member
4 Posts |
 Posted - 2004-02-19 : 14:51:16
|
| Hello,I am trying to write a SELECT statement that gets a part of the Table's name dynamically. What I want to know is how can I find out if that table has any values in it using such a dynamic select statement. I have tried EXISTS around EXECUTE but that is not permitted, I tried using SELECT COUNT(*) INTO @cntr ... but that does not work either. I need something of this form:EXISTS(EXECUTE('SELECT COUNT(*) INTO @cntr FROM myTable_'+@alias))But this statement does not work. Does anybody have an answer for this? I am sure somebody would have run into this..Thanks,Kushal |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-19 : 14:56:06
|
| EXEC('SELECT COUNT(*) FROM myTable_' + @alias)Why do you want to put the result set into another table? Do you want the result set put into a variable instead?Tara |
 |
|
|
kthakore
Starting Member
4 Posts |
Posted - 2004-02-19 : 15:00:14
|
| Tara,I don't want to put the result set into another table. I just need to know if there is naything in 'myTable_'+@alias table. I need to have an IF clause around that statement so that I can perform an insert into that table if there is nothing in there or else simply print a message saying a record exists. This is not embedded in any other language as well, I want pure Transact-SQL statements. I am just having trouble coming up with the right syntax to do that. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-19 : 15:07:08
|
| Since you are using dynamic sql, you'll need to do it all in one EXEC.EXEC('IF EXISTS (SELECT * FROM myTable' + @alias + ') PRINT 0 ELSE INSERT INTO myTable' + @alias + ' SELECT * FROM TableWhereDataExists')Tara |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-02-19 : 15:10:52
|
| [code]IF EXISTS( SELECT 1 --SO.Name as Tables, SI.rows as Rows FROM sysindexes as SI INNER JOIN sysobjects SO on SO.id = SI.id AND SO.type = 'U' WHERE (SI.indid = 0 OR SI.indid = 1) and SO.Name != 'dtproperties' and SO.Name = 'myTable_' + @alias AND SI.Rows > 0) PRINT 'Records Exist'ELSE PRINT 'Perform Insert'[/code] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-19 : 15:12:07
|
| Ah, great use of system tables to avoid dynamic sql.Tara |
|
|
|
kthakore
Starting Member
4 Posts |
Posted - 2004-02-19 : 15:23:51
|
| Thanks a lot guys for all the help.. I tried ehorn's version and it works like a charm..Thank you,Kushal |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-19 : 15:30:50
|
| Now where did that thread go when we were discussing banging away against the catalog?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-19 : 15:33:29
|
| Yeah, but for this, it might be ideal because of COUNT(*).Tara |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-02-19 : 15:51:02
|
quote:
Originally posted by X002548
Now where did that thread go when we were discussing banging away against the catalog?
Maybe this one Brett,http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32457PS: Kushal, Give this thread a read if you have time especially Robvolk's mention of - DBCC UPDATEUSAGE. |
|
|
|
kthakore
Starting Member
4 Posts |
Posted - 2004-02-19 : 16:24:33
|
| I'll do that ehorn. Thanks. |
|
|
|
|
Dynamic Select statement