>>Hi,
>>
>>I would like to create a SQL Insert (for SQL Server DB) that would add records from one table to another and automatically set fields of certain values. Here is a simplified example:
>>
>>The table which now has records. Name TENANT
>>Fields: TEN_PK, USER_ID, USER_PWRD
>>
>>The table where the records should be added: Name TEN_NAME
>>Fields: TEN_PK, USER_ID, USER_PWRD (basically the same field names)
>>
>>Example:
>>TENANT:
>>TEN_PK, USER_ID, USER_PWRD
>>1 "ABC" "123"
>>2 "CBS" "444"
>>3 "NBC" "9933"
>>
>
>I suggest to use MERGE command (assuming your PK should match), e.g.
>
>;Merge Ten_Name as targ
>using Tenant as srce on targ.Ten_PK = srce.Ten_Pk
>when not matched then insert
>(Ten_Pk, User_Id, User_Pwrd)
>values
>(srce.Ten_PK, srce.User_ID, srce/User_Pwrd)
>
>Your also need to handle cases when matched - probably do nothing and when not matched by source - do you want to keep these rows in your target table or delete them?
>
I am a little lost as to why I need merge. I don't need to check if the records match. That is, the Target Table has no records. And I want to create one record in the Target Table for each record in the Source table. I thought that I just need something like:
insert into Ten_name set ten_pk = x.ten_pk, user_id = x.user_id, user_pwrd = x.user_pwrd from
(select ten_pk, user_id, user_pwrd from Tenant x)
Does the above make sense?
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham