Select inside an insert does not work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select inside an insert does not work

Author

Topic

jadi
Starting Member

20 Posts

Posted - 2009-01-28 : 10:03:54

Let me explain in detail
I have 5 tables in same db from which I have collected the required data via a select xx from xjoiny join z....where abc.

It is a 15 line query.
Now I want to put the values of this query into a table in the same db.

I tried the insert into xyz(a,b) Select....(my whole above select query)

Can someone help me? How will I go about doing something like this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 10:08:31

the syntax is right. what was error you get? it should be


insert table (col1,col2,...coln)
SELECT col1,col2,...coln
FROM x 
join y
on..
join z
on...

jadi
Starting Member

20 Posts

Posted - 2009-01-28 : 10:10:11

INSERT INTO dbo.VOL_RENTAL_DATAGATHER_QUERY(dbo.DESPATCH_NOTE,dbo.DESPATCH_LINE,dbo.ORDER_NUMBER,dbo.ORDER_LINE_NUMBER,dbo.PART_CODE,dbo.IC_LOT_NUMBER,dbo.RENTAL_PART,dbo.RENTAL_PRICE,dbo.CYLNUM,dbo.[SHIPPING DATE],dbo.INVOICE_NUMBER,dbo.CONFIRM_DESPATCH_Y_OR_N,dbo.CUSTOMER_NUMBER,dbo.DELIVERY_ADDRESS_CODE,dbo.CUSTOMER_REFERENCE)
SELECT dbo.DESPATCH_NOTES.DESPATCH_NOTE, dbo.DESPATCH_NOTES.DESPATCH_LINE, dbo.DESPATCH_NOTES.ORDER_NUMBER,
dbo.DESPATCH_NOTES.ORDER_LINE_NUMBER, dbo.DESPATCH_NOTES.PART_CODE, dbo.DESPATCH_NOTES.IC_LOT_NUMBER,
SALES_ORDER_LINES_1.PART_CODE AS RENTAL_PART, SALES_ORDER_LINES_1.SALES_PRICE AS RENTAL_PRICE,
dbo.VOL_CYLMOVEMENTS.IC_LOT_NUMBER AS CYLNUM, dbo.VOL_CYLMOVEMENTS.MOVEMENT_DATE AS [SHIPPING DATE],
dbo.SALES_ORDER_INVOICE_LINES.INVOICE_NUMBER, dbo.DESPATCH_NOTES.CONFIRM_DESPATCH,
dbo.SALES_ORDER_INVOICE_LINES.CUSTOMER_NUMBER, dbo.SALES_ORDER_INVOICE_LINES.DELIVERY_ADDRESS_CODE,
dbo.SALES_ORDER_HEADERS.CUSTOMER_REFERENCE
FROM dbo.DESPATCH_NOTES INNER JOIN
dbo.SALES_ORDER_LINES ON dbo.DESPATCH_NOTES.ORDER_NUMBER = dbo.SALES_ORDER_LINES.ORDER_NUMBER AND
dbo.DESPATCH_NOTES.ORDER_LINE_NUMBER = dbo.SALES_ORDER_LINES.ORDER_LINE_NUMBER LEFT OUTER JOIN
dbo.SALES_ORDER_HEADERS ON dbo.SALES_ORDER_LINES.ORDER_NUMBER = dbo.SALES_ORDER_HEADERS.ORDER_NUMBER LEFT OUTER JOIN
dbo.VOL_CYLMOVEMENTS ON dbo.DESPATCH_NOTES.DESPATCH_NOTE = dbo.VOL_CYLMOVEMENTS.DOCUMENT_NUMBER AND
dbo.DESPATCH_NOTES.DESPATCH_LINE = dbo.VOL_CYLMOVEMENTS.DOCUMENT_LINE LEFT OUTER JOIN
dbo.SALES_ORDER_INVOICE_LINES ON dbo.SALES_ORDER_LINES.ORDER_NUMBER = dbo.SALES_ORDER_INVOICE_LINES.ORDER_NUMBER AND
dbo.SALES_ORDER_LINES.ORDER_LINE_NUMBER = dbo.SALES_ORDER_INVOICE_LINES.ORDER_LINE_NUMBER FULL OUTER JOIN
dbo.SALES_ORDER_LINES AS SALES_ORDER_LINES_1 ON
dbo.SALES_ORDER_LINES.ORDER_LINE_NUMBER = SALES_ORDER_LINES_1.ORDER_LINE_REFERENCE AND
dbo.SALES_ORDER_LINES.ORDER_NUMBER = SALES_ORDER_LINES_1.ORDER_NUMBER
GROUP BY dbo.DESPATCH_NOTES.DESPATCH_NOTE, dbo.DESPATCH_NOTES.DESPATCH_LINE, dbo.DESPATCH_NOTES.ORDER_NUMBER,
dbo.DESPATCH_NOTES.ORDER_LINE_NUMBER, dbo.DESPATCH_NOTES.PART_CODE, dbo.DESPATCH_NOTES.IC_LOT_NUMBER,
SALES_ORDER_LINES_1.PART_CODE, SALES_ORDER_LINES_1.SALES_PRICE, dbo.VOL_CYLMOVEMENTS.IC_LOT_NUMBER,
SALES_ORDER_LINES_1.ORDER_LINE_NUMBER, SALES_ORDER_LINES_1.ORDER_LINE_REFERENCE,
dbo.VOL_CYLMOVEMENTS.MOVEMENT_CODE, dbo.VOL_CYLMOVEMENTS.MOVEMENT_DATE,
dbo.SALES_ORDER_INVOICE_LINES.INVOICE_NUMBER, dbo.DESPATCH_NOTES.CONFIRM_DESPATCH,
dbo.SALES_ORDER_INVOICE_LINES.CUSTOMER_NUMBER, dbo.SALES_ORDER_INVOICE_LINES.DELIVERY_ADDRESS_CODE,
dbo.SALES_ORDER_HEADERS.CUSTOMER_REFERENCE
HAVING (dbo.VOL_CYLMOVEMENTS.MOVEMENT_CODE = 'CYLSHP') AND (SALES_ORDER_LINES_1.PART_CODE >= '901000') AND
(SALES_ORDER_LINES_1.PART_CODE <= '902999') AND (dbo.DESPATCH_NOTES.CONFIRM_DESPATCH = 'Y')
ORDER BY [SHIPPING DATE] DESC;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 10:14:19

