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
 Insert from multiple select (multiple table)

Author  Topic 

chulz90
Starting Member

26 Posts

Posted - 2013-05-17 : 04:49:51
hai guys,

i've some trouble to execute this query. i want to insert from two table
i'm using PL/SQL

can you help me to correct my query

insert into rspi_mutation
(MT_SEC,MT_ITEM_ID,MT_TAGNUM,MT_SERIAL_NO,MT_ITEM_STATUS,MT_STOREROOM,MT_DOC_ID,MT_DOC_STATUS,MT_STATUS_DATE,MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)
select max(mt_sec)+1 from rspi_mutation,
SELECT dn_itemnum,dn_tagnum,dn_serialnumber,eq_status,STORE_ROOM,dn_header_id,dn_status,dn_status_date,dn_from_locationid,dn_to_locationid,dn_qty
FROM V_INS_SCARD
where dn_header_id='46'

note:
-in rspi_mutation table contained data's
-mt_sec is a primary key in rspi_mutation table
-dn_header_id ='46' contain two data's

thank's for helping
sorry for my bad english

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 04:53:57
--Try this
insert into rspi_mutation
(MT_SEC,MT_ITEM_ID,MT_TAGNUM,
MT_SERIAL_NO,MT_ITEM_STATUS,
MT_STOREROOM,MT_DOC_ID,
MT_DOC_STATUS,MT_STATUS_DATE,
MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)
SELECT (select max(mt_sec)+1 from rspi_mutation),
dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qty
FROM V_INS_SCARD
where dn_header_id='46'


--
Chandu
Go to Top of Page

chulz90
Starting Member

26 Posts

Posted - 2013-05-17 : 05:05:25
quote:
Originally posted by bandi

--Try this
insert into rspi_mutation
(MT_SEC,MT_ITEM_ID,MT_TAGNUM,
MT_SERIAL_NO,MT_ITEM_STATUS,
MT_STOREROOM,MT_DOC_ID,
MT_DOC_STATUS,MT_STATUS_DATE,
MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)
SELECT (select max(mt_sec)+1 from rspi_mutation),
dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qty
FROM V_INS_SCARD
where dn_header_id='46'


--
Chandu


thank you so much bandi. u're always helping me.. but mt_sec result have a same value???
how to make it different
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 06:20:22
it should be this

insert into rspi_mutation
(MT_SEC,MT_ITEM_ID,MT_TAGNUM,
MT_SERIAL_NO,MT_ITEM_STATUS,
MT_STOREROOM,MT_DOC_ID,
MT_DOC_STATUS,MT_STATUS_DATE,
MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)
SELECT (select max(mt_sec) from rspi_mutation) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qty
FROM V_INS_SCARD
where dn_header_id='46'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 06:22:57
quote:
Originally posted by chulz90

quote:
Originally posted by bandi

--Try this
insert into rspi_mutation
(MT_SEC,MT_ITEM_ID,MT_TAGNUM,
MT_SERIAL_NO,MT_ITEM_STATUS,
MT_STOREROOM,MT_DOC_ID,
MT_DOC_STATUS,MT_STATUS_DATE,
MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)
SELECT (select max(mt_sec)+1 from rspi_mutation),
dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qty
FROM V_INS_SCARD
where dn_header_id='46'
--
Chandu


thank you so much bandi. u're always helping me.. but mt_sec result have a same value???
how to make it different


Welcome... Visakh already provided solution

--
Chandu
Go to Top of Page

chulz90
Starting Member

26 Posts

Posted - 2013-05-17 : 07:59:18
quote:
Originally posted by visakh16

it should be this

insert into rspi_mutation
(MT_SEC,MT_ITEM_ID,MT_TAGNUM,
MT_SERIAL_NO,MT_ITEM_STATUS,
MT_STOREROOM,MT_DOC_ID,
MT_DOC_STATUS,MT_STATUS_DATE,
MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)
SELECT (select max(mt_sec) from rspi_mutation) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qty
FROM V_INS_SCARD
where dn_header_id='46'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




it show an error message

ORA-00923: FROM keywordnot found where expected

what should i do ??
thanks before

FIXED

ROW_NUMBER() OVER (ORDER BY (select max(mt_sec) from rspi_mutation)desc

thanks visakh for helping
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 08:05:15
quote:
Originally posted by chulz90

quote:
Originally posted by visakh16

it should be this

insert into rspi_mutation
(MT_SEC,MT_ITEM_ID,MT_TAGNUM,
MT_SERIAL_NO,MT_ITEM_STATUS,
MT_STOREROOM,MT_DOC_ID,
MT_DOC_STATUS,MT_STATUS_DATE,
MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)
SELECT (select max(mt_sec) from rspi_mutation) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qty
FROM V_INS_SCARD
where dn_header_id='46'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




it show an error message

ORA-00923: FROM keywordnot found where expected

what should i do ??
thanks before


Post in a Oracle forum
This is MS SQL Server forum and we dont have much expertise on Oracle
For Oracle specific help try your luck @ Oracle forums like www.orafaq.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 08:15:34
I think you can use ROWNUM pseudo column in Oracle
--Check this
insert into rspi_mutation
(MT_SEC,MT_ITEM_ID,MT_TAGNUM,
MT_SERIAL_NO,MT_ITEM_STATUS,
MT_STOREROOM,MT_DOC_ID,
MT_DOC_STATUS,MT_STATUS_DATE,
MT_FROM_LOC_ID,MT_TO_LOC_ID,MT_SIGN_IN)
SELECT (select max(mt_sec) from rspi_mutation) + ROWNUM,
dn_itemnum, dn_tagnum, dn_serialnumber, eq_status, STORE_ROOM, dn_header_id, dn_status, dn_status_date, dn_from_locationid, dn_to_locationid, dn_qty
FROM V_INS_SCARD
where dn_header_id='46'

NOTE: You can get quick response by posting in relevant forums (dbforums.com for Oracle)

--
Chandu
Go to Top of Page

chulz90
Starting Member

26 Posts

Posted - 2013-05-17 : 08:22:21
sorry visakh, i don't know this is ms sql forum.. but it's fixed.
thanks for helping all
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-17 : 08:27:57
quote:
Originally posted by chulz90

sorry visakh, i don't know this is ms sql forum.. but it's fixed.
thanks for helping all


Welcome
I think you can do that by using pseudo column ROWNUM in Oracle...

--
Chandu
Go to Top of Page
   

- Advertisement -