How I can copy ID from table A to table B

Question

This is SQL code:

Declare @A_ID  AS A_ID

insert into TBL_FO VALUES(NEWID(), @A_ID, '30000.00','1','1')
(select * from TBL_DETAIL where A_ID = '59366409-2EB6-49BC-A88F-801692B735D6')

I want to follow or copy the A_ID from TBL_DETAIL as '59366409-2EB6-49BC-A88F-801692B735D6' to TBL_FO for @A_ID.. How I can declare the A_ID for copy the same ID

Can you share please share the structures of `tbl_fo` and `tbl_detail`? — Mureinik, Jul 31 '16 at 07:06
Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Jul 31 '16 at 07:08
Possible duplicate of [How can I insert values into a table, using a subquery with more than one result?](http://stackoverflow.com/questions/9692319/how-can-i-insert-values-into-a-table-using-a-subquery-with-more-than-one-result) — AlexB, Jul 31 '16 at 07:39
Tip: You can use an [`OUTPUT`](https://msdn.microsoft.com/en-us/library/ms177564.aspx) clause to get any data from the rows (Note plural.), e.g. identity column values for newly inserted rows. `OUTPUT` can be used with `INSERT`, `UPDATE`, `DELETE` and `MERGE` and provides access to both _before_ and _after_ values in the case of `UPDATE`. A tool well worth having in your pocket. — HABO, Jul 31 '16 at 13:38

objectNotFound · Accepted Answer · 2016-07-31T08:21:03.887

1

Try this:

Declare @A_ID  as varchar(100) -- adjust the length as needed
select @A_ID = A_ID from TBL_DETAIL where A_ID = '59366409-2EB6-49BC-A88F-801692B735D6'

Declare @FO_ID as int -- To insert this into One or more tables
SET @FO_ID = NEWID() -- Initialize it one time.

insert into TBL_FO VALUES(@FO_ID, @A_ID, '30000.00','1','1') -- First use of @FO_ID

insert into TBL_SOMEOTHERTBL VALUES( @FO_ID, .... )  -- Second use of @FO_ID

etc ...

edited Jul 31 '16 at 08:21

answered Jul 31 '16 at 07:06

objectNotFound

1,683
2
18
25

I tried.. thank you for advice. If possible using `INNER JOIN` up to 4 table? – KH LIM Jul 31 '16 at 07:27
@KHLIM Iam not sure what you mean by that ... pls elaborate – objectNotFound Jul 31 '16 at 07:44
this is work from me.. `TBL_FO ` already generate a new ID for FO_ID right. However, can I grab this `FO_ID` for insert another table?? – KH LIM Jul 31 '16 at 08:07
may I grab the FO_ID in the same to insert another table value? – KH LIM Jul 31 '16 at 08:19
Noted and Thank You. – KH LIM Jul 31 '16 at 08:39
@KHLIM very vague need more information and I think this will be a new question. – objectNotFound Aug 01 '16 at 12:17

AlexB · Answer 2 · 2016-07-31T07:25:44.027

1

Specify the columns you want to set and then follow with the subquery having the values you need in addition to the A_ID(in the order of your column names) :

insert 
into 
   TBL_FO (FirstColumnName, 
           SecondColumnName, 
           ThirdColumnName, 
           FourthColumnName, 
           FifthColumnName) 
(select 
   NEWID(), 
   A_ID, 
   '30000.00', 
   '1', 
   '1' 
 from 
   TBL_DETAIL 
 where 
   A_ID = '59366409-2EB6-49BC-A88F-801692B735D6')

Note: there is also the option to not specify the column names if the table columns are in the same order as you insert them

edited Jul 31 '16 at 07:25

answered Jul 31 '16 at 07:20

AlexB

3,518
4
29
46

hello if I answered your question, can you accept my answer please ? – AlexB Jul 31 '16 at 07:31
I think yours is little bit different. I need to change insert into to select – KH LIM Jul 31 '16 at 07:42
It is the right way to do it. You don't need to declare a variable for a_id – AlexB Jul 31 '16 at 07:43
this is copy from one all value from table A to table B am I right? i want just only A_ID only. – KH LIM Jul 31 '16 at 07:59
no it inserts the right fields... it would copy all value if it was select * but this is not the case – AlexB Aug 01 '16 at 21:19

How I can copy ID from table A to table B

2 Answers2