Populate SQL Table with Powershell script

• Loundy

  SSCertifiable

  Points: 6080

  More actions

  January 15, 2013 at 3:30 am

  #269207

  Hi All,

  I'd like to use a powershell script to populate a table with fixed drive space info.

  here is the table schema:

  IF OBJECT_ID(N'[dbo].[FixedDriveInformation]', N'U') IS NULL

  BEGIN

  CREATE TABLE dbo.FixedDriveInformation

  (

  Id INT IDENTITY(1,1) NOT NULL

  ,ServerInstance VARCHAR(100) NOT NULL

  ,DriveLetter CHAR(1) NOT NULL

  ,DriveLabel VARCHAR(50) NOT NULL

  ,GBFreeSpace NUMERIC(2,2) NOT NULL

  ,DriveSize NUMERIC(2,2) NOT NULL

  CONSTRAINT [FixedDriveInformation_Id] PRIMARY KEY CLUSTERED (Id)

  )

  IF OBJECT_ID(N'[dbo].[FixedDriveInformation]', N'U') IS NOT NULL

  BEGIN

  PRINT 'SUCCESS - Table [dbo].[FixedDriveInformation] created.'

  END

  ELSE

  BEGIN

  RAISERROR('FAIL - Table [dbo].[FixedDriveInformation] not created.',16,1)

  END

  END

  ELSE

  BEGIN

  PRINT 'EXISTS - Table [dbo].[FixedDriveInformation] already exists.'

  END

  GO

  Here is the powershell code i'm using:

  gwmi win32_volume -filter 'drivetype = 3' | select driveletter, label, @{LABEL='GBfreespace';EXPRESSION={"{0:N2}" -f ($_.freespace/1GB)} }, @{LABEL='DriveSize';EXPRESSION={"{0:N2}" -f ($_.Capacity/1GB)}}

  Does anyone know the best way for me to go about doing this?

  Cheers,

  [font="Times New Roman"]There's no kill switch on awesome![/font]

• Loundy

  SSCertifiable

  Points: 6080

  More actions

  January 15, 2013 at 3:40 am

  #1577227

  Just found this post which looks like it answers my questions 🙂

  http://www.sqlservercentral.com/articles/powershell/65196/

  [font="Times New Roman"]There's no kill switch on awesome![/font]