• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

T-SQL

Hello All,

This should be simple join but I think I am making some simple mistake which I can not figure out.

The thing is I have to insert the records in the same table for ID = 1 where ID = 0. As you can see in the example the PK columsn are ID and Code. There are already 2 records for ID = 1 but I need to insert the remaining 4 records for ID = 1 where ID = 0 (code = 3,4,5,6)

Can somebody please help me?

Thanks much


create table #b
(
	ID INT,
	Code INT,
	name varchar(10)

)
insert into #b values (0, 1, 'aaa')
insert into #b values (0, 2, 'bbb')
insert into #b values (0, 3, 'ccc')
insert into #b values (0, 4, 'ddd')
insert into #b values (0, 5, 'eee')
insert into #b values (0, 6, 'fff')
insert into #b values (1, 1, 'aaa')
insert into #b values (1, 2, 'bbb')
insert into #b values (3, 2, 'bbb')
insert into #b values (3, 6, 'fff')

Open in new window

0
BrookK
Asked:
BrookK
  • 4
  • 3
1 Solution
 
devlab2012Commented:
insert into #b(ID, Code, Name) select 1, Code, Name From #b where ID=0 AND Code NOT IN (SELECT Code from #b where ID = 1)
0
 
Kevin CrossChief Technology OfficerCommented:
Not sure I understand the issue, you should be able to just INSERT INTO the table as long as the ID and Code combination is unique.  Are you trying to duplicate the rows for say ID = 0 across the other ID values?
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, I didn't see devlab2012's post.  I suspect that is what you are after.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
BrookKAuthor Commented:
Yes, I have to duplicate the rows for ID = 1
0
 
BrookKAuthor Commented:
Thanks the reply devlab2012.

How do I do if there are more than 2 columns a PK. Do I mention all of them the NOT IN caluse separately?
0
 
Kevin CrossChief Technology OfficerCommented:
http:#a35700648 should do the trick.
0
 
Kevin CrossChief Technology OfficerCommented:
I would use a NOT EXISTS.

insert into #b(ID, Code, Name)
select 1, Code, Name
From #b tfr
where ID=0
and not exists (
   select 1
   from #b lkp
   where lkp.ID = 1
   and lkp.Code = tfr.Code
   -- add other columns here if needed
)
0
 
BrookKAuthor Commented:
Worked great!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now