create table ta (id int , amt numeric(18,6)) insert ta values (998, 3323.80) insert ta values (999, 508.74) insert ta values (1000, 122.00)create table tb (dp_id int ,amt numeric(18,6)) insert tb values(2231,13002.00) insert tb values(2231,9928.00) insert tb values(2231,2439.26) insert tb values(2231,9502.22) insert tb values(2231,9951.14) update tb set dp_id = select max(id) + 1 from ta --(只能得到一条)--要实现如下效果,这样后可以将TB插入到TA下面 dp_id amt 1001 13002.000000 1002 9928.000000 1003 2439.260000 1004 9502.220000 1005 9951.140000
先给一个SQL2008的 -- create table ta (id int , amt numeric(18,6)) insert ta values (998, 3323.80) insert ta values (999, 508.74) insert ta values (1000, 122.00)
DECLARE @TempMax INT SELECT @TempMAX = id FROM dbo.taUPDATE A SET A.dp_id = NumberIndex FROM dbo.tb AS A INNER JOIN ( SELECT (ROW_NUMBER() OVER(ORDER BY KeyID) + @TempMAX) AS NumberIndex, KeyID FROM dbo.tb ) AS B ON A.KeyID = B.KeyID SELECT * FROM dbo.tb
SQL2000的话呢,就把tb这个表再插入一个临时表,形如 SELECT IDENTITY(INT, 1, 1) AS KeyID, * INTO #Temp FROM dbo.tb之后再按SQL008的思路就可能了
create table ta (id int , amt numeric(18,6))
insert ta values (998, 3323.80)
insert ta values (999, 508.74)
insert ta values (1000, 122.00)create table tb (dp_id int ,amt numeric(18,6))
insert tb values(2231,13002.00)
insert tb values(2231,9928.00)
insert tb values(2231,2439.26)
insert tb values(2231,9502.22)
insert tb values(2231,9951.14)
update tb set dp_id = select max(id) + 1 from ta --(只能得到一条)--要实现如下效果,这样后可以将TB插入到TA下面
dp_id amt
1001 13002.000000
1002 9928.000000
1003 2439.260000
1004 9502.220000
1005 9951.140000
--
create table ta (id int , amt numeric(18,6))
insert ta values (998, 3323.80)
insert ta values (999, 508.74)
insert ta values (1000, 122.00)
create table tb (KeyID INT IDENTITY(1, 1), dp_id int ,amt numeric(18,6))
insert tb values(2231,13002.00)
insert tb values(2231,9928.00)
insert tb values(2231,2439.26)
insert tb values(2231,9502.22)
insert tb values(2231,9951.14)DROP TABLE tb
DECLARE @TempMax INT
SELECT @TempMAX = id FROM dbo.taUPDATE A SET A.dp_id = NumberIndex
FROM dbo.tb AS A
INNER JOIN
(
SELECT (ROW_NUMBER() OVER(ORDER BY KeyID) + @TempMAX) AS NumberIndex, KeyID
FROM dbo.tb
) AS B ON A.KeyID = B.KeyID
SELECT *
FROM dbo.tb
SELECT IDENTITY(INT, 1, 1) AS KeyID, *
INTO #Temp
FROM dbo.tb之后再按SQL008的思路就可能了