Re: Add columns to table; insert values based on row

Rich Shepard <rshepard(at)appl-ecosys(dot)com> writes:

> On Thu, 1 Nov 2018, Adrian Klaver wrote:
>
>>> alter table stations add column start_date date;
>>> alter table stations add column end_date date;
>>> alter table stations add column howmany integer;
>>> alter table stations add column bin_col char(8);
>>>
>>> insert into stations (start_date, end_date, howmany, bin_col) values ( )
>>> select site_nbr from stations
>>> where site_nbr = ' ';
>
>> Are you trying to add new data to existing records?
>
> Adrian,
>
> I am adding four columns to an existing table that already contains four
> columns.
>
>> If so where is the new data coming from?
>
> I have a text file and will fill each insert statement by hand if there's
> not a more efficient way to do this.
>
> Regards,
>
> Rich

Like others, I'm not clear on exactly what your after here, but did want
to point out

1. If your doing it by hand, you don't have to do a separate 'full'
insert statement for every row i.e.

insert into blah (x, y, z) values
(....),
(....),
(....),
....
(...);

is valid syntax. You don't need to do a full "insert into blah (....)
values (...)" for each insert.

2. If it really is an insert you want to do and you already have the
data in a file e.g. CSV or similar, then you can use the \copy command
to process the file, which is very fast.

3. Is it really insert or update you need?

--
Tim Cross