>>>>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?
>
>Merge is bullet-proof command, so you can run it multiple times. INSERT SQL will work as well once, but you can not run it the second time. So, if you're using SQL 2008 and up I suggest to forget about INSERT-SQL and UPDATE commands and always use MERGE command. The syntax will take a bit of time to get familiar with, but you will be better protected using this command.
>
>BTW, the correct syntax for above INSERT will be:
>

>insert into Ten_name (ten_pk, user_id, user_pwrd)
>select ten_pk, user_id, user_pwrd from Tenant x

Thank you. Then, in your syntax the 'x' after Tenant is not really necessary, right?