SQL Query to insert values from one table into another

Question

I have a 2 table structure:

Table A:

RID; Name; LP
E    F     1
E    F     2
E    F     3
E    F     12
E    F     152

Table B:

LP
1
2
3
12
152
...
156
157
180
itd.

Query something like:

Insert into TableA(RID, Name, LP) Values ('E', 'F', Select LP from TableB)

I want to achive:

RID; Name; LP
E    F     1
E    F     2
E    F     3
E    F     12
E    F     152
E    F     156
E    F     157
E    F     180
etc.

Which database - sql-server or postgresql?

user359040
– user359040

2011-12-29 13:09:51 +00:00
Commented Dec 29, 2011 at 13:09 — user359040
– user359040, Commented Dec 29, 2011 at 13:09

Lukas Eder · Accepted Answer · 2011-12-29 13:09:36Z

10

You're close. Try

INSERT INTO TableA(RID, Name, LP)
SELECT 'E', 'F', LP FROM TableB
-- Omit this where clause, if duplicate LP's are OK
WHERE TableB.LP NOT IN (SELECT LP FROM TableA)

answered Dec 29, 2011 at 13:09

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Friso Over a year ago

Can you do this in JOOQ?

Lukas Eder Over a year ago

@Friso: Of course! Feel free to ask a new question about it and I'll answer

Friso Over a year ago

@Lucas Eder, after a lot of puzzling I figured it out. Might post something anyway if I find the time as it maybe helpful to others. Thank for the reply (and your great work!)

Lukas Eder Over a year ago

@Friso: Sure! It's common practice here on Stack Overflow to answer your own questions to help future visitors. Looking forward!

Florin Ghita · Accepted Answer · 2011-12-29 13:09:37Z

1

Insert into TableA(RID, Name, LP) 
Select 
   'E', 'F', LP 
 from 
   TableB

answered Dec 29, 2011 at 13:09

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Comments

J. Bend · Accepted Answer · 2011-12-29 13:11:07Z

0

The "quick and dirty" solution i came up with is :

Insert into TableA(RID, Name, LP) Values ('E', 'F', Select LP from TableB where rowid = 1)

Insert into TableA(RID, Name, LP) Values ('E', 'F', Select LP from TableB where rowid = 2)

...

and so on...

answered Dec 29, 2011 at 13:11

J. Bend

3092 gold badges3 silver badges15 bronze badges

1 Comment

Lukas Eder Over a year ago

That is really "quick and dirty" ;-)

Aki · Accepted Answer · 2014-06-21 11:43:56Z

0

select * into [NEW_TABLE] from [OLD_TABLE]

No need to create new table. It'll be created automatically.

Enjoy.

answered Jun 21, 2014 at 11:43

Aki

1794 silver badges13 bronze badges

Collectives™ on Stack Overflow

SQL Query to insert values from one table into another

Table A:

Table B:

4 Answers 4

4 Comments

Comments

1 Comment

No need to create new table. It'll be created automatically.

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table A:

Table B:

4 Answers 4

4 Comments

Comments

1 Comment

No need to create new table. It'll be created automatically.

Comments

Your Answer

Sign up or log in

Post as a guest

Related