Batch - For loop - Set two variables in loop to sqlcmd

Question

I have this batch that get the free space about the devices that my system has and the name of they (C:\, D:\, etc).

for /f "tokens=2 delims==" %%I in (
    'wmic LOGICALDISK get FreeSpace /format:list 2^>NUL'
) do (sqlcmd -v varSpace="%%I" column="FIELD1" -i C:\cmdutils\test.sql)

for /f "tokens=2 delims==" %%U in (
    'wmic LOGICALDISK get name /format:list 2^>NUL'
) do (sqlcmd -v varSpace="%%U" column="FIELD2" -i C:\cmdutils\test.sql)

The sql file does an insert:

USE [BBDD_SYSTEM]
INSERT INTO SYS_TABLE ($(column)) VALUES ('$(varSpace)')

The problem is the first for loop insert 3 rows (I have 3 disks) with the free space and the second loop insert 3 rows more with the name, total 6 rows

The correct operation is 3 rows in total with the space and name.

How can I join the two loops?

aschipfl · Accepted Answer · 2015-12-29 01:34:09Z

You can combine the two wmic command lines you are using, like this:

for /F "skip=1 tokens=1,2,3 delims= " %%I in ('
    2^> nul wmic LOGICALDISK GET FreeSpace^,Name^,Size /FORMAT:TABLE
') do (
    if not "%%K"=="" (
        sqlcmd -v varSpace="%%I" column="FIELD1" -v varName="%%J" column="FIELD2" -i C:\cmdutils\test.sql
    )
)

There are the 3 fields FreeSpace, Name, Size returned in table format. The first line is skipped by for /F, because it is the header. Then only the first 2 fields FreeSpace and Name are extracted but Size is not; this has just been added so that the needed field Name is not the last one, because wmic produces Unicode output, and for /F does proper conversion to ANSI but not for the last field.

The if not "%%K"=="" query is intended to filter out disks that are not formatted. For such disks, both FreeSpace and Size are empty, so there is only one token Name.

Caution:
I am definitely not sure about the sqlcmd command line, actually I was just guessing.

Magoo · Accepted Answer · 2015-12-29 01:56:17Z

1

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
FOR /f "tokens=1*delims==" %%a IN (
 'wmic LOGICALDISK get FreeSpace^,name /format:list' 
 ) DO (
 IF /i "%%a"=="Freespace" SET "free=%%b"
 IF /i "%%a"=="Name" (
  SET "name=%%b"
  ECHO sqlcmd -v varSpace="!free:~0,-1!" column="FIELD1" -v varSpace="!name:~0,2!" column="FIELD2" -i C:\cmdutils\test.sql
 )
)

GOTO :EOF

This worked for me, but I'm not sure of the sqlcmd syntax (the sqlcmd is simply echoed)

answered Dec 29, 2015 at 1:56

Magoo

80.8k8 gold badges68 silver badges92 bronze badges

Comments

SachaDee · Accepted Answer · 2015-12-29 02:22:47Z

1

With WMIC:

@echo off

for /f "tokens=1,2 delims= " %%a in ('wmic LOGICALDISK get freespace^,name /format:table ^| findstr "[0-9] :"') do (
  echo Do something SQL with Drive : [%%b] Free : [%%a]
)

Another way is to use the DIR command piped in Find "Bytes "to get the 2 variables :

@echo off
setlocal enabledelayedexpansion
set /a $c=1
set "$drive=c: d: e:"
for %%a in (%$Drive%) do (
   for /f "tokens=3 delims= " %%b in ('2^>nul dir %%a ^|find "bytes "') do (
      echo Do something SQL with Drive : [%%a] Free : [%%b]
  )
)

edited Dec 29, 2015 at 2:22

answered Dec 28, 2015 at 23:57

SachaDee

9,6833 gold badges27 silver badges36 bronze badges

Collectives™ on Stack Overflow

Batch - For loop - Set two variables in loop to sqlcmd

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related