java大批量数据传输_java-如何以编程方式在表之间传输大量数据...

我有两个表,其中第一个表有1400万,第二个表中有150万数据.

所以我想知道如何将这些数据传输到另一个要标准化的表中？

以及如何将某种类型转换为另一种类型,例如：我有一个名为“ year”的字段,但其类型为varchar,但我想用一个整数代替,我该怎么做？

我曾考虑过从Java循环使用JDBC来做到这一点,但是我认为这样做没有效率.

// 1.5 million of data

CREATE TABLE dbo.directorsmovies

(

movieid INT NULL,

directorid INT NULL,

dname VARCHAR (500) NULL,

addition VARCHAR (1000) NULL

)

//14 million of data

CREATE TABLE dbo.movies

(

movieid VARCHAR (20) NULL,

title VARCHAR (400) NULL,

mvyear VARCHAR (100) NULL,

actorid VARCHAR (20) NULL,

actorname VARCHAR (250) NULL,

sex CHAR (1) NULL,

as_character VARCHAR (1500) NULL,

languages VARCHAR (1500) NULL,

genres VARCHAR (100) NULL

)

这是我的新表：

DROP TABLE actor

CREATE TABLE actor (

id INT PRIMARY KEY IDENTITY,

name VARCHAR(200) NOT NULL,

sex VARCHAR(1) NOT NULL

)

DROP TABLE actor_character

CREATE TABLE actor_character(

id INT PRIMARY KEY IDENTITY,

character VARCHAR(100)

)

DROP TABLE director

CREATE TABLE director(

id INT PRIMARY KEY IDENTITY,

name VARCHAR(200) NOT NULL,

addition VARCHAR(150)

)

DROP TABLE movie

CREATE TABLE movie(

id INT PRIMARY KEY IDENTITY,

title VARCHAR(200) NOT NULL,

year INT

)

DROP TABLE language

CREATE TABLE language(

id INT PRIMARY KEY IDENTITY,

language VARCHAR (100) NOT NULL

)

DROP TABLE genre

CREATE TABLE genre(

id INT PRIMARY KEY IDENTITY,

genre VARCHAR(100) NOT NULL

)

DROP TABLE director_movie

CREATE TABLE director_movie(

idDirector INT,

idMovie INT,

CONSTRAINT fk_director_movie_1 FOREIGN KEY (idDirector) REFERENCES director(id),

CONSTRAINT fk_director_movie_2 FOREIGN KEY (idMovie) REFERENCES movie(id),

CONSTRAINT pk_director_movie PRIMARY KEY(idDirector,idMovie)

)

DROP TABLE genre_movie

CREATE TABLE genre_movie(

idGenre INT,

idMovie INT,

CONSTRAINT fk_genre_movie_1 FOREIGN KEY (idMovie) REFERENCES movie(id),

CONSTRAINT fk_genre_movie_2 FOREIGN KEY (idGenre) REFERENCES genre(id),

CONSTRAINT pk_genre_movie PRIMARY KEY (idMovie, idGenre)

)

DROP TABLE language_movie

CREATE TABLE language_movie(

idLanguage INT,

idMovie INT,

CONSTRAINT fk_language_movie_1 FOREIGN KEY (idLanguage) REFERENCES language(id),

CONSTRAINT fk_language_movie_2 FOREIGN KEY (idMovie) REFERENCES movie(id),

CONSTRAINT pk_language_movie PRIMARY KEY (idLanguage, idMovie)

)

DROP TABLE movie_actor

CREATE TABLE movie_actor(

idMovie INT,

idActor INT,

CONSTRAINT fk_movie_actor_1 FOREIGN KEY (idMovie) REFERENCES movie(id),

CONSTRAINT fk_movie_actor_2 FOREIGN KEY (idActor) REFERENCES actor(id),

CONSTRAINT pk_movie_actor PRIMARY KEY (idMovie,idActor)

)

更新：

我正在使用SQL Server 2008.

抱歉,我忘了提到的是不同的数据库：

未规范化的是调用纪律数据库和我的规范化的调用imdb.

最好的祝福,

Valter Henrique.