>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?


>After this SQL Insert the table TEN_NAMES will have the fields with the same values.
>
>I am starting to write a SQL Insert but get lost quickly. I would appreciate any help.