从表1和表2中选择不同的值，然后在sqlserver 2008中的表1中插入它们 [英] selecting distinct value from table 1 and table 2 and then inserting those in table 1 in sqlserver 2008

问题描述

以下是我正在使用的声明。

Here is the statement which I am using.

INSERT INTO Course_Data (Branch,CC,C_Title,Sem,Credits) 
 values 
(select distinct from (select * from Course_Data) union (select * from TEMP1) a order by a.CC)

我遇到错误：

关键字'select'附近的语法不正确。

'a'附近的语法不正确。



我有一个名为Course_Data的表，我使用SqlBulkCopy从excel文件导入数据。在插入Course_Data表之前，我需要检查它插入的行是否已经在Course_Data表中。如果它存在行，那么它应该跳过该条目，如果不存在，则必须插入该行。为了实现这一点，我创建了一个临时（Temp）表，将所有行从excel插入Temp，然后从Course_Data和Temp中选择不同的值，并将不同的值插入Course_Data

I encountered with error :
Incorrect syntax near the keyword 'select'.
Incorrect syntax near 'a'.

I have a table called Course_Data to which I am importing data from excel file using SqlBulkCopy. Before it inserts into Course_Data table I need to check if the row which it is inserting is already on Course_Data table. If it row exists then it should skip that entry and if it is not then that row has to be inserted. So to achieve this I created a temporary(Temp) table, inserted all the rows from excel to Temp, then select distinct values from Course_Data and Temp, and insert distinct values to Course_Data

推荐答案

试试这个：

Try this:

insert into course_data
select branch, cc, c_title, seem, credits from temp t where not exists
(
SELECT * FROM course_data cd1 WHERE cd1.branch=t.branch and cd1.cc=t.cc and cd1.c_title=t.c_title and cd1.sem=t.sem and cd1.credits=t.credits
)

发生错误是因为从SELECT语句插入时不应指定VALUES关键字。



什么是插入选择组合和不同的值，可能类似于

The error you get happens because you should not specify VALUES keyword when inserting from a SELECT statement.

What comes to the insert select combination and the distinct values, perhaps something like

INSERT INTO Course_Data (Branch, CC, C_Title ,Sem,Credits)
SELECT Branch, CC, C_Title, Sem, Credits 
FROM TEMP1
EXCEPT 
SELECT Branch, CC, C_Title, Sem, Credits 
FROM Course_Data

请阅读我对该问题的评论。



试试这个：

PLease, read my comment to the question.

Try this:

INSERT INTO Course_Data (Branch,CC,C_Title,Sem,Credits)
SELECT DISTINCT Branch,CC,C_Title,Sem,Credits
FROM (
    SELECT Branch,CC,C_Title,Sem,Credits
    FROM Course_Data
    UNION ALL
    SELECT Branch,CC,C_Title,Sem,Credits
   FROM TEMP1
) AS A
ORDER BY A.CC)

详情请见：INSERT（SQL） [ ^ ]

这篇关于从表1和表2中选择不同的值，然后在sqlserver 2008中的表1中插入它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！