Bulk entry - purchase date, warrenty date and custom field 1 entry? SQL Help?

[djolly]

We have a load of new laptops with names "English-01, English-02, English-03 etc.etc.

They all have the same purchase date, warranty and cost (which we are using custom field 1 for).

Rather than manually enter this information on each laptop, is there a way to run an SQL query in the Lansweeper configuration app?

Any help greatly appreciated!

[Hemoco]

This may be possible to do, but we do not have the code for such a query.

[djolly]

Bump, could do with some help on this please

[djolly]

Nearly there! Just need help with one more. I am not sure if this is possible syntacticly speaking but here goes.

I have many bar code entries I need to input, it is a four digit number that increments by 1 each time. e.g.

UPDATE tblCompCustom
SET BarCode= '3001'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'R21-STN01')

Then the next one ...

UPDATE tblCompCustom
SET BarCode= '3002'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'R21-STN02')

Etc. Is there a way of entering this all in one go in one SQL statement?

[djolly]

That worked perfectly. Thank you.

I was doing this the long way by entering some dummie information in the Custom field1 then running the previous query, but this way saves me a lot of time. Thanks again

[Hemoco]

This may happen if those specific computers have no value for any of the fields in tblCompCustom. Could you try the script below instead. It forces a value into tblCompCustom, so the Custom1 field can be updated.

INSERT INTO tblCompCustom (Computername)
SELECT Computername
FROM tblComputers
WHERE (Computername NOT IN (SELECT Computername FROM tblCompCustom AS tblCompCustom_1))
GO
UPDATE tblCompCustom
SET Custom1 = '£259.00'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'R21-STN%')

[djolly]

That worked great for the English laptops but I am now trying this on another name and having an odd problem.

The SQL statment says "The command completed successfully" but when I check the entry in the browser the field is still blank.

UPDATE tblCompCustom
SET Custom1 = '£259.00'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'R21-STN%')

Basically we have a numbre of machines called R21-STN01, R21-STN02 etc.etc.


Am I missing something?

[Hemoco]

This works the same way. Simply replace "Custom1" in the code.

Change purchase date for computers whose name starts with "English":

UPDATE tblCompCustom
SET PurchaseDate = 'YourInfo'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'English%')

Change warranty date for computers whose name starts with "English":

UPDATE tblCompCustom
SET WarrantyDate = 'YourInfo'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'English%')

[djolly]

Thank you for your reply. I will give that a try.

Is there any way of doing something like this with purchase date and warranty date?

[Hemoco]

An example can be found below. Execute this SQL script in your Lansweeper configuration console under Lansweeper\Database Scripts to update the "Custom1" field for all computers whose name starts with "English". Replace "YouInfo" with the information you want to put in the field.

UPDATE tblCompCustom
SET Custom1 = 'YourInfo'
FROM tblCompCustom INNER JOIN
tblComputers ON tblCompCustom.Computername = tblComputers.Computername
WHERE (tblComputers.Computer LIKE 'English%')