mobing data-posting again

I hate to post twice but I never received a response from 2 days ago.
I am capturing system information about computers. Currently I have one
table named SystemInfo that gets data inserted by DTS everyday. Before
running DTS job, I would truncate table and insert all new data. Since there
are times I miss pulling data for machines, I end up truncating data and
then losing that information unitl maybe the next day. I am trying to keep
my system info with the latest data even when I do not poll.
Steps I took:
Kept SystemInfo table and created a StagingSystemInfo table that the DTS job
inserts all data. Both tables are identical as in structure. Instead of
truncating SystemInfo, I need to know how to check my staging table and if
the computername exists drop computername from SystemInfo and insert data
and them move onto the next node. I just don't know how to do the check. I
know I can use the insert into command.
For example,
If computername exists in StagingSystemInfo table
Then drop that computername from SystemInfo and insert data.
Keep running unil I have reached the last computername.
In the end I should have my SystemInfo table with current data and if I did
not get data for a particular machine in staging table I will still have in
SystemInfo table.
At the end drop table."Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eVYvxHxVFHA.3320@.TK2MSFTNGP12.phx.gbl...
>I hate to post twice but I never received a response from 2 days ago.
>
> I am capturing system information about computers. Currently I have one
> table named SystemInfo that gets data inserted by DTS everyday. Before
> running DTS job, I would truncate table and insert all new data. Since
> there
> are times I miss pulling data for machines, I end up truncating data and
> then losing that information unitl maybe the next day. I am trying to keep
> my system info with the latest data even when I do not poll.
> Steps I took:
> Kept SystemInfo table and created a StagingSystemInfo table that the DTS
> job
> inserts all data. Both tables are identical as in structure. Instead of
> truncating SystemInfo, I need to know how to check my staging table and if
> the computername exists drop computername from SystemInfo and insert data
> and them move onto the next node. I just don't know how to do the check. I
> know I can use the insert into command.
>
> For example,
> If computername exists in StagingSystemInfo table
> Then drop that computername from SystemInfo and insert data.
> Keep running unil I have reached the last computername.
> In the end I should have my SystemInfo table with current data and if I
> did
> not get data for a particular machine in staging table I will still have
> in
> SystemInfo table.
> At the end drop table.
Why not simply perform an update and an insert? You didn't provide any
information on the structure of the tables involved (DDL), so all I can
offer is pseudocode:
UPDATE SystemInfo
SET
Column1 = (SELECT Column1 FROM StagingSystemInfo WHERE
SystemInfo.computername = StagingSystemInfo.computername),
Column2 = (SELECT Column2 FROM StagingSystemInfo WHERE
SystemInfo.computername = StagingSystemInfo.computername),
.
.
.
ColumnN = (SELECT ColumnN FROM StagingSystemInfo WHERE
SystemInfo.computername = StagingSystemInfo.computername)
WHERE EXISTS (SELECT * FROM StagingSystemInfo WHERE SystemInfo.computername
= StagingSystemInfo.computername)
INSERT INTO SystemInfo (computername, Column1, Column2, ... ,ColumnN)
SELECT computername, Column1, Column2, ... ,ColumnN
FROM StagingSystemInfo
WHERE NOT EXISTS (SELECT * FROM SystemInfo WHERE SystemInfo.computername =
StagingSystemInfo.computername)|||Below is the structure of my database. I included my Create Table statement.
As you can see they are the same
SystemInfo - Where I want to store all the most updated data.
CREATE TABLE [dbo].[SystemInfo] (
[IBDI_IBDIVersion] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Win32_BIOS_Name] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Win32_BIOS_SMBIOSBIOSVersion] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_ComputerSystem_Model] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_ComputerSystem_TotalPhysicalMemor
y] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_ComputerSystem_TimeZone] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_DiskDrive_Model] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_DiskDrive_FirmWare] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_LogicalDisk_FreeSpace] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_LogicalDisk_Size] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_POTSModem_Description] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LTSPOS_ModemFlashDate] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_Processor_CurrentClockSpeed] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemName] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
Next is the StagingSystemInfo where I load the data and want to update the
SystemInfo.
CREATE TABLE [dbo].[StagingSystemInfo] (
[IBDI_IBDIVersion] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Win32_BIOS_Name] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Win32_BIOS_SMBIOSBIOSVersion] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_ComputerSystem_Model] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_ComputerSystem_TotalPhysicalMemor
y] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_ComputerSystem_TimeZone] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_DiskDrive_Model] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_DiskDrive_FirmWare] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_LogicalDisk_FreeSpace] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_LogicalDisk_Size] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_POTSModem_Description] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LTSPOS_ModemFlashDate] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32_Processor_CurrentClockSpeed] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemName] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
"Chris Hohmann" <nospam@.thankyou.com> wrote in message
news:eAN59nxVFHA.1148@.tk2msftngp13.phx.gbl...
> "Big D" <BigDaddy@.newsgroup.nospam> wrote in message
> news:eVYvxHxVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Why not simply perform an update and an insert? You didn't provide any
> information on the structure of the tables involved (DDL), so all I can
> offer is pseudocode:
> UPDATE SystemInfo
> SET
> Column1 = (SELECT Column1 FROM StagingSystemInfo WHERE
> SystemInfo.computername = StagingSystemInfo.computername),
> Column2 = (SELECT Column2 FROM StagingSystemInfo WHERE
> SystemInfo.computername = StagingSystemInfo.computername),
> .
> .
> .
> ColumnN = (SELECT ColumnN FROM StagingSystemInfo WHERE
> SystemInfo.computername = StagingSystemInfo.computername)
> WHERE EXISTS (SELECT * FROM StagingSystemInfo WHERE
> SystemInfo.computername = StagingSystemInfo.computername)
> INSERT INTO SystemInfo (computername, Column1, Column2, ... ,ColumnN)
> SELECT computername, Column1, Column2, ... ,ColumnN
> FROM StagingSystemInfo
> WHERE NOT EXISTS (SELECT * FROM SystemInfo WHERE SystemInfo.computername =
> StagingSystemInfo.computername)
>
>
>|||"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:%23m1a8V0VFHA.2128@.TK2MSFTNGP15.phx.gbl...
> "Chris Hohmann" <nospam@.thankyou.com> wrote in message
> news:eAN59nxVFHA.1148@.tk2msftngp13.phx.gbl...
> Below is the structure of my database. I included my Create Table
> statement. As you can see they are the same
> SystemInfo - Where I want to store all the most updated data.
> CREATE TABLE [dbo].[SystemInfo] (
> [IBDI_IBDIVersion] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Win32_BIOS_Name] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Win32_BIOS_SMBIOSBIOSVersion] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_ComputerSystem_Model] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_ComputerSystem_TotalPhysicalMemor
y] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_ComputerSystem_TimeZone] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_DiskDrive_Model] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_DiskDrive_FirmWare] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_LogicalDisk_FreeSpace] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_LogicalDisk_Size] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_POTSModem_Description] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LTSPOS_ModemFlashDate] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_Processor_CurrentClockSpeed] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [SystemName] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>
> Next is the StagingSystemInfo where I load the data and want to update the
> SystemInfo.
> CREATE TABLE [dbo].[StagingSystemInfo] (
> [IBDI_IBDIVersion] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Win32_BIOS_Name] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Win32_BIOS_SMBIOSBIOSVersion] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_ComputerSystem_Model] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_ComputerSystem_TotalPhysicalMemor
y] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_ComputerSystem_TimeZone] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_DiskDrive_Model] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_DiskDrive_FirmWare] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_LogicalDisk_FreeSpace] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_LogicalDisk_Size] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_POTSModem_Description] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LTSPOS_ModemFlashDate] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Win32_Processor_CurrentClockSpeed] [nvarchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [SystemName] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
Questions:
1. Which one of these columns is the computername column you identified in
your original post?
2. Is there a primary key on either of these tables?
3. Do all of these columns need to be NVARCHAR?
4. Did you try to apply the pseudocode I provided?
5. Can you post your replies either inline or below the quoted text?
Otherwise, it makes the flow of this thread difficult to follow.|||The computername is the SystemName.
The SystemName is the Primary Key.
No all the columns do not have to be varchar.
"Chris Hohmann" <nospam@.thankyou.com> wrote in message
news:OBtEtICWFHA.1200@.TK2MSFTNGP14.phx.gbl...
> "Big D" <BigDaddy@.newsgroup.nospam> wrote in message
> news:%23m1a8V0VFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Questions:
> 1. Which one of these columns is the computername column you identified in
> your original post?
> 2. Is there a primary key on either of these tables?
> 3. Do all of these columns need to be NVARCHAR?
> 4. Did you try to apply the pseudocode I provided?
> 5. Can you post your replies either inline or below the quoted text?
> Otherwise, it makes the flow of this thread difficult to follow.
>|||"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:OYLxXTCWFHA.2540@.tk2msftngp13.phx.gbl...
> "Chris Hohmann" <nospam@.thankyou.com> wrote in message
> news:OBtEtICWFHA.1200@.TK2MSFTNGP14.phx.gbl...
> The computername is the SystemName.
> The SystemName is the Primary Key.
> No all the columns do not have to be varchar.
>
What about questions 4 and 5? Did you try the pseudocode? Can you please
post your replies either inline or below the quoted text? Here's the results
of cutting and pasting from the DDL you provided into the pseudocode.
UPDATE SystemInfo
SET
IBDI_IBDIVersion = (SELECT IBDI_IBDIVersion FROM StagingSystemInfo WHERE
SystemInfo.SystemName = StagingSystemInfo.SystemName),
Win32_BIOS_Name = (SELECT Win32_BIOS_Name FROM StagingSystemInfo WHERE
SystemInfo.SystemName = StagingSystemInfo.SystemName),
Win32_BIOS_SMBIOSBIOSVersion = (SELECT Win32_BIOS_SMBIOSBIOSVersion FROM
StagingSystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName),
Win32_ComputerSystem_Model = (SELECT Win32_ComputerSystem_Model FROM
StagingSystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName),
Win32_ComputerSystem_TotalPhysicalMemory
= (SELECT
Win32_ComputerSystem_TotalPhysicalMemory
FROM StagingSystemInfo WHERE
SystemInfo.SystemName = StagingSystemInfo.SystemName),
Win32_ComputerSystem_TimeZone = (SELECT Win32_ComputerSystem_TimeZone FROM
StagingSystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName),
Win32_DiskDrive_Model = (SELECT Win32_DiskDrive_Model FROM
StagingSystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName),
Win32_DiskDrive_FirmWare = (SELECT Win32_DiskDrive_FirmWare FROM
StagingSystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName),
Win32_LogicalDisk_FreeSpace = (SELECT Win32_LogicalDisk_FreeSpace FROM
StagingSystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName),
Win32_LogicalDisk_Size = (SELECT Win32_LogicalDisk_Size FROM
StagingSystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName),
Win32_POTSModem_Description = (SELECT Win32_POTSModem_Description FROM
StagingSystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName),
LTSPOS_ModemFlashDate = (SELECT LTSPOS_ModemFlashDate FROM
StagingSystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName),
Win32_Processor_CurrentClockSpeed = (SELECT
Win32_Processor_CurrentClockSpeed FROM StagingSystemInfo WHERE
SystemInfo.SystemName = StagingSystemInfo.SystemName),
WHERE EXISTS (SELECT * FROM StagingSystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName)
INSERT INTO SystemInfo(IBDI_IBDIVersion, Win32_BIOS_Name,
Win32_BIOS_SMBIOSBIOSVersion, Win32_ComputerSystem_Model,
Win32_ComputerSystem_TotalPhysicalMemory
, Win32_ComputerSystem_TimeZone,
Win32_DiskDrive_Model, Win32_DiskDrive_FirmWare,
Win32_LogicalDisk_FreeSpace, Win32_LogicalDisk_Size,
Win32_POTSModem_Description, LTSPOS_ModemFlashDate,
Win32_Processor_CurrentClockSpeed, SystemName)
SELECT IBDI_IBDIVersion, Win32_BIOS_Name, Win32_BIOS_SMBIOSBIOSVersion,
Win32_ComputerSystem_Model, Win32_ComputerSystem_TotalPhysicalMemory
,
Win32_ComputerSystem_TimeZone, Win32_DiskDrive_Model,
Win32_DiskDrive_FirmWare, Win32_LogicalDisk_FreeSpace,
Win32_LogicalDisk_Size, Win32_POTSModem_Description, LTSPOS_ModemFlashDate,
Win32_Processor_CurrentClockSpeed, SystemName
FROM StagingSystemInfo
WHERE NOT EXISTS (SELECT * FROM SystemInfo WHERE SystemInfo.SystemName =
StagingSystemInfo.SystemName)