combination of insert and select query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

insert into TempTable1STOCKDETAILS (PRODUCT_NO,PRODUCT_NAME,QTY_IN,UNIT_COST,FROM_DATE,TO_DATE)
(SELECT  a.PRODUCT_NO PRODUCT_NO, a.PRODUCT_NAME PRODUCT_NAME, SUM(A.QTY_IN) as QTY_IN,AVG(A.UNIT_COST) AS UNIT_COST
FROM STOCKDETAILS A  WHERE  a.WH_NO='WH1' AND a.STOCK_JR IN ('IN') AND a.STOCK_DATE BETWEEN
Convert(DATETIME,'29-09-2014',105) AND Convert(DATETIME,'30-09-2014',105)GROUP BY a.PRODUCT_NO , a.PRODUCT_NAME ) ,Convert(DATETIME,'29-09-2014',105),Convert(DATETIME,'30-09-2014',105)

here PRODUCT_NO,PRODUCT_NAME,QTY_IN,UNIT_COST get by using select query ,and
FROM_DATE,TO_DATE equals to Convert(DATETIME,'29-09-2014',105),Convert(DATETIME,'30-09-2014',105)

Posted 16-Oct-14 20:09pm

navya chowdary

Updated 16-Oct-14 20:12pm

v2

Add a Solution

Comments

Garth J Lancaster 17-Oct-14 2:14am

so, what's your question ?

navya chowdary 17-Oct-14 2:16am

it giving errors as
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 109, Level 15, State 1, Line 1
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

aarif moh shaikh 17-Oct-14 2:16am

What's your actual problem

King Fisher 17-Oct-14 2:16am

Error?

Jitendra Ku. Sahoo 17-Oct-14 2:17am

No one can replay as your question is not clear :(

navya chowdary 17-Oct-14 2:18am

i want this query execution,made some modifications

navya chowdary 17-Oct-14 2:25am

if any one now similar examples ,please post here

VC.J 17-Oct-14 2:42am

small suggestion why are you using Aliasing in select statement its not required as you are putting the data to other table

ChauhanAjay 17-Oct-14 3:00am

You are not having the values for from_date and to_date. Either remove from insert statement or add them in the select statement.

navya chowdary 17-Oct-14 3:02am

i want those values for printing purpose

[no name] 17-Oct-14 3:01am

1.) Bracket before SELECT is wrong
2.) Field lists of INSERT and SELECT do not match
3.) Alias in SELECT is not nessecary
4.) GROUP BY with non DB-fields makes no sense and is most probably not possible

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-10-16T23:42:00

Try this:

SQL

INSERT INTO TempTable1STOCKDETAILS (PRODUCT_NO,PRODUCT_NAME,QTY_IN,UNIT_COST,FROM_DATE,TO_DATE)
SELECT  a.PRODUCT_NO, a.PRODUCT_NAME, SUM(A.QTY_IN) AS QTY_IN, AVG(A.UNIT_COST) AS UNIT_COST, '29-09-2014' AS FROM_DATE, '30-09-2014' AS TO_DATE
FROM STOCKDETAILS A  WHERE  a.WH_NO='WH1' AND a.STOCK_JR = 'IN' AND a.STOCK_DATE BETWEEN '29-09-2014' AND '30-09-2014'
GROUP BY a.PRODUCT_NO, a.PRODUCT_NAME