declare @strQuery varchar(8000)
set @strQuery = 'insert into table1 (UserID,SP_Name,ServerIP,Real_Act) select '+convert(varchar,@Gm_UserID)+' ,''wwww_Open'' ,'+@ServerIP+',''insert into ['+@ServerIP+'].owner.dbo.table2 (User_Index_ID,User_id,ban_date) values ('''+convert(varchar,User_Index_id)+''','''+User_id +''','''+substring(convert(varchar,ban_date, 20), 1,20)+''')' from ['+@ServerIP+'].owner.dbo.table2 where USER_ID='''+@UserID+''''
print @strQuery
这句拼的语句老报错,现在脑子晕了,搞不清东南西北了
拼之后print @strQuery 结果是下面这样
insert into table1 (UserID,SP_Name,ServerIP,Real_Act) select 11 ,'wwww_Open' ,'192.168.0.2','insert into [192.168.0.2].owner.dbo.table2 (User_Index_ID,User_id,ban_date) values ( '''+convert(varchar,User_Index_id)+''','''+ User_id+''','''+ substring(convert(varchar,ban_date, 20), 1,20)+''')' from [192.168.0.2].owner.dbo.table2 where USER_ID='xxxxx'
求一下这个怎么拼
set @strQuery = 'insert into table1 (UserID,SP_Name,ServerIP,Real_Act) select '+convert(varchar,@Gm_UserID)+' ,''wwww_Open'' ,'+@ServerIP+',''insert into ['+@ServerIP+'].owner.dbo.table2 (User_Index_ID,User_id,ban_date) values ('''+convert(varchar,User_Index_id)+''','''+User_id +''','''+substring(convert(varchar,ban_date, 20), 1,20)+''')' from ['+@ServerIP+'].owner.dbo.table2 where USER_ID='''+@UserID+''''
print @strQuery
这句拼的语句老报错,现在脑子晕了,搞不清东南西北了
拼之后print @strQuery 结果是下面这样
insert into table1 (UserID,SP_Name,ServerIP,Real_Act) select 11 ,'wwww_Open' ,'192.168.0.2','insert into [192.168.0.2].owner.dbo.table2 (User_Index_ID,User_id,ban_date) values ( '''+convert(varchar,User_Index_id)+''','''+ User_id+''','''+ substring(convert(varchar,ban_date, 20), 1,20)+''')' from [192.168.0.2].owner.dbo.table2 where USER_ID='xxxxx'
求一下这个怎么拼
应该是insert into table2 values ('sss','dd','fff') 少了''简单的sql报错报'sss','dd','fff' 没有这个列名
declare @Gm_UserID varchar(20),@ServerIP varchar(20),@UserID varchar(20)
set @Gm_UserID = 'TEST1'
set @ServerIP = 'TEST2'
set @UserID = 'TEST3'
set @strQuery = 'insert into table1 (UserID,SP_Name,ServerIP,Real_Act) select '''+convert(varchar(20),@Gm_UserID)+''' ,''wwww_Open'' ,'''+@ServerIP+''',''insert into ['+@ServerIP+'].owner.dbo.table2 (User_Index_ID,User_id,ban_date) values (''''''+convert(varchar,User_Index_id)+'''''',''''''+User_id +'''''',''''''+substring(convert(varchar,ban_date, 20), 1,20)+'''''')'' from ['+@ServerIP+'].owner.dbo.table2 where USER_ID='''+@UserID+''''
print @strQuery
insert into table1 (UserID,SP_Name,ServerIP,Real_Act) select 'TEST1' ,'wwww_Open' ,'TEST2','insert into [TEST2].owner.dbo.table2 (User_Index_ID,User_id,ban_date) values ('''+convert(varchar,User_Index_id)+''','''+User_id +''','''+substring(convert(varchar,ban_date, 20), 1,20)+''')' from [TEST2].owner.dbo.table2 where USER_ID='TEST3'
declare @Gm_UserID int,@ServerIP int,@UserID varchar(20)
set @Gm_UserID = 100083
set @ServerIP = 105
set @UserID = 'TEST3'
set @strQuery = 'insert into table1 (UserID,SP_Name,ServerIP,Real_Act) select '+convert(varchar(20),@Gm_UserID)+' ,''wwww_Open'' ,'+rtrim(@ServerIP)+',''insert into ['+rtrim(@ServerIP)+'].owner.dbo.table2 (User_Index_ID,User_id,ban_date) values (''''''+convert(varchar,User_Index_id)+'''''',''''''+User_id +'''''',''''''+substring(convert(varchar,ban_date, 20), 1,20)+'''''')'' from ['+rtrim(@ServerIP)+'].owner.dbo.table2 where USER_ID='''+@UserID+''''
print @strQuery/*
insert into table1 (UserID,SP_Name,ServerIP,Real_Act) select 100083 ,'wwww_Open' ,105,'insert into [105].owner.dbo.table2 (User_Index_ID,User_id,ban_date) values ('''+convert(varchar,User_Index_id)+''','''+User_id +''','''+substring(convert(varchar,ban_date, 20), 1,20)+''')' from [105].owner.dbo.table2 where USER_ID='TEST3'
*/
+' ,''wwww_Open'' ,'''+@ServerIP+''',insert into ['+@ServerIP+'].owner.dbo.table2(User_Index_ID,User_id,ban_date) values ('
+'convert(varchar,User_Index_id),User_id ,substring(convert(varchar,ban_date, 20), 1,20)'
+') from ['+@ServerIP+'].owner.dbo.table2 where USER_ID='''+@UserID+''''print @strQuery
declare @ServerIP nvarchar(20)
declare @Gm_UserID int
declare @UserID nvarchar(20)
set @ServerIP='192.168.0.2'
set @Gm_UserID=11
set @UserID='xxxx'set @strQuery = 'insert into table1 (UserID,SP_Name,ServerIP,Real_Act) select
'+convert(varchar,@Gm_UserID)+' ,''wwww_Open'' ,'''+@ServerIP+''',
''insert into ['+@ServerIP+'].owner.dbo.table2 (User_Index_ID,User_id,ban_date) values
( ''''''+convert(varchar,User_Index_id)+'''''',''''''+ User_id+'''''',''''''+
substring(convert(varchar,ban_date, 20), 1,20)+'''''')''
from ['+@ServerIP+'].owner.dbo.table2 where USER_ID=''xxxxx'''
print @strQuery