Write SQL script to insert data

Question

In a database that contains many tables, I need to write a SQL script to insert data if it is not exist.

Table currency

| id     | Code    | lastupdate | rate      |
+--------+---------+------------+-----------+
| 1      | USD     | 05-11-2012 | 2         |
| 2      | EUR     | 05-11-2012 | 3         |

Table client

| id     | name    | createdate | currencyId|
+--------+---------+------------+-----------+
| 4      | tony    | 11-24-2010 | 1         |
| 5      | john    | 09-14-2010 | 2         |

Table: account

| id     | number  | createdate | clientId  |
+--------+---------+------------+-----------+
| 7      | 1234    | 12-24-2010 | 4         |
| 8      | 5648    | 12-14-2010 | 5         |

I need to insert to:

currency (id=3, Code=JPY, lastupdate=today, rate=4)
client (id=6, name=Joe, createdate=today, currencyId=Currency with Code 'USD')
account (id=9, number=0910, createdate=today, clientId=Client with name 'Joe')

Problem:

script must check if row exists or not before inserting new data
script must allow us to add a foreign key to the new row where this foreign related to a row already found in database (as currencyId in client table)
script must allow us to add the current datetime to the column in the insert statement (such as createdate in client table)
script must allow us to add a foreign key to the new row where this foreign related to a row inserted in the same script (such as clientId in account table)

Note: I tried the following SQL statement but it solved only the first problem

INSERT INTO Client (id, name, createdate, currencyId)
SELECT 6, 'Joe', '05-11-2012', 1
WHERE not exists (SELECT * FROM Client where id=6);

this query runs without any error but as you can see I wrote createdate and currencyid manually, I need to take currency id from a select statement with where clause (I tried to substitute 1 by select statement but query failed).

This is an example about what I need, in my database, I need this script to insert more than 30 rows in more than 10 tables.

any help

and do you want a trigger, stored procedure to do this or maybe a php script? — Manuel, May 11 '12 at 07:47
i use postgresql, i need a script that can i run it to insert data, whoever what is its type, but i think trigger not suitable here, and stored procedure will be the best way to do this — , May 11 '12 at 08:00
You need to select the currency_id from the currency table. You search criterium will be currencycode = 'USD'. Similar for the insert into the account table, which will have to obtain the client_id from the client table. BTW: don't use "name" as a column name in pg. It is a reserved word. (I am not sure about "code", but I would never use that either) BTW: the "not exists" looks good. — wildplasser, May 11 '12 at 08:52

score 0 · Accepted Answer · answered May 11 '12 at 08:50

0

You wrote

I tried to substitute 1 by select statement but query failed

But I wonder why did it fail? What did you try? This should work:

INSERT INTO Client (id, name, createdate, currencyId)
SELECT 
    6, 
    'Joe', 
    current_date, 
    (select c.id from currency as c where c.code = 'USD') as currencyId
WHERE not exists (SELECT * FROM Client where id=6);

answered May 11 '12 at 08:50

Thomas Mueller

48,905
14
116
132

You don't need the subquery. It can be a plain "select ... FROM ...". – wildplasser May 11 '12 at 08:56
Yes, it could be a plain select. But a subquery also works, and might be clearer. Also, the insert would still work even if the row was not found, just with null. – Thomas Mueller May 11 '12 at 09:00
It is the kind of place where I would *not* appreciate a nullable foreign key ;-) – wildplasser May 11 '12 at 09:03
i try your subquery its work, but i still have a problem (number 3). how can i get the current datetime to select it in the daabase? – May 11 '12 at 09:16
@danny-london: The current datetime can be selected using `current_date` as I have used in my example insert statement. PostgreSQL also supports converting the value to a varchar if this is what you require (see the documentation for `cast`). – Thomas Mueller May 11 '12 at 09:33
i know, but i mean that i need current dateTime, not only current date – May 11 '12 at 09:37
@wildplasser: In the Client table has a constraint (foreign key, or even `not null`), then the insert would fail. Which might be better (clearer) than if no row was inserted at all. – Thomas Mueller May 11 '12 at 09:37
@danny-london: Well, what about if you use Google or check the PostgreSQL documentation for that? – Thomas Mueller May 11 '12 at 09:38
thanks Thomas Mueller i found (Now()) function that works fine. thanks all – May 11 '12 at 09:39

score 0 · Answer 2 · answered May 11 '12 at 08:57

It looks like you can work out if the data exists. Here is a quick bit of code written in SQL Server / Sybase that I think answers you basic questions:

create table currency(
    id numeric(16,0) identity primary key,
    code varchar(3) not null,
    lastupdated datetime not null,
    rate smallint
);

create table client(
    id numeric(16,0) identity primary key,
    createddate datetime not null,
    currencyid numeric(16,0) foreign key references currency(id)
);

insert into currency (code, lastupdated, rate)
values('EUR',GETDATE(),3)

--inserts the date and last allocated identity into client
insert into client(createddate, currencyid)
values(GETDATE(), @@IDENTITY)

go

IMHO there is no "IDENTITY" in sql. You might be confused by Microsoft? — wildplasser, May 11 '12 at 09:22
GETDATE() didn't work, it gives an error (No function matches the given name and argument types. You might need to add explicit type casts.) — , May 11 '12 at 09:32

Write SQL script to insert data

2 Answers2