Need help with Insert Into Select Distinct

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with Insert Into Select Distinct

Author

Topic

TurboConn
Starting Member

4 Posts

Posted - 2012-03-20 : 19:32:29

Hi -
Very new to SQL, and using MS SQL Server Studio.

Here's what I'm trying to do:
Look in a simple two-column table (x,y). There are multiple x values with varying y values, but never a duplicate of x and y.
For any value x that has a y value of 18, I want to insert a duplicate x with a y value of 27.

For example, if my table has:
X Y
A 5
B 12
B 18
C 11

Then in the end, I want:
X Y
A 5
B 12
B 18
B 27
C 11

However, I'm getting Violation of PRIMARY KEY constraint, Cannot insert duplicate key in object.

Here's my statement. If I comment out the insert into, I get my distinct list of 18s, that I want to duplicate with 27s:

insert into [Database].[Table].[Name]
select distinct a.XColumn, a.YColumn
from Table.Name a, Table.Name b
where a.YColumn = 18
and b.XColumn not in (select XColumn from Table.Name where YColumn = 27)
and a.XColumn = b.XColumn

It has to be easy... Any ideas?

Thanks,
Randy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 20:02:12

[code]
insert into [Database].[Table].[Name]
select distinct a.XColumn, a.YColumn
from Table.Name a, Table.Name b
where a.YColumn = 18
and b.XColumn not in (select XColumn from Table.Name where YColumn = 27)
and a.XColumn = b.XColumn

union all

select distinct a.XColumn, 27
from Table.Name a, Table.Name b
where a.YColumn = 18
and b.XColumn not in (select XColumn from Table.Name where YColumn = 27)
and a.XColumn = b.XColumn
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TurboConn
Starting Member

4 Posts

Posted - 2012-03-20 : 20:20:14

[quote]Originally posted by visakh16


union all

Ahh, so I am trying to insert an X but without a Y, so it doesn't know what to put there?

Thanks! I'm still getting the same error, but I think I can get there from here.

Thanks Again,
Randy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 20:36:44

hmm...didnt get you. can you elaborate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TurboConn
Starting Member

4 Posts

Posted - 2012-03-20 : 20:51:25

quote:
Originally posted by visakh16

hmm...didnt get you. can you elaborate?

(BTW, I really appreciate the help you're offering me)

Sorry. I thought that I understood what you were doing to fix my SQL statement with the Union All. When I looked up a Union All, it looked like you were combining two statements: The first to cover the X column, the second to cover the Y column. I guess not now?

I understand an Insert Into needs columns and values.
I know my columns are "XColumn" and "YColumn".
I know the list of XColumn values that I want to Insert Into the table. These can be found by running:

     select distinct a.XColumn, a.YColumn
     from Table.Name a, Table.Name b
     where a.YColumn = 18
     and b.XColumn not in (select XColumn from Table.Name where YColumn = 27)
     and a.XColumn = b.XColumn

I know the list of YColumn values that I want to Insert Into the table: 27.

What I still can't do, for whatever reason, is insert 27s where there are already 18s. Maybe my approach is completely wrong?

Thanks,
Randy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 20:56:09

first run and see this select


select distinct a.XColumn, a.YColumn
from Table.Name a, Table.Name b
where a.YColumn = 18
and b.XColumn not in (select XColumn from Table.Name where YColumn = 27)
and a.XColumn = b.XColumn

union all


select distinct a.XColumn, 27
from Table.Name a, Table.Name b
where a.YColumn = 18
and b.XColumn not in (select XColumn from Table.Name where YColumn = 27)
and a.XColumn = b.XColumn

this will clone all records in table having YColumn value 18 to YColumn value 27 (for whom its not already present and merge them with original list. so the complete will give all records having YColumn 18 and a copy with YColumn 27. I hope thats what you asked for

If not please post required information in below format for us to understand clearly what you're after

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TurboConn
Starting Member

4 Posts

Posted - 2012-03-20 : 21:12:05

I ran your Select Distinct, and it's exactly what I wanted to see in the final table. Given this, I figured out what I needed to do: Change my a.YColumn to a 27. This exact statement solved my problem:

insert into [Database].[Table].[Name]
select distinct a.XColumn, 27
from Table.Name a, Table.Name b
where a.YColumn = 18
and b.XColumn not in (select XColumn from Table.Name where YColumn = 27)
and a.XColumn = b.XColumn

I now have new copies of my XColumn values that had YValues of 18. The new ones have values of 27.

Thanks Again, and Best Regards,
Randy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources