sql server - SQL - How to insert values while checking condition based on another table? If null generate new ID -
i given task use cursor duplicate tables. (don't ask me why or suggest me use other way cause that's part of requirement of task given)
set ansi_nulls on go set quoted_identifier on go alter procedure [dbo].[duplicatecompanyinfo] @comp_companyid nvarchar(80) begin set nocount on; declare @companyid nvarchar(30), @personid nvarchar(30), @personlinkid nvarchar(30), @addresslinkid nvarchar(30), @addressid nvarchar(30), @phonelinkid nvarchar(30), @phoneid nvarchar(30), @persphonelinkid nvarchar(30), @persphoneid nvarchar(30) exec @companyid = crm_next_id 5 exec @personid = crm_next_id 13 exec @addressid = crm_next_id 1 -- add company insert company ( comp_companyid, comp_primarypersonid, comp_primaryaddressid, comp_name, comp_type, comp_status, comp_createdby, comp_createddate, comp_updatedby, comp_updateddate, comp_timestamp, comp_secterr, comp_website ) select @companyid, @personid, @addressid, comp_name, comp_type, comp_status, '1', getdate(), '1', getdate(), getdate(), comp_secterr, comp_website company comp_companyid = @comp_companyid , comp_deleted null ---- personlink cursor ---------------------------------------------- -- declare variables declare @c_peli_personlinkid nvarchar(30) declare @c_peli_personid nvarchar(30) declare @c_peli_companyid nvarchar(30) declare @c_peli_createdby nvarchar(30) declare @c_peli_createddate nvarchar(30) declare @c_peli_updatedby nvarchar(30) declare @c_peli_updateddate nvarchar(30) declare @c_peli_timestamp nvarchar(30) --declare cursor declare personlinkcursor cursor select peli_personlinkid, peli_personid, peli_companyid, peli_createdby, peli_createddate, peli_updatedby, peli_updateddate, peli_timestamp person_link inner join person on peli_personid = pers_personid , peli_companyid = @comp_companyid , pers_deleted null , peli_deleted null --open cursor & fetch 1st row variables open personlinkcursor fetch next personlinkcursor @c_peli_personlinkid, @c_peli_personid, @c_peli_companyid, @c_peli_createdby, @c_peli_createddate, @c_peli_updatedby, @c_peli_updateddate, @c_peli_timestamp --fetch successful --check new row while @@fetch_status = 0 begin exec @c_peli_personlinkid = crm_next_id 31 exec @personid = crm_next_id 13 insert person_link ( peli_personlinkid, peli_personid, peli_companyid, peli_createdby, peli_createddate, peli_updatedby, peli_updateddate, peli_timestamp ) values ( @c_peli_personlinkid, @personid, @companyid, '1', getdate(), '1', getdate(), getdate() ) --get next available row variables fetch next personlinkcursor @c_peli_personlinkid, @c_peli_personid, @c_peli_companyid, @c_peli_createdby, @c_peli_createddate, @c_peli_updatedby, @c_peli_updateddate, @c_peli_timestamp end close personlinkcursor deallocate personlinkcursor ---- person cursor ---------------------------------------------- -- declare variables declare @c_pers_personid nvarchar(30) declare @c_pers_companyid nvarchar(30) declare @c_pers_primaryuserid nvarchar(30) declare @c_pers_firstname nvarchar(30) declare @c_pers_secterr nvarchar(30) declare @c_pers_createdby nvarchar(30) declare @c_pers_createddate nvarchar(30) declare @c_pers_updatedby nvarchar(30) declare @c_pers_updateddate nvarchar(30) declare @c_pers_timestamp nvarchar(30) --declare cursor declare personcursor cursor select pers_personid, pers_companyid, pers_primaryuserid, pers_firstname, pers_secterr, pers_createdby, pers_createddate, pers_updatedby, pers_updateddate, pers_timestamp person inner join person_link on pers_personid = peli_personid , peli_companyid = @comp_companyid , pers_deleted null , peli_deleted null --open cursor & fetch 1st row variables open personcursor fetch next personcursor @c_pers_personid, @c_pers_companyid, @c_pers_primaryuserid, @c_pers_firstname, @c_pers_secterr, @c_pers_createdby, @c_pers_createddate, @c_pers_updatedby, @c_pers_updateddate, @c_pers_timestamp --fetch successful --check new row while @@fetch_status = 0 begin exec @personid = crm_next_id 13 insert person ( pers_personid, pers_companyid, pers_primaryuserid, pers_firstname, pers_secterr, pers_createdby, pers_createddate, pers_updatedby, pers_updateddate, pers_timestamp ) values ( @personid, @companyid, @c_pers_primaryuserid, @c_pers_firstname, @c_pers_secterr, '1', getdate(), '1', getdate(), getdate() ) --get next available row variables fetch next personcursor @c_pers_personid, @c_pers_companyid, @c_pers_primaryuserid, @c_pers_firstname, @c_pers_secterr, @c_pers_createdby, @c_pers_createddate, @c_pers_updatedby, @c_pers_updateddate, @c_pers_timestamp end close personcursor deallocate personcursor end this table structure:
company table:
comp_companyid | comp_primarypersonid | comp_primaryaddressid | comp_name ------------------------------------------------------------------------- 2 | 2 | 2 | company 2 3 | 3 | 3 | company 3 person table:
pers_personid | pers_companyid | pers_primaryaddressid | pers_name ------------------------------------------------------------------------- 2 | 2 | 2 | person 2 3 | 3 | 3 | person 3 4 | 2 | 2 | person 4 person link table:
peli_personlinkid | peli_personid | peli_companyid ------------------------------------------------------------------------- 2 | 2 | 2 3 | 3 | 3 4 | 4 | 2 now i'm inserting values each table in cursor loop shown above. insert value of peli_personid personlink & pers_personid person based on column comp_primarypersonid company on if id exists, use id, else generate next id. how do that? thanks!
i've tried this:
insert person_link ( peli_personlinkid, peli_personid, peli_companyid, peli_createdby, peli_createddate, peli_updatedby, peli_updateddate, peli_timestamp ) values ( @c_peli_personlinkid,(select @personid company comp_primarypersonid not null , comp_companyid = @comp_companyid), @companyid, '1', getdate(), '1', getdate(), getdate() ) it succeeded in taking value of person id company's comp_primarypersonid peli_personid duplicated not generate new id. how can proper condition check? condition check within loop defines: if comp_primarypersonid's value exists, use value, if not next id.thanks!
same case in prior step - put them single cursor. first - copy person , obtain new personid, second - insert person_link link newly created person.
if need create person_link (which strange because copied all of person_links) "primary" person - need new person_id.
(select @personid company comp_primarypersonid not null , comp_companyid = @comp_companyid) in select refer old @comp_companyid obtain old person_id while needing new 1 (which can found under @companyid id).
but not obtain old id select - because selects constant value of @personid variable not initialized before select contains garbage previous places of usage.
anyway, lost relation primary person new company @ moment because of first insert company - newly generated comp_primarypersonid value links nothing.
one way solve check inside of cursor whether @c_peli_personid same old companies' comp_primarypersonid , update new companies' comp_primarypersonid field @personid value. store old companies' comp_primarypersonid variable @ beginning of script , use in cursor.
Comments
Post a Comment