try something like


INSERT INTO dbo.VOL_RENTAL_DATAGATHER_QUERY
(DESPATCH_NOTE,
DESPATCH_LINE,
ORDER_NUMBER,
ORDER_LINE_NUMBER,
PART_CODE,
IC_LOT_NUMBER,
RENTAL_PART,
RENTAL_PRICE,
CYLNUM,
[SHIPPING DATE],
INVOICE_NUMBER,
CONFIRM_DESPATCH_Y_OR_N,
CUSTOMER_NUMBER,
DELIVERY_ADDRESS_CODE,
CUSTOMER_REFERENCE)
SELECT dbo.DESPATCH_NOTES.DESPATCH_NOTE, dbo.DESPATCH_NOTES.DESPATCH_LINE, dbo.DESPATCH_NOTES.ORDER_NUMBER, 
dbo.DESPATCH_NOTES.ORDER_LINE_NUMBER, dbo.DESPATCH_NOTES.PART_CODE, dbo.DESPATCH_NOTES.IC_LOT_NUMBER, 
SALES_ORDER_LINES_1.PART_CODE AS RENTAL_PART, SALES_ORDER_LINES_1.SALES_PRICE AS RENTAL_PRICE, 
dbo.VOL_CYLMOVEMENTS.IC_LOT_NUMBER AS CYLNUM, dbo.VOL_CYLMOVEMENTS.MOVEMENT_DATE AS [SHIPPING DATE], 
dbo.SALES_ORDER_INVOICE_LINES.INVOICE_NUMBER, dbo.DESPATCH_NOTES.CONFIRM_DESPATCH, 
dbo.SALES_ORDER_INVOICE_LINES.CUSTOMER_NUMBER, dbo.SALES_ORDER_INVOICE_LINES.DELIVERY_ADDRESS_CODE, 
dbo.SALES_ORDER_HEADERS.CUSTOMER_REFERENCE
FROM dbo.DESPATCH_NOTES INNER JOIN
dbo.SALES_ORDER_LINES ON dbo.DESPATCH_NOTES.ORDER_NUMBER = dbo.SALES_ORDER_LINES.ORDER_NUMBER AND 
dbo.DESPATCH_NOTES.ORDER_LINE_NUMBER = dbo.SALES_ORDER_LINES.ORDER_LINE_NUMBER LEFT OUTER JOIN
dbo.SALES_ORDER_HEADERS ON dbo.SALES_ORDER_LINES.ORDER_NUMBER = dbo.SALES_ORDER_HEADERS.ORDER_NUMBER LEFT OUTER JOIN
dbo.VOL_CYLMOVEMENTS ON dbo.DESPATCH_NOTES.DESPATCH_NOTE = dbo.VOL_CYLMOVEMENTS.DOCUMENT_NUMBER AND 
dbo.DESPATCH_NOTES.DESPATCH_LINE = dbo.VOL_CYLMOVEMENTS.DOCUMENT_LINE LEFT OUTER JOIN
dbo.SALES_ORDER_INVOICE_LINES ON dbo.SALES_ORDER_LINES.ORDER_NUMBER = dbo.SALES_ORDER_INVOICE_LINES.ORDER_NUMBER AND 
dbo.SALES_ORDER_LINES.ORDER_LINE_NUMBER = dbo.SALES_ORDER_INVOICE_LINES.ORDER_LINE_NUMBER FULL OUTER JOIN
dbo.SALES_ORDER_LINES AS SALES_ORDER_LINES_1 ON 
dbo.SALES_ORDER_LINES.ORDER_LINE_NUMBER = SALES_ORDER_LINES_1.ORDER_LINE_REFERENCE AND 
dbo.SALES_ORDER_LINES.ORDER_NUMBER = SALES_ORDER_LINES_1.ORDER_NUMBER
GROUP BY dbo.DESPATCH_NOTES.DESPATCH_NOTE, dbo.DESPATCH_NOTES.DESPATCH_LINE, dbo.DESPATCH_NOTES.ORDER_NUMBER, 
dbo.DESPATCH_NOTES.ORDER_LINE_NUMBER, dbo.DESPATCH_NOTES.PART_CODE, dbo.DESPATCH_NOTES.IC_LOT_NUMBER, 
SALES_ORDER_LINES_1.PART_CODE, SALES_ORDER_LINES_1.SALES_PRICE, dbo.VOL_CYLMOVEMENTS.IC_LOT_NUMBER, 
SALES_ORDER_LINES_1.ORDER_LINE_NUMBER, SALES_ORDER_LINES_1.ORDER_LINE_REFERENCE, 
dbo.VOL_CYLMOVEMENTS.MOVEMENT_CODE, dbo.VOL_CYLMOVEMENTS.MOVEMENT_DATE, 
dbo.SALES_ORDER_INVOICE_LINES.INVOICE_NUMBER, dbo.DESPATCH_NOTES.CONFIRM_DESPATCH, 
dbo.SALES_ORDER_INVOICE_LINES.CUSTOMER_NUMBER, dbo.SALES_ORDER_INVOICE_LINES.DELIVERY_ADDRESS_CODE, 
dbo.SALES_ORDER_HEADERS.CUSTOMER_REFERENCE
HAVING (dbo.VOL_CYLMOVEMENTS.MOVEMENT_CODE = 'CYLSHP') AND (SALES_ORDER_LINES_1.PART_CODE >= '901000') AND 
(SALES_ORDER_LINES_1.PART_CODE <= '902999') AND (dbo.DESPATCH_NOTES.CONFIRM_DESPATCH = 'Y')
ORDER BY [SHIPPING DATE] DESC;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 10:15:38

