Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Appending a Query Result into existing Table

Posted on 2010-11-20
14
Medium Priority
?
470 Views
Last Modified: 2012-06-21
I'd like to know how to place the resultset of a query into an existing table in SQL Server.
0
Comment
Question by:brohjoe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34180807
use this syntax:
insert into destinationtable(field1,field2,...)
select field1,field2,...
from ...
where ...
0
 

Author Comment

by:brohjoe
ID: 34180827
Ok, the result column is 'Col_Result.'  So my syntax is,

"Insert into desinationTable(Col_Result)
Select Col_Result
From ???

I don't understand the select statement.  'Col_Result' is in SQL Server's 'Result' window.  How do I address that with the select statement?  Or do you mean something else?  Please clarify.

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34180835
let me know what is your select statement exactly? and what is your destination table structure?
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 41

Expert Comment

by:Sharath
ID: 34180846
Do you have non-NULL columns in your destinationTable? If yes, you need to pass a non-NULL value to those columns in the SELECT clasue and you need to include those columns in the columns list in the INSERT statement.

Insert into desinationTable(Col_Result)
Select Col_Result
From Source_Table
0
 

Author Comment

by:brohjoe
ID: 34180891
How do you reference the source table when it is a query result?   I guess what I'm trying to create is a derived column that is added to an existing table.  

The exact query is listed here.  The query resultset is the result of a combination of other columns in the queried table.  I want to add the results of the query into the table as a new table.
USE [master]

GO

Select Case 
	When  
		Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty <= 0 Then 0

	Else
		Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty

	End As 
		ThisWeek1

	From
		Demand

Open in new window

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34180905
INSERT INTO DESTINATION TABLE( Col_Result )
Select Case
      When  
            Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty <= 0 Then 0

      Else
            Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty

      End As
            ThisWeek1

      From
            Demand
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34180906
try like this
USE [master]

GO

Select Case 
	When  
		Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty <= 0 Then 0

	Else
		Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty

	End As 
		ThisWeek1
    Into DestinationTable
	From
		Demand

select * from DestinationTable

Open in new window

0
 

Author Comment

by:brohjoe
ID: 34180910
Correction, I want to add the query resultset into the existing table, not a new table.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34180912
previous sql statement , will insert into an existing table,
if you want to put them in a NEW TABLE , do this:
Select Case
      When  
            Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty <= 0 Then 0

      Else
            Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty

      End As
            ThisWeek1
INTO NewTable
      From
            Demand
0
 

Author Comment

by:brohjoe
ID: 34180931
This code only provides a resultset in the bottom window of SQL Server.  It does not add the column to the 'Demand' table:

Select Case
      When  
            Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty <= 0 Then 0

      Else
            Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty

      End As
            ThisWeek1

      From
            Demand

If I replace, 'From Demand' with 'INTO Demand', I get invalid column errors.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34180936
Do you want to add new column to the existing Demand table or do you want to insert the result of your query to an existing table?
0
 

Author Comment

by:brohjoe
ID: 34180947
Add a new column (ThisWeek1) into the existing Demand Table.
0
 
LVL 30

Accepted Solution

by:
Reza Rad earned 2000 total points
ID: 34180968
you want to add a computed column?
if yes, do this:

ALTER TABLE Demand ADD ThisWeek1 AS (Case
      When  
            Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty <= 0 Then 0

      Else
            Bklog_Qty + Week_1 + midpoint - Avail_Inv_Qty

      End)
0
 

Author Closing Comment

by:brohjoe
ID: 34180978
Thanks, that did the trick.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question