SQL: select insert into select add fixed value fron another table-CodePudding

Good Mornig,

i have 3 tables:

1° tbl: timestamp_begin, timestamp_end, column1, column2....columnX, id

2° tbl: timestamp, column1, column2 ....columnX

3° tbl: timestamp, column1, column2 ....columnX, id

I need to select all record from tbl 2 WHERE "timestamp" is between "timestamp_begin" and "timestamp_end" off tbl 1 and then insert all record retrieved into a tbl 3 with additional column (equal for each row) with it value is "id" retrieve from tbl 1.

Is possible do that with a single query command?

Thank a lot for any suggestions

Jumpier

CodePudding user response：

Asuming table3 doesn't exist yet:

SELECT t1.id, t2.[timestamp], t2.column1, t2.column2 INTO table03
FROM table02 t2 INNER JOIN table01 t1 ON t2.[timestamp] BETWEEN t1.timestamp_begin AND t1.timestamp_end

If table3 already exists

INSERT INTO table03 SELECT t1.id, t2.[timestamp], t2.column1, t2.column2 
FROM table02 t2 INNER JOIN table01 t1 ON t2.[timestamp] BETWEEN t1.timestamp_begin AND t1.timestamp_end

As @Caius Jard suggested, you could have begun by creating the select to get the records you wanted to insert, and later modifying it with the select into / insert into syntax.