表记录更新和插入 [英] Table records updates and inserting

问题描述

大家好，

你能帮我这个忙吗?
在侧面SP中，
我有一个表T1，其中有许多列和许多记录，从此数据插入到花药表T2中，同时又插入到表T2中，我需要检查ID是否存在，如果存在，则用许多记录进行更新或插入数据.
请帮助我.
高级谢谢.
问候，
Narasimha

Hi All,

Can you please help me on this anybody.
In side SP,
I have one Table T1 have many columns and many records, from this data insert into anther table T2 while inserting into Table T2 I need to check ID is exist or not if its exist do the updating with many records or do inserting data.
Please help me on this.
Advanced thank you.
Regards,
Narasimha

推荐答案

这里是示例方法

我创建了2个临时表#SampleTable1"和#SampleTable2"，并将一些数据插入其中，如下所示

Here is a sample approach

I have created 2 temp tables "#SampleTable1" and "#SampleTable2" and inserted some data into it as shown below

CREATE TABLE #SampleTable1
(
	ID INT,
	[Name] VARCHAR(100)
)

CREATE TABLE #SampleTable2
(
	ID INT,
	[EmpName] VARCHAR(100)
)

INSERT INTO #SampleTable1
SELECT 1, 'James' UNION ALL
SELECT 2, 'Scott' UNION ALL
SELECT 3, 'Rob' UNION ALL
SELECT 4, 'Tom' 


INSERT INTO #SampleTable2
SELECT 1, 'James s' UNION ALL
SELECT 2, 'Tom s' 

接下来，我将基于ID列，使用示例表1中的名称更新示例表2中的EmpName列.

Next I will update the EmpName column in Sample table 2 with the names in sample table 1 based on the ID column.

--Update Existing Records in Sample Table 2
UPDATE ST2
SET ST2.[EmpName] = ST1.[Name]
FROM #SampleTable1 ST1
INNER JOIN #SampleTable2 ST2 ON ST1.ID = ST2.ID

接下来从样本表1插入样本表2中不存在的记录，如下所示

Next insert records that don''t exist in sample table 2 from sample table 1 as shown below

--Insert New Records to Sample Table 2
INSERT #SampleTable2
SELECT ST1.ID, ST1.[Name]
FROM #SampleTable1 ST1
LEFT JOIN #SampleTable2 ST2 ON ST1.ID = ST2.ID
WHERE ST2.ID IS NULL

这篇关于表记录更新和插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！