select query table with attaching column name

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

Transact-SQL (2005)

select query table with attaching column name

Author

Topic

vinay789
Starting Member

40 Posts

Posted - 2010-01-12 : 02:11:17

Hi to all,
I am developing a T-SQL with two while loops. I am retriving data from database in my first select query i am retriving one column based on this cloumn in the next select query i want to add it to the table name for example
DECLARE B1 CURSOR READ_ONLY
FOR
SELECT [spaceid] FROM [dbo].[siteownerspaces]
OPEN B1
FETCH NEXT FROM B1 INTO
@spaceid
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE C1 CURSOR READ_ONLY
FOR

SELECT A.fromdate,Ad.memail from dbo.myadvertises@spaceid A JOIN [dbo].[membershipdet] Ad ON A.advid=Ad.compid
OPEN C1
FETCH NEXT FROM C1 INTO
@fromdate, @memail
here spaceid is a column and myadvertises is the table name my problem is how to add this column to myadvertisers table pls solve this issue

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-12 : 02:17:52

Read about Dynamic SQL in BOL

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vinay789
Starting Member

40 Posts

Posted - 2010-01-12 : 02:29:28

Thankyou Senthil,
For your quick reply i am new bie to ms sql server. I have read your article but i am unable to find where i have strucked can you make an example.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 02:31:23

[code]
DECLARE B1 CURSOR READ_ONLY
FOR
SELECT [spaceid] FROM [dbo].[siteownerspaces]
OPEN B1
FETCH NEXT FROM B1 INTO
@spaceid
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE C1 CURSOR READ_ONLY
FOR

SELECT A.fromdate,Ad.memail from dbo.myadvertises@spaceid A JOIN [dbo].[membershipdet] Ad ON A.advid=Ad.compid
OPEN C1
FETCH NEXT FROM C1 INTO
@fromdate, @memail
[/code]
why does you table name come from another field? i dont think this is a good approach. perhaps you can explain your scenario.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-12 : 02:32:25

Declare @table_name varchar(100)
set @table_name='department'

exec('Select * from '+@table_name)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-12 : 02:33:58

Go through this

http://www.sommarskog.se/dynamic_sql.html
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vinay789
Starting Member

40 Posts

Posted - 2010-01-12 : 05:37:59

DECLARE B1 CURSOR READ_ONLY
FOR
SELECT [spaceid] FROM [dbo].[siteownerspaces]
OPEN B1
FETCH NEXT FROM B1 INTO
@spaceid
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE C1 CURSOR READ_ONLY
FOR

SELECT A.fromdate,Ad.memail from dbo.myadvertises(' + @spaceid +') A JOIN [dbo].[membershipdet] Ad ON A.advid=Ad.compid
OPEN C1
FETCH NEXT FROM C1 INTO
@fromdate, @memail
I have done like this but still its getting an error:Invalid object name 'staging1.dbo.myadvertises'.
Any hepl will greatly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 05:43:57

you need to use exec or sp_executesql. look into syntax in books online

vinay789
Starting Member

40 Posts

Posted - 2010-01-12 : 05:51:01

hi
Thanks for your reply i want only how to concatenate column name with table name
i.e., dbo.myadvertises(' + @spaceid +') here @spaceid is column name and myadvertises is the table name. Any help will greatly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 05:52:24

quote:
Originally posted by vinay789

hi
Thanks for your reply i want only how to concatenate column name with table name
i.e., dbo.myadvertises(' + @spaceid +') here @spaceid is column name and myadvertises is the table name. Any help will greatly appreciated

cant understand why you need to concat columnname like this. can you explain?

vinay789
Starting Member

40 Posts

Posted - 2010-01-12 : 06:07:41

yes
i have seperate tables like TABLE1,TABLE2,TABLE3...... in sql server db
and these 1,2,3.... are columns in one table i am doing job scheduling task for my TSQL query i want to check the fromdate everyday so for this purpose i want to check in all tables TABLE1,TABLE2,TABLE3......
Any help will greatly appreciated

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-12 : 08:29:11

Many here have given you the starting foundation to what you need. You can not write a table name as a variable directly, it must be stored in a string and then run with Exec ( ) or sp_executesql. By 'dynamically' they mean that you are building the select statement off of conditions that will set viriables accordingly and then stringing together those variables to be executed.

Please look at the links given.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources