How to capture result set - 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

How to capture result set

Author

Topic

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-06-07 : 14:53:41

Hi how do I capture a resultset from an existing query and put it in a different table? Can u give me a description?

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-06-07 : 15:05:45

It's a Table that has multiple CTEs

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-07 : 15:12:32

You can create the new table and insert into it.
INSERT INTO newlyCreatedTable
<query>
or change the query to
SELECT <columns>
INTO yourNewTable -- you can do this once, after that the table is already there, so you'd use the above
FROM etc.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-07 : 22:37:52

quote:
Originally posted by Joshrinn

It's a Table that has multiple CTEs

you can use SELECT ... INTO TableName...FROM CTE...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-06-08 : 07:40:40

Thanks guys

Joshrinn
Posting Yak Master

118 Posts

Posted - 2012-06-08 : 07:51:30

Btw I have an identical table created for one of the table that was used in the CTE. There were 5 columns in that old table with one of the columns being LastBalance. I added an extra column in my new table and named it LastBalance1. I then inserted the values from my old table into the new table but the extra column on my new table, I put that as blank. The purpose of this whole thing was that the LastBalance in the old table has value of 123456 for sept month, I want that value in my new column lastbalance1 for Oct. so the sept value in LastBalance column should be the oct value in LastBalance 1, oct value in LastBalance column should be Nov balance in Lastbalance1 so on and so forth.
So I used

UPDATE Tbl_new
SET lastbalance1=123456
WHERE Process_date='10/31/2011'. But there are so many accounts and I am hard coding these values by hand. My question is can I update this new column this way without hard coding every number?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-08 : 11:13:51

I don't see any Account in your query so I'm not clear on what you are trying to do. Please post DDL. DML and expected results. Here are some links that can help you prepare that information if you are not familar with how to provide it:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-08 : 11:49:12

quote:
Originally posted by Joshrinn

Btw I have an identical table created for one of the table that was used in the CTE. There were 5 columns in that old table with one of the columns being LastBalance. I added an extra column in my new table and named it LastBalance1. I then inserted the values from my old table into the new table but the extra column on my new table, I put that as blank. The purpose of this whole thing was that the LastBalance in the old table has value of 123456 for sept month, I want that value in my new column lastbalance1 for Oct. so the sept value in LastBalance column should be the oct value in LastBalance 1, oct value in LastBalance column should be Nov balance in Lastbalance1 so on and so forth.
So I used

UPDATE Tbl_new
SET lastbalance1=123456
WHERE Process_date='10/31/2011'. But there are so many accounts and I am hard coding these values by hand. My question is can I update this new column this way without hard coding every number?

As i understand you're trying to get previous month value into next months lastbalance1 field. in that case, name field as PrevMonthBalance and use logic like below


UPDATE n
      SET n.PrevMonthBalance=t.CurrentBalance
FROM Tbl_new n
INNER JOIN tableOld t
ON t.Process_date = DATEADD(mm,-1,n.Process_date )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources