SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Insert from multiple select (multiple table)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chulz90
Starting Member

Indonesia
26 Posts

Posted - 05/17/2013 :  04:49:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 05/17/2013 :  04:53:57  Show Profile  Reply with Quote
--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

Indonesia
26 Posts

Posted - 05/17/2013 :  05:05:25  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/17/2013 :  06:20:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 05/17/2013 :  06:22:57  Show Profile  Reply with Quote
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

Indonesia
26 Posts

Posted - 05/17/2013 :  07:59:18  Show Profile  Reply with Quote
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

Edited by - chulz90 on 05/17/2013 08:06:58
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/17/2013 :  08:05:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 05/17/2013 :  08:15:34  Show Profile  Reply with Quote
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

Indonesia
26 Posts

Posted - 05/17/2013 :  08:22:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 05/17/2013 :  08:27:57  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000