select * from split('aa;bb;cc;dd',';')
StringToTable是分隔函数产生的结果如下:
table1
aa
bb
cc
dd想根据这个表写四个sql
如:
update t1 set 字段=aa
update t1 set 字段1=aa
update t1 set 字段2=aa
update t1 set 字段3=aa
该如何写啊?
StringToTable是分隔函数产生的结果如下:
table1
aa
bb
cc
dd想根据这个表写四个sql
如:
update t1 set 字段=aa
update t1 set 字段1=aa
update t1 set 字段2=aa
update t1 set 字段3=aa
该如何写啊?
update t1 set 字段=aa
update t1 set 字段1=bb
update t1 set 字段2=cc
update t1 set 字段3=dd
SET 字段=(SELECT TOP 1 val FROM #Tmp WHERE Row=1),
字段1=(SELECT TOP 1 val FROM #Tmp WHERE Row=2),
字段2=(SELECT TOP 1 val FROM #Tmp WHERE Row=3),
字段3=(SELECT TOP 1 val FROM #Tmp WHERE Row=4)
DROP TABLE #Tmp
table1
aa
bb
cc
dd ---------------------------------------
-- Author: happyflsytone
-- Date:2008-10-22 22:40:26
-------------------------------------- Test Data: table1
IF OBJECT_ID('table1') IS NOT NULL
DROP TABLE table1
Go
CREATE TABLE table1(aa NVARCHAR(2))
Go
INSERT INTO table1
SELECT 'aa' UNION ALL
SELECT 'bb' UNION ALL
SELECT 'cc' UNION ALL
SELECT 'dd'
GO
--Start
declare @s varchar(8000)select @s = isnull(@s+';','') + ' update t1 set 字段='''+aa+''' '
from table1
print @s--Result:
/*update t1 set 字段='aa' ; update t1 set 字段='bb' ; update t1 set 字段='cc' ; update t1 set 字段='dd' */
--End
go
declare @sql varchar(8000),@char varchar(1)
set @char='aa'
select @sql=isnull(@sql,'')+'update t1 set '+name+'='''+@char+''';'
from syscolumns where id=object_id('t1')
select @sql
exec(@sql)update t1 set 字段0='a';
update t1 set 字段1='a';
update t1 set 字段2='a';
update t1 set 字段3='a';
Declare @x xml
Declare @sql nvarchar(4000)Set @string=N'aa;bb;cc;dd'
Set @x=Convert(xml,'<r>'+Replace(@string,';','</r><r>')+'</r>');With t As
(Select id=1,t.v.value('.','nvarchar(50)') As x From @x.nodes('r') t(v))
Select @sql=Isnull(@sql+Char(13)+Char(10),'')+'update t1 Set 字段'+Replace(id-1,0,'')+'='+x
From (Select id=Row_number() Over(order By id),x From t) aPrint @sql/*
update t1 Set 字段=aa
update t1 Set 字段1=bb
update t1 Set 字段2=cc
update t1 Set 字段3=dd*/