December 30, 2009 at 2:56 pm
Guy's,
I have 3 tables
Computers Table
SerialNumber Vchar(25) PK
NodeName Vchar(25)
Software Table
SoftwareID Int PK
Software Name Vchar(50)
CompSoft Table
SerialNumber Vchar(25) PK
SoftwareID Int PK
What I need to do is get 4 SoftwareID's and add them to every computer in the database if it does not already exist and I am using the Compsoft table to do that.
December 30, 2009 at 3:10 pm
What have you tried so far?
Also, please read and follow the recommendation in the first link in my signature on how to post sample data.
What would you insert into the nodeName column (assuming your talking about [Computers Table] when you write "every computer in the database" ...)
December 30, 2009 at 7:31 pm
If I understand your problem correctly, You have all computers in computers table, all software in software table and the compsoft table will have the combination of the two. I believe you want to join the computers table and the software table and insert them into the compsoft table, if they currently do not exist in the compsoft table. If that is the case here is your query.
Insert into Compsoft
(SerialNumber,SoftwareID)
select C.SerialNumber,S.SoftwareID
from dbo.Software S cross join dbo.Computers C
where not exists (select 1 from Compsoft CS where CS.SoftwareID = S.SoftwareID and CS.SerialNumber = C.SerialNumber)
To explain, you need a cross join(cartesian product) on the two tables, then you exclude the records from the compsoft table using the not exists select.
hope this is what you are looking for.
December 30, 2009 at 9:04 pm
--===== Create the test table with CREATE TABLE #software
(
SoftwareID INT IDENTITY(1,1) PRIMARY KEY --Is an IDENTITY column on real table SoftwareName VarCHAR(50)
)
--===== Create the test table with CREATE TABLE #custsoft
--This is a junction table to complete my many-to-many relationship
(
SoftwareID INT IDENTITY(1,1) PRIMARY KEY --Is an IDENTITY column on real table SerialNumber VarChar(25) IDENTITY(1,1) PRIMARY KEY --Is an IDENTITY column on real table
)
--===== Create the test table with CREATE TABLE #computers
(
SerialNumber VarChar(25) IDENTITY(1,1) PRIMARY KEY --Is an IDENTITY column on real table NodeName VarCHAR(25)
)
What I need to do is
INSERT INTO custsoft
Values(26, 56, 67, 87) From software
for every serialnumber in the computer table that is not already in the custsoft table.
December 31, 2009 at 9:10 am
I am not sure why you are doing a join on the software table if you already know the four values you want to insert, however, the query I gave you last night will work if you qualify the software id, see below.
Insert into Compsoft
(SerialNumber,SoftwareID)
select C.SerialNumber,S.SoftwareID
from dbo.Software S cross join dbo.Computers C
where not exists (select 1 from Compsoft CS where CS.SoftwareID = S.SoftwareID and CS.SerialNumber = C.SerialNumber)
and S.SoftwareID in(26, 56, 67, 87)
January 5, 2010 at 6:28 am
I am not sure why you are doing a join on the software table if you already know the four values you want to insert, however, the query I gave you last night will work if you qualify the software id, see below.
Insert into Compsoft
(SerialNumber,SoftwareID)
select C.SerialNumber,S.SoftwareID
from dbo.Software S cross join dbo.Computers C
where not exists (select 1 from Compsoft CS where CS.SoftwareID = S.SoftwareID and CS.SerialNumber = C.SerialNumber)
and S.SoftwareID in(26, 56, 67, 87)
Hey Thanks jcdyntek,
This works great and is exactly what I needed.:-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply