使用查询分析器，将a表的userid字段里面的内容用","拆分出了后插入到b表里面，并且bid对应的是aida表
aid astr
1 1,t,4
2 5,6
3 3,p,zb表
bid bstr插入b表后结果如下：
bid bstr
1 1
1 t
1 4
2 5
2 6
3 3
3 p
3 z命令在查询分析器里完成
aid astr
1 1,t,4
2 5,6
3 3,p,zb表
bid bstr插入b表后结果如下：
bid bstr
1 1
1 t
1 4
2 5
2 6
3 3
3 p
3 z命令在查询分析器里完成
b
select
*
from
(
Select
a.aid,a.[bstr]=substring(a.[bstr],b.number,charindex(',',a.[bstr]+',',b.number)-b.number)
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[userid])
where
substring(','+a.[bstr],b.number,1)=',')t
b
select
*
from
(
Select
a.aid,a.[bstr]=substring(a.[bstr],b.number,charindex(',',a.[bstr]+',',b.number)-b.number)
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[bstr])
where
substring(','+a.[bstr],b.number,1)=',')t
b
select
*
from
(
Select
a.aid,a.[astr]=substring(a.[astr],b.number,charindex(',',a.[astr]+',',b.number)-b.number)
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[astr])
where
substring(','+a.[astr],b.number,1)=',')t
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-04-28 12:05:22
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([aid] int,[astr] varchar(5))
insert [a]
select 1,'1,t,4' union all
select 2,'5,6' union all
select 3,'3,p,z'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([bid] sql_variant,[bstr] sql_variant)
--------------开始查询--------------------------
insert into
b
select
*
from
(
Select
a.aid,[astr]=substring(a.[astr],b.number,charindex(',',a.[astr]+',',b.number)-b.number)
from
a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[astr])
where
substring(','+a.[astr],b.number,1)=',')t
select * from b
----------------结果----------------------------
/* bid bstr
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1
1 t
1 4
2 5
2 6
3 3
3 p
3 z(8 行受影响)
*/
CREATE FUNCTION [dbo].[func_split]
(
@SourceSql VARCHAR (MAX),
@StrSeprate VARCHAR (10)
)
RETURNS @temp TABLE
(
[F1] VARCHAR (100) NULL
)
WITH
EXECUTE AS CALLER
AS
BEGIN
DECLARE @i INT
SET @SourceSql = rtrim (ltrim (@SourceSql))
SET @i = charindex (@StrSeprate, @SourceSql) WHILE @i >= 1
BEGIN
IF len (left (@SourceSql, @i - 1)) > 0
BEGIN
INSERT @temp
VALUES (left (@SourceSql, @i - 1))
END
SET @SourceSql = substring (@SourceSql, @i + 1, len (@SourceSql) - @i)
SET @i = charindex (@StrSeprate, @SourceSql)
END IF @SourceSql <> ''
INSERT @temp
VALUES (@SourceSql)
RETURN
END--============================================================================================
--2用游标调用刚才创建function:[dbo].[func_split]()DECLARE @bid INT;
DECLARE @bstr NVARCHAR(255);DECLARE CurB CURSOR FOR SELECT aid,astr FROM 表aOPEN CurBFETCH NEXT FROM CurB INTO @bid,@bstr
WHILE @@FETCH_STATUS=0BEGIN INSERT INTO 表b
(
bid,
bstr
)
SELECT @bid,F1 FROM [dbo].[func_split](@bstr,',')
FETCH NEXT FROM CurB INTO @bid,@bstr
END
CLOSE CurB
DEALLOCATE CurB
没解决，a和b表是在不同数据库中的，表结构也不同，a表里面已经有上万条数据了
数据库.dbo.a
数据库.dbo.b