Subject Re: [firebird-support] Can I insert data where some of the data is decided by the insert statement?
Author Helen Borrie
At 02:08 AM 1/07/2004 +0000, you wrote:
>Hi, I have 2 tables: OLD_USERS, USERS.
>The table(s) ordinarily store user records, but can also store
>templates for users. Someone in the past had the bad idea to indicate
>a template record by putting the string ".STYLE." into the password
>field!!
>
>So I'm trying to get the data from the old table to the new table
>(which has a column IS_TEMPPLATE_RECORD [T/F] ), and whereever I see
>".STYLE." set IS_TEMPPLATE_RECORD to true.
>What I want (which is obviously wrong between the second pair of
>brackets) is:
>
>INSERT INTO USERS (USER_NAME,USER_PASSWORD,IS_TEMPPLATE_RECORD)
>SELECT USERNAME, USERSPASSWORD, ( OLD_USERS.USERSPASSWORD IS '.STYLE.'
>) FROM OLD_USERS;
>
>So how do I achieve this? One option I guess is for a stored procedure
>(but I don't want to do that because I'm targeting more than 1
>database type). Another option is to have two inserts, one where
>OLD_USERS.USERSPASSWORD == '.STYLE.' and the other where
>OLD_USERS.USERSPASSWORD != '.STYLE.'
>
>But is there a way to do this in one statement?

In Firebird 1.5, yes:

INSERT INTO USERS (USER_NAME,USER_PASSWORD,IS_TEMPPLATE_RECORD)
SELECT
USERNAME,
USERSPASSWORD,
CASE WHEN (USERSPASSWORD = '.STYLE.') THEN 'T' ELSE 'F' END
FROM OLD_USERS;

Otherwise, no. SP necessary.

/heLen