And change the HAVING clause to a WHERE clause.

E 12°55'05.63"
N 56°04'39.26"

jadi
Starting Member

20 Posts

Posted - 2009-01-28 : 10:16:29

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.VOL_RENTAL_DATAGATHER_QUERY'.

jadi
Starting Member

20 Posts

Posted - 2009-01-28 : 10:17:52

INSERT INTO dbo.VOL_RENTAL_DATAGATHER_QUERY
(DESPATCH_NOTE,
DESPATCH_LINE,
ORDER_NUMBER,
ORDER_LINE_NUMBER,
PART_CODE,
IC_LOT_NUMBER,
RENTAL_PART,
RENTAL_PRICE,
CYLNUM,
[SHIPPING DATE],
INVOICE_NUMBER,
CONFIRM_DESPATCH_Y_OR_N,
CUSTOMER_NUMBER,
DELIVERY_ADDRESS_CODE,
CUSTOMER_REFERENCE)
SELECT dbo.DESPATCH_NOTES.DESPATCH_NOTE, dbo.DESPATCH_NOTES.DESPATCH_LINE, dbo.DESPATCH_NOTES.ORDER_NUMBER,
dbo.DESPATCH_NOTES.ORDER_LINE_NUMBER, dbo.DESPATCH_NOTES.PART_CODE, dbo.DESPATCH_NOTES.IC_LOT_NUMBER,
SALES_ORDER_LINES_1.PART_CODE AS RENTAL_PART, SALES_ORDER_LINES_1.SALES_PRICE AS RENTAL_PRICE,
dbo.VOL_CYLMOVEMENTS.IC_LOT_NUMBER AS CYLNUM, dbo.VOL_CYLMOVEMENTS.MOVEMENT_DATE AS [SHIPPING DATE],
dbo.SALES_ORDER_INVOICE_LINES.INVOICE_NUMBER, dbo.DESPATCH_NOTES.CONFIRM_DESPATCH,
dbo.SALES_ORDER_INVOICE_LINES.CUSTOMER_NUMBER, dbo.SALES_ORDER_INVOICE_LINES.DELIVERY_ADDRESS_CODE,
dbo.SALES_ORDER_HEADERS.CUSTOMER_REFERENCE
FROM dbo.DESPATCH_NOTES INNER JOIN
dbo.SALES_ORDER_LINES ON dbo.DESPATCH_NOTES.ORDER_NUMBER = dbo.SALES_ORDER_LINES.ORDER_NUMBER AND
dbo.DESPATCH_NOTES.ORDER_LINE_NUMBER = dbo.SALES_ORDER_LINES.ORDER_LINE_NUMBER LEFT OUTER JOIN
dbo.SALES_ORDER_HEADERS ON dbo.SALES_ORDER_LINES.ORDER_NUMBER = dbo.SALES_ORDER_HEADERS.ORDER_NUMBER LEFT OUTER JOIN
dbo.VOL_CYLMOVEMENTS ON dbo.DESPATCH_NOTES.DESPATCH_NOTE = dbo.VOL_CYLMOVEMENTS.DOCUMENT_NUMBER AND
dbo.DESPATCH_NOTES.DESPATCH_LINE = dbo.VOL_CYLMOVEMENTS.DOCUMENT_LINE LEFT OUTER JOIN
dbo.SALES_ORDER_INVOICE_LINES ON dbo.SALES_ORDER_LINES.ORDER_NUMBER = dbo.SALES_ORDER_INVOICE_LINES.ORDER_NUMBER AND
dbo.SALES_ORDER_LINES.ORDER_LINE_NUMBER = dbo.SALES_ORDER_INVOICE_LINES.ORDER_LINE_NUMBER FULL OUTER JOIN
dbo.SALES_ORDER_LINES AS SALES_ORDER_LINES_1 ON
dbo.SALES_ORDER_LINES.ORDER_LINE_NUMBER = SALES_ORDER_LINES_1.ORDER_LINE_REFERENCE AND
dbo.SALES_ORDER_LINES.ORDER_NUMBER = SALES_ORDER_LINES_1.ORDER_NUMBER
GROUP BY dbo.DESPATCH_NOTES.DESPATCH_NOTE, dbo.DESPATCH_NOTES.DESPATCH_LINE, dbo.DESPATCH_NOTES.ORDER_NUMBER,
dbo.DESPATCH_NOTES.ORDER_LINE_NUMBER, dbo.DESPATCH_NOTES.PART_CODE, dbo.DESPATCH_NOTES.IC_LOT_NUMBER,
SALES_ORDER_LINES_1.PART_CODE, SALES_ORDER_LINES_1.SALES_PRICE, dbo.VOL_CYLMOVEMENTS.IC_LOT_NUMBER,
SALES_ORDER_LINES_1.ORDER_LINE_NUMBER, SALES_ORDER_LINES_1.ORDER_LINE_REFERENCE,
dbo.VOL_CYLMOVEMENTS.MOVEMENT_CODE, dbo.VOL_CYLMOVEMENTS.MOVEMENT_DATE,
dbo.SALES_ORDER_INVOICE_LINES.INVOICE_NUMBER, dbo.DESPATCH_NOTES.CONFIRM_DESPATCH,
dbo.SALES_ORDER_INVOICE_LINES.CUSTOMER_NUMBER, dbo.SALES_ORDER_INVOICE_LINES.DELIVERY_ADDRESS_CODE,
dbo.SALES_ORDER_HEADERS.CUSTOMER_REFERENCE
WHERE
dbo.VOL_CYLMOVEMENTS.MOVEMENT_CODE = 'CYLSHP' AND
SALES_ORDER_LINES_1.PART_CODE >= '901000' AND
SALES_ORDER_LINES_1.PART_CODE <= '902999' AND dbo.DESPATCH_NOTES.CONFIRM_DESPATCH = 'Y'

