Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Appending a Query Result into existing Table

Posted on 2010-11-20
14
Medium Priority
?
474 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

876 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