0

I have a 2 tables, I want to set a job for filling table 1 with some data in table 2. but there are some other columns in table1 that are not in table2. these cloumns have constant value. for example: table1: name, date,number (random) table 2: name,date

i wanna put name and date from table 2 into table 1 automatically. but I dont know how to fill third coulmun.

thanks

Afsan Rezaei
  • 21
  • 7
  • What would the random number be based on? and can you give an example. – P.Salmon Dec 22 '20 at 11:36
  • It's not important. some of them are 0 or 1, and the other are forexample between 1 and 1000. my question is how to fill a table with some constant value and others from a existing table? – Afsan Rezaei Dec 22 '20 at 11:41

1 Answers1

0

my question is how to fill a table with some constant value and others from a existing table?

Just like that:

insert into table1(name, date, num)
select name, date, 1 from table2

This assigns a constant value of 1 to column num in every row that is copied from the source.

If you want something else, say an auto-incrementing counter:

insert into table1(name, date, num)
select name, date, row_number() over(order by (select null)) from table2

If you had a default on the target column that you wanted to rely on, you would just ignore the column in the statement:

insert into table1(name, date)
select name, date from table2
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you. but table2 has not constant value. I want to fill my table1 column with some data from table 2 . the other table1 columns are constant and I have to fill them myself! there aren't in table 2 – Afsan Rezaei Dec 22 '20 at 12:28
  • I have to fill some columns with table 2 values and the others with constant value ( for example value from 1 to 1000) – Afsan Rezaei Dec 22 '20 at 12:32
  • @AfsanRezaei: that's what the first query does. I extended my answer with another example. – GMB Dec 22 '20 at 12:33
  • @AfsanRezaei Do you understand the meaning of "constant"? It does not vary or mutate or change (e.g., 1 to 1000). Now would be a good time to provide a simple example of a few rows you want to insert and what the content of those inserted rows should be after insertion. – SMor Dec 22 '20 at 15:28