insert data to table from select statment

Question

i worked on two tables (persons) and (payments) .payments table contain columns (payment_id , payment_value , payment_class , date , name , person_id) now i want to insert data to payments by select from persons table like that

insert into Payments(payment_id , payment_value , payment_class , date , name , person_id)
select person_id , person_name , person_status from Persons where person_status = 'sub'

how to insert payments to every person selected from persons table ..

What values do you want to insert? You are telling the insert that you will fill all those column but them you select some other different values from persons table. That will not work — Filipe Silva, Dec 08 '13 at 01:12
i just want fill date column with date value i will declare it later and all other columns i want it null — Hamonbatra, Dec 08 '13 at 01:15
And you want the person_id also, right? and the name in payments, is it from a person? — Filipe Silva, Dec 08 '13 at 01:18
ok .. my friend .. i want insert payment with only date value and all other column null to each person selected in query — Hamonbatra, Dec 08 '13 at 01:20

score 0 · Accepted Answer · answered Dec 08 '13 at 01:23

0

As per your requirement, the following query inserts a record with the current date and all other columns null in table payments, for each person

INSERT INTO Payments (payment_id,payment_value,payment_class,date,name,person_id)
SELECT NULL, NULL, NULL, GETDATE(), NULL, NULL
FROM Persons
WHERE person_status = 'sub';

If you want to have person_id extracted directly from table persons, you can do:

INSERT INTO Payments (payment_id,payment_value,payment_class,date,name,person_id)
SELECT NULL, NULL, NULL, GETDATE(), NULL, person_id
FROM Persons
WHERE person_status = 'sub';

answered Dec 08 '13 at 01:23

Filipe Silva

21,189
5
53
68

thanks very much .. one another thing .. if i want change null to parameter take its value from textbox .. and payment_id is primary key .. how can i increase 1 to max value in table to each new record – Hamonbatra Dec 08 '13 at 01:29
You should have it as identity column ([see this answer on how to change it](http://stackoverflow.com/a/6777807/1385896)) and don't pass it at all, and it will get automatically incremented. – Filipe Silva Dec 08 '13 at 01:35
ok i know that but identity cause problem for me .. i want the numbers complete ... not when i delete third row .. the new row will be 4 .. i don't want that !!! – Hamonbatra Dec 08 '13 at 01:39
@user3038625. You can calculate the max in that query, but imagine you have 5 and delete the number 2, when you select the max, it will be 5, and you do +1, and it inserts as 6, but you will still have a hole in your ids. The identity is the best way to handle this. If you need them ordered and you need the to get the records with a column ordered as if there was no holes you can use a rank() (look it up in stackoverflow, there are lots of examples) – Filipe Silva Dec 08 '13 at 01:42

insert data to table from select statment

1 Answers1