Solved

Appending a Query Result into existing Table

Posted on 2010-11-20
14
461 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

809 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