TA貢獻1883條經驗 獲得超3個贊
一、可以用游標，一條條insert,每次兩個表。
二、關聯表更新，增加一個源表主鍵Xid
use testdb2
go
/*******************建立測試數據***[email protected]***********************/
IF NOT OBJECT_ID('[TBXXX]') IS NULL
DROP TABLE [TBXXX]
GO
CREATE TABLE [TBXXX](
[Xid] int identity(1,1) primary key,
[A1] NVARCHAR(20) null,
[A2] NVARCHAR(20) null,
[B1] Nvarchar(10) null,
[B2] NVARCHAR(20) null
)
go
INSERT INTO TBXXX
SELECT 'a1',replicate('A1',6),replicate('B1',4),replicate('B1',5) union ALL
SELECT 'a2',replicate('A2',6),replicate('B2',4),replicate('B2',5) union ALL
SELECT 'a3',replicate('A3',6),replicate('B3',4),replicate('B3',5) union ALL
SELECT 'a4',replicate('A4',6),replicate('B4',4),replicate('B4',5)
go
IF NOT OBJECT_ID('[tbA]') IS NULL
DROP TABLE [tbA]
GO
CREATE TABLE [tbA](
[Aid] int identity(1,1) primary key,
[A1] NVARCHAR(20) null,
[A2] NVARCHAR(20) null,
[Xid] int null
)
go
IF NOT OBJECT_ID('[tbB]') IS NULL
DROP TABLE [tbB]
GO
CREATE TABLE [tbB](
[Bid] int identity(1,1) primary key,
[B1] Nvarchar(10) null,
[B2] NVARCHAR(20) null,
[Aid] int null,
[Xid] int null
)
go
insert into [tbA] select A1, A2,XId from [TBXXX]
insert into [tbB] (Aid,Xid) select AID,Xid from [tbA]
----select * FROM [tbB]
update [tbB] SET B1=t.B1,B2=t.B2
FROM [TBXXX] t INNER JOIN [tbB] on t.Xid=[tbB].Xid
ALTER table [tbA]
drop column Xid
GO
ALTER table [tbB]
drop column Xid
GO
SELECT * FROM [tbA]
select * FROM [tbB]
舉報