how to store query and result on the same table

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)

how to store query and result on the same table

Author

Topic

Dave_007
Starting Member

15 Posts

Posted - 2008-06-13 : 13:05:09

One column in my table stores SQL queries(QueryCoulmn). Another coulmn supposed to store the result of those queries(ResultColumn). Can I run an update query or how can I do that? I could not figure out the syntax.

update tablename
set ResultColumn=exec(QueryCoulmn)

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 13:12:33

You cant use exec like this in update statement.
b/w what will be type of your result column? also will resultset structure be same always?

Dave_007
Starting Member

15 Posts

Posted - 2008-06-13 : 13:21:17

result column is int. Yes, result structure would be same always.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 13:32:26

quote:
Originally posted by Dave_007

result column is int. Yes, result structure would be same always.

will it be a single integer value always?

Dave_007
Starting Member

15 Posts

Posted - 2008-06-13 : 13:33:28

yes, it will be single value always

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 13:42:53

quote:
Originally posted by Dave_007

yes, it will be single value always

then you can do it like this

DECLARE @temp TABLE
(
Val int 
)
DECLARE @ID int,@Query varchar(8000)

SELECT @ID=MIN(PK)
FROM YourTable

WHILE @ID IS NOT NULL
BEGIN
SELECT @Query=QueryCoulmn
FROM YourTable
WHERE PK=@ID

INSERT INTO @temp
EXEC (@Query)

UPDATE YourTable
SET ResultColumn=(SELECT Val FROM @temp)
WHERE QueryCoulmn=@Query

DELETE FROM @temp

SELECT @ID=MIN(PK)
FROM YourTable
WHERE PK >@ID
END

Dave_007
Starting Member

15 Posts

Posted - 2008-06-13 : 13:47:20

i will try that, thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources