查表的数据来拼接insert语句的写法

--实际拜访表
alter table sfa_t_tplaactualvisit add a_id int,a_remark varchar(10)

select * from sfa_t_tplaactualvisit where actualvisitid >= 14343

select * from sfa_t_tplaactualvisit where a_id is not null

--在60.11执行
select '
 insert into dbo.sfa_t_tplaactualvisit
 (
      [VisitPlanID]
      ,[usernumber]
      ,[UserName]
      ,[StoreID]
      ,[StoreName]
      ,[Visit_StartTime]
      ,[Visit_EndTime]
      ,[VisitContent]
      ,[LostReport]
      ,[VisitSummary]
      ,[ReachPhoto]
      ,[LatLng]
      ,[PositioningType]
      ,[SystemDate]
      ,[EndTime]
      ,[Guid]
      ,[leavephoto]
      ,[xinkehuxiaojie]
      ,[qiaotangxiaojie]
      ,[xinpinxiaojie]
      ,[duizhangxiaojie]
      ,[feiyongxiaojie]
      ,[otherxiaojie]
   ,a_remark
      ,[a_id]
 
 )
 values
 (
 '  
 +           
    ISNULL('''' + cast([VisitPlanID] as varchar) + ''',', 'null,') +
    ISNULL('''' + cast([usernumber] as varchar) + ''',', 'null,') +
    ISNULL('''' + cast([UserName] as varchar) + ''',', 'null,') +
    ISNULL('''' + cast([StoreID] as varchar) + ''',', 'null,') +
    ISNULL('''' + cast([StoreName] as varchar) + ''',', 'null,') +
    ISNULL('''' + cast([Visit_StartTime] as varchar)+ ''',', 'null,') +
    ISNULL('''' + cast([Visit_EndTime] as varchar)+ ''',', 'null,') +
    ISNULL('''' + cast([VisitContent] as varchar)+ ''',', 'null,') +
    ISNULL('''' + cast([LostReport] as varchar)+ ''',', 'null,') +
    ISNULL('''' + cast([VisitSummary] as varchar)+ ''',', 'null,') +
    ISNULL('''' + cast([ReachPhoto] as varchar(50))+ ''',', 'null,') +
             ISNULL('''' + cast([LatLng] as varchar)+ ''',', 'null,') +
             ISNULL('''' + cast([PositioningType] as varchar(300))+ ''',', 'null,') +
             ISNULL('''' + cast([SystemDate] as varchar)+ ''',', 'null,') +
             ISNULL('''' + cast([EndTime] as varchar)+ ''',', 'null,') +
       ISNULL('''' + cast([Guid] as nvarchar(50)) + ''',', 'null,') +
    ISNULL('''' + cast([leavephoto] as nvarchar(50)) + ''',', 'null,') +
    ISNULL('''' + cast([xinkehuxiaojie] as nvarchar(50)) + ''',', 'null,') +
             ISNULL('''' + cast([qiaotangxiaojie] as nvarchar(50)) + ''',', 'null,') +
             ISNULL('''' + cast([xinpinxiaojie] as nvarchar(50)) + ''',', 'null,') +
             ISNULL('''' + cast([duizhangxiaojie] as nvarchar(50)) + ''',', 'null,') +
             ISNULL('''' + cast([feiyongxiaojie] as nvarchar(50)) + ''',', 'null,') +
             ISNULL('''' + cast([otherxiaojie] as nvarchar(50)) + ''',', 'null,') +
    '''1'','+ 
    ISNULL('''' + cast(ActualVisitID as varchar)+ ''');', 'null);
 '
 ) as xxx
 from xw_dl_1007724.dbo.sfa_t_tplaactualvisit where ActualVisitID >= 14343

 --根据guid更新实际拜访表storeid
with a as
(
select t1.ActualVisitID,t2.guid,t2.storeid from sfa_t_tplaactualvisit t1
left join sfa_t_tbasstore t2 on t1.guid = t2.guid
where t1.a_id is not null and t1.storeid != t2.Storeid
)
update sfa_t_tplaactualvisit set storeid = a.storeid from a
where a.ActualVisitID = sfa_t_tplaactualvisit.ActualVisitID

 

 --是否存在重复的数据 visitplanid,usernumber,guid
 select visitplanid,usernumber,guid from sfa_t_tplaactualvisit
 where visitplanid is not null
 group by  visitplanid,usernumber,guid  having count(*)>1

 --存在重复的数据即做处理
 select * from sfa_t_tplaactualvisit t1 inner join
 (
  select visitplanid,usernumber,guid from sfa_t_tplaactualvisit
  where visitplanid is not null
  group by  visitplanid,usernumber,guid  having count(*)>1
 )
 t2 on t1.visitplanid = t2.visitplanid and t1.usernumber = t2.usernumber and t1.guid = t2.guid

 

 --有重复即删除
delete from sfa_t_tplaactualvisit where ActualVisitID in
(
  select t1.ActualVisitID from sfa_t_tplaactualvisit t1 inner join
  (
    select visitplanid,usernumber,guid from sfa_t_tplaactualvisit
       where visitplanid is not null
       group by  visitplanid,usernumber,guid  having count(*)>1
  ) t2 on t1.visitplanid = t2.visitplanid and t1.usernumber = t2.usernumber and t1.guid = t2.guid
  where a_id is not null
)