c**t
发帖数: 2744 | 1 like oracle or SQL Server 2008
MERGE INTO TblA USING TblB ON TblA.PK=TblB.PK
WHEN MATCHED THEN UPDATE ..
WHEN NOT MATCHED THEN INSERT ...
how to do it in SQL Server 2000?
Thinking to use cursor of TblB, then
UPDATE TblA set ... WHERE TblA.PK=Cursor.PK
if @@ERROR <> 0 then
INSERT INTO TblA ...
end
This worked, but seems too slow; any better way? |
|
i*****w
发帖数: 75 | 2 The following code is not optimized for performance, I just would like to
show the steps to solve the problem.
HTH
-- Data Preparation for Table A
declare @TblA table(name varchar(20), project varchar(10), score int)
insert into @TblA(name, project, score)
select 'Jack', 'A', 100
union all
select 'Jack', 'B', 50
union all
select 'Jack', 'C', 50
union all
select 'Susan', 'A' , 50
union all
select 'Susan', 'B' , 50
-- ... 阅读全帖 |
|
i*****w
发帖数: 75 | 3 建议一种方法:
1) UNPIVOT TableA, You will get:
ID1, COL1, CONTENT1
ID2, COL2, CONTENT2
ID3, COL3, CONTENT3
2) Join TableA and TableB by LIKE.
EXAMPLE:
-- Prepare Source Table
DECLARE @tblA Table (ID int identity, col1 varchar(100), col2 varchar(100),
col3 varchar(100))
INSERT INTO @tblA (col1, col2, col3)
SELECT 'this is a test', 'I am not sure', 'Give it a try.'
UNION ALL
SELECT 'who cares', 'No one knows', 'Why not'
UNION ALL
SELECT 'it is impossible', 'please let me know', 'be honest'
-- ... 阅读全帖 |
|
j*****n
发帖数: 1781 | 4 use LEFT JOIN in SQL Server:
SELECT A.email
FROM tblA LEFT JOIN tblB
ON tblA.email = tblB.email
WHERE tblB.email IS NULL |
|
|
|
|
m**********2
发帖数: 2252 | 8 我用self join,
SELECT a.*,b.*, c.* (--whatever field you want)
FROM tbla a,tblb b1,tblc c
where a.id = b1.id
and a.seqno = c.seqno
and b1.date =
(SELECT MAX (b2.date) from tblb b2
where b2.id = b1.id
group by b2.id) |
|
