Posted By

HarleyAR on 07/13/12

Tagged

Versions (?)

Last Edited at 07/13/12 11:36pm

Statistics

Viewed 755 times

Favorited by 0 user(s)

Related snippets

Powershell & SQL - Add unique key identity column

/ Published in: Windows PowerShell

Adds unique key identity column to tables based off of csv. Custom script for Kronos.

Highlighted TextExpand

Import-Module SQLServer

# Declare & set primary variables/constants
$SQLServer     = "KRONOSDBARC"
$SQLDB         = "TKCSDB"
$NewIndexCol    = "UQKey"

$dataSource=import-csv â€œq:\temp\tables.csvâ€

foreach($dataRecord in $datasource) 
{
  $NewIndex = $NewIndexCol + "_" + $dataRecord.Table

$SQLQuery = "Delete from " + $dataRecord.Table
  write $SQLQuery
  Set-SqlData -sqlserver $SQLServer -dbname $SQLDB -qry $SQLQuery
  
  $SQLQuery = "Alter Table " + $dataRecord.Table + " add " + $NewIndexCol + " int identity not null"
  write $SQLQuery
  Set-SqlData -sqlserver $SQLServer -dbname $SQLDB -qry $SQLQuery  
  
  $SQLQuery = "Create unique index " + $NewIndex + " on " + $dataRecord.Table + "(" + $NewIndexCol + ")"
  write $SQLQuery
  Set-SqlData -sqlserver $SQLServer -dbname $SQLDB -qry $SQLQuery  
}

<# q:\temp\tables.csv
Table,keyCols,type,Row Count
ARCHIVEEMAILS,<none>,,0
ARCMETADATASTR,<none>,,0
ARCPROCESSMAP,<none>,,0
ASSIGNMENTRULE,**Needs key with non-nullable columns**,,0
COLUMNCONFIGMM,<none>,,240
COMBINATION,<none>,,1
COMBINATNKEY,<none>,,12
ES_TEMP,<none>,,0
FCOPERHRSITEM,<none>,,0
FCTIMESCALEITM,<none>,,0
KCSDBINITDATA,<none>,,0
KCSONEROW,<none>,,1
KCSUPGRADEHISTORY,<none>,,78
LLSETLLEIMPORT,<none>,,0
MINORRULEDAY,<none>,,0
MINORRULEWEEK,<none>,,0
MYWTKEMPLOYEE,**Needs key with non-nullable columns**,,31634
MYWTKGENIEEMPS,**Needs key with non-nullable columns**,,0
MYWTKSCHEDEMPS,**Needs key with non-nullable columns**,,0
NOTIFMSGQUEUE,**Needs key with non-nullable columns**,,0
PERSONDATEIMPORT,<none>,,0
PERSONMANYIMPORT,<none>,,0
PRGDPRSNDEVUPD,<none>,,757
PT_TEMP,<none>,,0
PUNCHEVENTTRC,<none>,,907287
RECENTENTRY,<none>,,941
RPTHFREPLACE,<none>,,0
SCHEDPATRNDATE,<none>,,1
SEQCHECK,<none>,,0
SEQUENCEDITEM,**Needs key with non-nullable columns**,,7
SHFTASGNMNTTRC,<none>,,3123600
TI_TEMP,<none>,,0
TIMESHTITMTRC,<none>,,54103585
TP_TEMP,<none>,,0
WFC_TEMPCOLUMNS,<none>,,0
WFC_TEMPOBJECTS,<none>,,0
WFCAUDIT,<none>,,2310481
WFCGLOBALS,<none>,,1
WFCSEGMENTMAP,<none>,,9
WMTDEVICEUPDATE,**Needs key with non-nullable columns**,,0
WPMSGRPSCHBDNOTS,**Needs key with non-nullable columns**,,0
WPMSHFTDAYBDNOTS,**Needs key with non-nullable columns**,,0
WPMSSCPTRNBDNOTS,**Needs key with non-nullable columns**,,0
WPMVACBIDNOTES,**Needs key with non-nullable columns**,,0
WRMEMAILADDRES,<none>,,0
WRMJOBSTATHIST,<none>,,0
WRMMDCOLUMNVAL,<none>,,0
WRMMDROWSELDTA,<none>,,0
WRMRETROCPHIST,<none>,,0
WRMRTRCPENDVER,<none>,,0
WRMTASKPROCMAP,<none>,,0
WTDOPTOUTSTAT,<none>,,0
#>

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.