将记录从一个表插入到sql server中的另一个表 [英] insert record from one table to another table in sql server

问题描述

我有两个表，我想将记录从一个表插入到另一个表中，如果记录存在于同一个id中，则更新它，否则插入新记录，而不使用curser或loops.Please为我提供这个问题的简单解决方案。 sould支持sqlserver 2005/2008

I have two tables ,i want to insert record from one table to another table ,if record exists in same id them update it otherwise insert new record, without using curser or loops.Please provide me simple solution for this problem.code sould support both sqlserver 2005/2008

推荐答案

在这两种情况下都可以使用EXISTS [ ^ ]语句，指定要测试行是否存在的子查询。



插入方案：

In both cases there is possoble to use EXISTS[^] statement, which specifies a subquery to test for the existence of rows.

Insert scenario:

INSERT INTO TableName (Field1, Field2, ..., FieldN)
SELECT Field1, Field2, ..., FieldN
FROM TableName AS t1
WHERE NOT EXISTS (
    SELECT Field1, Field2, ..., FieldN
    FROM OtherTableName AS t2 
    WHERE t1.ID = t2.ID
    )

使用 IN子句可以实现同样的目标[ ^ ]。



更新方案：

The same is possible to achieve using IN clause[^].

Update scenario:

UPDATE t1 
    SET t1.Field1 = t2.Field1,
        t1.Field2 = t2.Field2
FROM TableName AS t1 INNER JOIN OtherTableName AS t2 ON t1.ID = t2.ID

这是一般概念......根据需要改变它。

This is a general conception... Change it to your needs.

如果我理解，你想保留第二张桌子作为第一张的副本。你为什么不做这样的事情，即把第二张桌子弄空，然后从第一张桌子填满。

If I understand, you want to keep the 2nd table as copy of 1st. Why don't you do something like this i.e. make the 2nd table empty and then fill it from 1st table.

DELETE FROM Table2

INSERT Table2
    SELECT * FROM Table1

请注意，这不是大数据的最佳解决方案。如果您正在尝试实施某种备份过程，还有其他技术可以做到这一点。

Please note that this is not the best solution for huge data. If you are trying to implement some kind of backup procedure, there are other techniques to do that.

这篇关于将记录从一个表插入到sql server中的另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！