Insert values of one table in a database to another table in another database

Question

I would like to take some data from a table from DB1 and insert some of that data to a table in DB2.

How would one proceed to do this?

This is what I've got so far:

CREATE VIEW old_study AS
SELECT *
FROM dblink('dbname=mydb', 'select name,begins,ends from study')
AS t1(name varchar(50), register_start date, register_end date);
/*old_study now contains the data I wanna transfer*/

INSERT INTO studies VALUES (nextval('studiesSequence'),name, '',3, 0, register_start, register_end)
SELECT name, register_start, register_end from old_study;

This is how my table in DB2 looks:

CREATE TABLE studies(
    id int8 PRIMARY KEY NOT NULL,
    name_string VARCHAR(255) NOT NULL,
    description VARCHAR(255),
    field int8 REFERENCES options_table(id) NOT NULL,
    is_active INTEGER NOT NULL,
    register_start DATE NOT NULL,
    register_end DATE NOT NULL
);

Answer 1

You should include the column names in both the insert and select:

insert into vip_employees(name, age, occupation)
    select name, age, occupation
    from employees;

However, your data structure is suspect. Either you should use a flag in employees to identify the "VIP employees". Or you should have a primary key in employees and use this primary key in vip_employees to refer to employees. Copying over the data fields is rarely the right thing to do, especially for columns such as age which are going to change over time. Speaking of that, you normally derive age from the date of birth, rather than storing it directly in a table.

Answer 2

Loop and cursor are weapons of last resort. Try to avoid them. You probably want INSERT INTO ... SELECT:

INSERT INTO x(x, y, z)
SELECT x, y, z
FROM t;

SqlFiddleDemo

EDIT:

INSERT INTO vip_employees(name, age, occupation) -- your column list may vary
SELECT name, age, occupation
FROM employees;

Answer 3

INSERT INTO studies
(
    id
    ,name_string
    ,description
    ,field
    ,is_active
    ,register_start
    ,register_end
    )

SELECT nextval('studiesSequence')
    ,NAME
    ,''
    ,3
    ,0
    ,register_start
    ,register_end
FROM dblink('dbname=mydb', 'select name,begins,ends from study') 
     AS t1(NAME VARCHAR(50), register_start DATE, register_end DATE);

You can directly insert values that retured by dblink()(that means no need to create a view)

Answer 4

Your syntax is wrong. You cannot have both, a values clause for constant values and a select clause for a query in your INSERT statement.

You'd have to select constant values in your query:

insert into studies 
(
  id,
  name_string,
  description,
  field,
  is_active,
  register_start,
  register_end
)
select
  studiesSequence.nextval,
  name, 
  'Test', 
  null,
  0,
  register_start, 
  register_end
from old_study;