Thread: INSERT INTO ... SELECT
Hi, I would like to insert into a table values from a table and user defined ones. Here is the example: I found this statement to insert values from another table: INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER; But the test_table has another column, which should have the same value for all the customers. Is there something like INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, name from CUSTOMER: and if so, what ist the correct statement? If not, what is an alternative to insert a single row at a time? Thanks in advance Silke
Centuries ago, Nostradamus foresaw when trissl@informatik.hu-berlin.de (Silke Trissl) would write: > I would like to insert into a table values from a table and user > defined ones. Here is the example: > > I found this statement to insert values from another table: > > INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER; > > But the test_table has another column, which should have the same > value for all the customers. > > Is there something like > > INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, > name from CUSTOMER: > > and if so, what ist the correct statement? If not, what is an > alternative to insert a single row at a time? You're close. The constant term needs to be inside the SELECT. Try: insert into test_table (int_id, cust_id, cust_name) select '1', id, name from customer; -- select 'cbbrowne' || '@' || 'acm.org'; http://www.ntlug.org/~cbbrowne/sap.html (eq? 'truth 'beauty) ; to avoid unassigned-var error, since compiled code ; will pick up previous valueto var set!-ed, ; the unassigned object. -- from BBN-CL's cl-parser.scm
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Monday 11 August 2003 03:21, Silke Trissl wrote: > > Is there something like > > INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, > name from CUSTOMER: > > and if so, what ist the correct statement? If not, what is an > alternative to insert a single row at a time? > I'm not too sure on what you want, but I'll guess. INSERT INTO test_table(int_id, cust_id, cust_name) SELECT1, id, name FROM customer; Does this do what you want? - -- Jonathan Gardner <jgardner@jonathangardner.net> Live Free, Use Linux! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/ORhhWgwF3QvpWNwRAsoTAJ9Wq+iZ3/JbapXeS1wrv9VgR1gtAgCfVItU ZsBeXCPBuLuOgBTWn3vu2Gc= =PJDU -----END PGP SIGNATURE-----
Coincidentally, I have a very similar case using some twists. The table I want to insert data is something like table A ( user01 int, user02 int, ... primary key (user01, user02), CHECK ( user01 < user02 ) ); And the user table is: tabe user_table ( user int constraint pk_user primary key UNIQUE, --PK ..., email varchar(25) not null, ... }; In the insertion statement, the user02 is obtained by a subselect statement: "select user from user where email=?". There may not exist the email in the user table. And if yes, the check condition may not be met. If the condition is notmet, the two user IDs need to be switched. How the query shall be construted? Thanks for any suggestions. v. -- --------- Original Message --------- DATE: Mon, 11 Aug 2003 21:57:05 From: Christopher Browne <cbbrowne@acm.org> To: pgsql-sql@postgresql.org Cc: >Centuries ago, Nostradamus foresaw when trissl@informatik.hu-berlin.de (Silke Trissl) would write: >> I would like to insert into a table values from a table and user >> defined ones. Here is the example: >> >> I found this statement to insert values from another table: >> >> INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER; >> >> But the test_table has another column, which should have the same >> value for all the customers. >> >> Is there something like >> >> INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, >> name from CUSTOMER: >> >> and if so, what ist the correct statement? If not, what is an >> alternative to insert a single row at a time? > >You're close. > >The constant term needs to be inside the SELECT. > >Try: > insert into test_table (int_id, cust_id, cust_name) > select '1', id, name from customer; >-- >select 'cbbrowne' || '@' || 'acm.org'; >http://www.ntlug.org/~cbbrowne/sap.html >(eq? 'truth 'beauty) ; to avoid unassigned-var error, since compiled code > ; will pick up previous value to var set!-ed, > ; the unassigned object. >-- from BBN-CL's cl-parser.scm > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > ____________________________________________________________ Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005
> > Coincidentally, I have a very similar case using some twists. > > The table I want to insert data is something like > > table A ( > user01 int, > user02 int, > ... > primary key (user01, user02), > CHECK ( user01 < user02 ) > ); > > And the user table is: > > tabe user_table ( > user int constraint pk_user primary key UNIQUE, --PK > ..., > email varchar(25) not null, > ... > }; > > In the insertion statement, the user02 is obtained by a subselect statement: "select user from user where email=?". > > There may not exist the email in the user table. And if yes, the check condition may not be met. If the condition is n ot met, the two user IDs need to be switched. > > How the query shall be construted? > > Thanks for any suggestions. > I'd say this conditional insert into user02 column can be done by a PL/pgSQL function combined by CASE statements. I'm thinking of something like (untested of course) INSERT INTO ... SELECT CASE WHEN user01 < get_user02(email=?) THEN user01 ELSE get_user02(email=?) END, CASE WHEN user01 < get_user02(email=?) THEN get_user02(email=?) ELSE user01 END, ... FROM table A ... ; You might think calling the function four times is a waste, but as far as I understand it PL/pgSQL functions are run via prepared execution plans (see PL/pgSQL - SQL Procedural Language - Overview), so it shouldn't be too bad. Don't know what to do about > There may not exist the email in the user table. Insert a NULL? HTH Regards, Christoph
Thanks, Christoph for your thought. An alternative solution I have is to fetch the user table first and act according with the retured value. It doesn't seemto have a single query solution. v. -- --------- Original Message --------- DATE: Wed, 13 Aug 2003 13:40:53 From: Christoph Haller <ch@rodos.fzk.de> To: pgsql-sql@postgresql.org, vwu98034@lycos.com Cc: >> >> Coincidentally, I have a very similar case using some twists. >> >> The table I want to insert data is something like >> >> table A ( >> user01 int, >> user02 int, >> ... >> primary key (user01, user02), >> CHECK ( user01 < user02 ) >> ); >> >> And the user table is: >> >> tabe user_table ( >> user int constraint pk_user primary key UNIQUE, --PK >> ..., >> email varchar(25) not null, >> ... >> }; >> >> In the insertion statement, the user02 is obtained by a subselect >statement: "select user from user where email=?". >> >> There may not exist the email in the user table. And if yes, the check >condition may not be met. If the condition is n >ot met, the two user IDs need to be switched. >> >> How the query shall be construted? >> >> Thanks for any suggestions. >> > >I'd say this conditional insert into user02 column can be done by a >PL/pgSQL function >combined by CASE statements. >I'm thinking of something like (untested of course) >INSERT INTO ... >SELECT >CASE WHEN user01 < get_user02(email=?) >THEN user01 ELSE get_user02(email=?) END, >CASE WHEN user01 < get_user02(email=?) >THEN get_user02(email=?) ELSE user01 END, >... >FROM table A ... ; > >You might think calling the function four times is a waste, >but as far as I understand it PL/pgSQL functions are run via >prepared execution plans (see PL/pgSQL - SQL Procedural Language - >Overview), >so it shouldn't be too bad. >Don't know what to do about >> There may not exist the email in the user table. >Insert a NULL? > >HTH > >Regards, Christoph > > > ____________________________________________________________ Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005