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

T-SQL to perform a specialized INSERT INTO

I'm not too familiar with T-SQL and was wondering if I could get some help with an INSERT INTO statement..  

I have two tables, tblAssets and tblADComputers.  I am writing a job that creates entries in tblAssets for every computer in tblADComputers where my FK in tblAssets is set to tblADComputers.objectGUID

I understand you can do an INSERT INTO tblAssets (<fields>) SELECT <fields> FROM tblADComputers but there are a couple required fields for the new tblAssets entries that I need to set in the statement as well..  So I was hoping to make it look something like

INSERT INTO tblAssets (field1,field2) (field1value, (SELECT <field2> FROM tblADComputers))

Is this feasible?

Additionally, I don't want it to attempt to create duplicate entries, so if the objectGUID FK already exists in tblAssets, I don't want it to attempt to reinsert that objectGUID entry from tblADComputers.. I assume this would include some sort of WHERE NOT EXISTS clause but Im not sure how to put it together.  Any help with this would be greatly appreciated.
0
CantonIT
Asked:
CantonIT
2 Solutions
 
dportasCommented:
INSERT INTO tblAssets (field1, field2)
SELECT 'field1value', C.field2
 FROM tblADComputers C
 LEFT JOIN tblAssets A
 ON C.field2 = A.field2
 AND A.field1 = 'field1value'
 WHERE A.field1 IS NULL ;
0
 
CantonITAuthor Commented:
Didn't work..  However the following SQL snippet did.
INSERT INTO tblAssets 
     (assetModelID, ADLinked, ADObjectGUID, assetName, networkName)
SELECT 
     'bfae1bee-3ffd-4c59-8b4d-f2055b2dc4a2', 'true',  tblADComputers.objectGUID, 
     tblADComputers.dnsHostName, tblADComputers.dnsHostName
FROM tblADComputers
WHERE NOT EXISTS (
     SELECT 
          tblAssets.ADObjectGUID 
          FROM tblAssets 
          WHERE tblADComputers.objectGUID = tblAssets.ADObjectGUID
);
 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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