SQL copy from one table to another

Question

I need to copy some field from one table to another.

first table is called phpbb_karma, second one is smf_log_karma

I need the following structure:

user_id -> id_executor
poster_id -> id_target
log_time -> karma_time
action -> karma_action

I ran the following query:

INSERT INTO
`smf_log_karma`
(`id_executor`, `id_target`, `log_time`, `action`)
SELECT
(`user_id`, `poster_id`, `karma_time`, `karma_action`)
AS
`id_executor`, `id_target`, `log_time`, `action`
FROM
`phpbb_karma`

and it returned

#1241 - Operand should contain 1 column(s)

Any help would be appreciated, thanks.

http://stackoverflow.com/questions/4101739/sql-server-select-into-existing-table — Maxim Zhukov, Jul 04 '13 at 08:38
I have another problem, please bare with me :) action in first table is defined with + and - and in second one it should be 1 and -1 (+=1, -=-1) — WebDeveloper, Jul 04 '13 at 09:15

kevinm · Accepted Answer · 2013-07-04T09:17:33.523

3

INSERT INTO
        `smf_log_karma`
        (`id_executor`, `id_target`, `log_time`, `action`)

   SELECT
        `user_id`, `poster_id`, `karma_time`, `karma_action`
        FROM
        `phpbb_karma` AS T1
 WHERE NOT EXISTS    (
                SELECT 1
                FROM `smf_log_karma` AS T2
                WHERE
               (T2.id_executor = T1.user_id) );

edited Jul 04 '13 at 09:17

answered Jul 04 '13 at 08:45

kevinm

475
2
7

I am trying all the solutions, will report in a minute – WebDeveloper Jul 04 '13 at 09:13
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 13 – WebDeveloper Jul 04 '13 at 09:17
#1062 - Duplicate entry '2-69' for key 'PRIMARY' duplicate entries seem to be a problem on every successful run – WebDeveloper Jul 04 '13 at 09:19
I have another problem, please bare with me :) action in first table is defined with + and - and in second one it should be 1 and -1 (+=1, -=-1) – WebDeveloper Jul 04 '13 at 09:21
okay...try this... SELECT 1 FROM `smf_log_karma` AS T2 WHERE (T1.user_id=T2.id_executor) ); – kevinm Jul 04 '13 at 09:27
I think that both id_target and id_executor are primary...I've made a screenshot so that you can check it yourself - http://img842.imageshack.us/img842/4236/0i29.jpg – WebDeveloper Jul 04 '13 at 09:34
I cant view the screenshot... There can be only one 1 primary field.. the others must be unique... anyways ... in that case add all those unique fields to your `NOT EXISTS` clause... – kevinm Jul 04 '13 at 09:38
SELECT 1 FROM smf_log_karma AS T2 WHERE (T1.user_id=T2.id_executor) OR (T1.poster_id=T2.id_target) ); – kevinm Jul 04 '13 at 09:40
its not ur problem.. i cants see it coz of the firewall on my network.. anyways.. did u try that code after adding all ur `UNIQUE` fields to the `WHERE` part of the `NOT EXISTS` clause? – kevinm Jul 04 '13 at 09:45
I did and it didn't work...it seems that I can't fill up karma log and all I can do is to export previous karma records into smf_members table. Thanks for your help. – WebDeveloper Jul 04 '13 at 09:49

score 1 · Answer 2 · answered Jul 04 '13 at 08:54

you need to Take out the (`) symbol from your both table

INSERT INTO smf_log_karma 
(id_executor, id_target, log_time, action)
SELECT user_id, poster_id, karma_time, karma_action
FROM
phpbb_karma

and make sure your table and column is same data type and exactly same and your id_executor cannot be duplicated if PK

zari · Answer 3 · 2013-07-04T08:44:30.413

0

Do you mean you want to copy some fields not all of them? If so, I think you forgot to use "values" in your syntax and it's the reason. You must use insert into .... (...) values select ....

edited Jul 04 '13 at 08:44

answered Jul 04 '13 at 08:39

zari

1,709
1
12
18

all right please add "values" to your syntax after close parenthesis and delete AS segment. – zari Jul 04 '13 at 08:43

score 0 · Answer 4 · answered Jul 04 '13 at 08:42

0

You just need the following to copy all rows:

INSERT INTO smf_log_karma 
(id_executor, id_target, log_time, action)
SELECT user_id, poster_id, karma_time, karma_action
FROM
phpbb_karma

answered Jul 04 '13 at 08:42

Craig

11
1

I get #1062 - Duplicate entry '2-69' for key 'PRIMARY' which should be normal as this is karma mod and same user got multiple karma. How can I avoid errors for duplicate entries? – WebDeveloper Jul 04 '13 at 08:46
Add a WHERE clause to avoid inserting duplicates: WHERE user_id NOT IN (SELECT id_executor FROM smf_log_karma) – Craig Jul 04 '13 at 08:49
not quite, same error but different entries - #1062 - Duplicate entry '483-1525' for key 'PRIMARY' – WebDeveloper Jul 04 '13 at 09:13
Is id_target your PRIMARY KEY? Try the WHERE clause with: WHERE user_id NOT IN (SELECT id_executor FROM smf_log_karma) AND poster_id NOT IN (SELECT id_target FROM smf_log_karma) – Craig Jul 04 '13 at 09:28
I think that both id_target and id_executor are primary...I've made a screenshot so that you can check it yourself - http://img842.imageshack.us/img842/4236/0i29.jpg – WebDeveloper Jul 04 '13 at 09:33
Great thanks, use the following WHERE clause: WHERE NOT EXISTS(SELECT * FROM smf_log_karma WHERE id_executor=user_id AND id_target=poster_id) – Craig Jul 04 '13 at 09:43

score 0 · Answer 5 · answered Jul 04 '13 at 08:45

0

You need this though the column naming is not necessary in the select but is helpful

enter code here
 INSERT INTO smf_log_karma
     (id_executor,
      id_target,
      log_time,
      action)
 SELECT
      user_id as id_executor,
      poster_id as id_target,
      karma_time as log_time,
      karma_action as action
 FROM phpbb_karma

You can see it in action here

answered Jul 04 '13 at 08:45

Java Devil

10,629
7
33
48

I get #1062 - Duplicate entry '2-69' for key 'PRIMARY' which should be normal as this is karma mod and same user got multiple karma. How can I avoid errors for duplicate entries? – WebDeveloper Jul 04 '13 at 08:48

score 0 · Answer 6 · answered Jul 04 '13 at 08:51

0

INSERT INTO `smf_log_karma` (`id_executor`, `id_target`, `log_time`, `action`)   
SELECT `user_id` AS `id_executor`, `poster_id` AS `id_target`, `karma_time` AS `log_time`, `karma_action` AS `action`
FROM `phpbb_karma`

answered Jul 04 '13 at 08:51

mzy

1,754
2
20
36

I get #1062 - Duplicate entry '2-69' for key 'PRIMARY' which should be normal as this is karma mod and same user got multiple karma. How can I avoid errors for duplicate entries? – WebDeveloper Jul 04 '13 at 09:18

score 0 · Answer 7 · answered Jul 04 '13 at 10:14

I am a team member of Data Migration.

So try this Query and let me know if it worked.

INSERT INTO `smf_log_karma`
(
     `id_executor`,
     `id_target`, 
     `log_time`, 
     `action`
)
SELECT
     `user_id`, 
     `poster_id`, 
     `karma_time`, 
     `karma_action`
FROM
`phpbb_karma` AS T1
where T1.user_id
not in (select `id_executor` from `smf_log_karma`)

SQL copy from one table to another

7 Answers7