Solved

Appending a Query Result into existing Table

Posted on 2010-11-20
14
459 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
  • 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
 
LVL 40

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 40

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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 40

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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now