selecting distinct value from table 1 and table 2 and then inserting those in table 1 in sqlserver 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Here is the statement which I am using.

C#

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)

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

Posted 1-Mar-15 2:03am

partha143

Add a Solution

Comments

Maciej Los 1-Mar-15 9:15am

Does Course_data and TEMP1 table contain the same set of columns with the same data type?

partha143 1-Mar-15 9:16am

Yes. They both have same table definations

3 solutions

Solution 1

Try this:

SQL

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
)

Posted 1-Mar-15 3:18am

Peter Leow

Comments

Maciej Los 1-Mar-15 9:24am

Looks promissing ;)

partha143 1-Mar-15 10:03am

I tried your solution peter, but I am still unable to avoid duplication.

Solution 2

PLease, read my comment to the question.

Try this:

SQL

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)

For further information, please see: INSERT (SQL)[^]

Posted 1-Mar-15 3:19am

Maciej Los

Comments

partha143 1-Mar-15 9:50am

I tried your code and I am still unable to avoid insertion of duplicate values.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-03-01T18:27:00

Solution 3

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

SQL

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

Posted 1-Mar-15 18:27pm

Wendelius

Comments

partha143 3-Mar-15 10:36am

Thanks a lot Mika :)

Wendelius 3-Mar-15 12:25pm

You're welcome :)