jadi
Starting Member

20 Posts

Posted - 2009-01-28 : 10:19:32

Msg 156, Level 15, State 1, Line 43
Incorrect syntax near the keyword 'WHERE'.

jadi
Starting Member

20 Posts

Posted - 2009-01-28 : 10:24:56

i REMOVED THE wHERE and put the HAVING again and I got all this!

Msg 207, Level 16, State 1, Line 2
Invalid column name 'DESPATCH_NOTE'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'DESPATCH_LINE'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ORDER_NUMBER'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ORDER_LINE_NUMBER'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'PART_CODE'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'IC_LOT_NUMBER'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'RENTAL_PART'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'RENTAL_PRICE'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'CYLNUM'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'SHIPPING DATE'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'INVOICE_NUMBER'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'CONFIRM_DESPATCH_Y_OR_N'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'CUSTOMER_NUMBER'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'DELIVERY_ADDRESS_CODE'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'CUSTOMER_REFERENCE'.
Msg 215, Level 16, State 1, Line 16
Parameters supplied for object 'dbo.VOL_RENTAL_DATAGATHER_TABLE' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 10:27:10

[code]INSERT dbo.VOL_RENTAL_DATAGATHER_QUERY
(
DESPATCH_NOTE,
DESPATCH_LINE,
ORDER_NUMBER,
ORDER_LINE_NUMBER,
PART_CODE,
IC_LOT_NUMBER,
RENTAL_PART,
RENTAL_PRICE,
CYLNUM,
[SHIPPING DATE],
INVOICE_NUMBER,
CONFIRM_DESPATCH_Y_OR_N,
CUSTOMER_NUMBER,
DELIVERY_ADDRESS_CODE,
CUSTOMER_REFERENCE
)
SELECT dn.DESPATCH_NOTE,
dn.DESPATCH_LINE,
dn.ORDER_NUMBER,
dn.ORDER_LINE_NUMBER,
dn.PART_CODE,
dn.IC_LOT_NUMBER,
ol.PART_CODE AS RENTAL_PART,
ol.SALES_PRICE AS RENTAL_PRICE,
vcm.IC_LOT_NUMBER AS CYLNUM,
vcm.MOVEMENT_DATE AS [SHIPPING DATE],
oil.INVOICE_NUMBER,
dn.CONFIRM_DESPATCH,
oil.CUSTOMER_NUMBER,
oil.DELIVERY_ADDRESS_CODE,
soh.CUSTOMER_REFERENCE
FROM dbo.DESPATCH_NOTES AS dn
INNER JOIN dbo.SALES_ORDER_LINES AS sol ON sol.ORDER_NUMBER = dn.ORDER_NUMBER
AND sol.ORDER_LINE_NUMBER = dn.ORDER_LINE_NUMBER
LEFT JOIN dbo.SALES_ORDER_HEADERS AS soh ON soh.ORDER_NUMBER = sol.ORDER_NUMBER
LEFT JOIN dbo.VOL_CYLMOVEMENTS AS vcm ON vcm.DOCUMENT_NUMBER = dn.DESPATCH_NOTE
AND vcm.DOCUMENT_LINE = dn.DESPATCH_LINE
AND vcm.MOVEMENT_CODE = 'CYLSHP'
LEFT JOIN dbo.SALES_ORDER_INVOICE_LINES AS oil ON oil.ORDER_NUMBER = sol.ORDER_NUMBER
AND oil.ORDER_LINE_NUMBER = sol.ORDER_LINE_NUMBER
AND ol.PART_CODE >= '901000'
AND ol.PART_CODE <= '902999'
FULL JOIN dbo.SALES_ORDER_LINES AS ol ON ol.ORDER_LINE_REFERENCE = sol.ORDER_LINE_NUMBER
AND ol.ORDER_NUMBER = sol.ORDER_NUMBER
WHERE dn.CONFIRM_DESPATCH = 'Y'
GROUP BY dn.DESPATCH_NOTE,
dn.DESPATCH_LINE,
dn.ORDER_NUMBER,
dn.ORDER_LINE_NUMBER,
dn.PART_CODE,
dn.IC_LOT_NUMBER,
ol.PART_CODE,
ol.SALES_PRICE,
vcm.IC_LOT_NUMBER,
ol.ORDER_LINE_NUMBER,
ol.ORDER_LINE_REFERENCE,
vcm.MOVEMENT_CODE,
vcm.MOVEMENT_DATE,
oil.INVOICE_NUMBER,
dn.CONFIRM_DESPATCH,
oil.CUSTOMER_NUMBER,
oil.DELIVERY_ADDRESS_CODE,
soh.CUSTOMER_REFERENCE
ORDER BY vcm.MOVEMENT_DATE DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

jadi
Starting Member

20 Posts

Posted - 2009-01-28 : 10:27:26

I got it
u al are the best
sorry i will have to take the query off.

jadi
Starting Member

20 Posts

Posted - 2009-01-28 : 10:28:24

quote:
Originally posted by jadi

I got it
u al are the best
sorry i will have to take the query off.

May be not...........someone might use it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 12:30:51

quote:
Originally posted by jadi

quote:
Originally posted by jadi

I got it
u al are the best
sorry i will have to take the query off.

May be not...........someone might use it

thats appreciated

please dont delete the answers once you get solution. somebody else might benefit from each of these threads

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources