Polaris SQL Forum
The "funnest" session of all IUG!

Microsoft SQL Server is the underlying database for Polaris. The SQL dialect for this database is T-SQL.  This is important to know when googling how to do a particular action/function. Including the keyword tsql helps zero in on helpful results.

Read Access is great! Cricital for obtaining reports not otheriwse included with Polaris as a standard product. 

For the fearless, there are also SQL updates. 

UPDATE Best Practices

1) Back up the table you are editing.  These can be saved indefinitely (though I delete mine after a year).

SELECT * INTO Polaris.Polaris.Fines_BACKUP20190507 FROM Polaris.Polaris.Fines WITH (NOLOCK)

2) Run a SELECT statement to check how many rows should be edited in your update.  You can compare the number of rows from the SELECT to the number of affected rows in your UPDATE. This is a good first indicator of something is not going according to plan.

3) Wrap your edits with BEGIN TRANDASCTION, END TRANSACTION
Then you can test the result before you COMMIT TRANSACTION.  If you find something amiss, you can always ROLLBACK TRANSACTION.

Q: Is there a way to upload TRN offline files via SQL automatically?
A: You might be able to write a SQL job to slurp a TRN file and make the appropriate changes to the DB.  Hoever, in this specific situation you might be better served by the automatic upload of offline files which is available in several recent versions of Polaris. Take a look!
PS: Since the actions in questions are exclusively for the creation/editing of patrons, Trevor's recommendation would be to skip the TRN step and write a SQL job to slurp the excel and edit the patron tables in Polaris.

Updating Polaris Using SQL
The most basic UPDATE statement is as follows.

UPDATE Polaris.Polaris.Fines SET Amount = '.10'

which would set the Amount field in every row of the table to the same amount.  Usually you only want to update a certain set of rows.  Thus you can use the WHERE clause.

UPDATE Polaris.Polaris.Fines SET Amount = '.10' WHERE OrganizationID = '3'

You can nest queries quite easily in SQL. The following code combines several tables to find items from Library A where the item price field does not have a value AND there is a price value in the bib record.

SELECT cir.ItemRecordID
FROM Polaris.Polaris.BibliographicRecords AS [br] WITH (NOLOCK)
LEFT OUTER JOIN Polaris.Polaris.BibliographicTags AS [bt] WITH (NOLOCK)
ON br.BibliographicRecordID = bt.BibliographicRecordID AND bt.TagNumber = '20'
LEFT OUTER JOIN Polaris.Polaris.BibliographicSubfields AS [bs] WITH (NOLOCK)
ON bt.BibliographicTagID = bs.BibliographicTagID AND bs.Subfield = 'c'
INNER JOIN Polaris.Polaris.CircItemRecords AS [cir] WITH (NOLOCK)
ON br.BibliographicRecordID = cir.AssociatedBibRecordID
INNER JOIN Polaris.Polaris.ItemRecordDetails AS [ird] WITH (NOLOCK)
ON cir.ItemRecordID = ird.ItemRecordID
WHERE bs.Data IS NOT NULL
AND ird.Price IS NULL
AND cir.AssignedBranchID = '29'

The end goal is to update all the above items so that the price field is filled in.  The UPDATE statement then looks like this

UPDATE Polaris.Polaris.ItemRecordDetails
SET Price = bs.Data
FROM Polaris.Polaris.BibliographicRecords AS [br] WITH (NOLOCK)
LEFT OUTER JOIN Polaris.Polaris.BibliographicTags AS [bt] WITH (NOLOCK)
ON br.BibliographicRecordID = bt.BibliographicRecordID AND bt.TagNumber = '20'
LEFT OUTER JOIN Polaris.Polaris.BibliographicSubfields AS [bs] WITH (NOLOCK)
ON bt.BibliographicTagID = bs.BibliographicTagID AND bs.Subfield = 'c'
INNER JOIN Polaris.Polaris.CircItemRecords AS [cir] WITH (NOLOCK)
ON br.BibliographicRecordID = cir.AssociatedBibRecordID
INNER JOIN Polaris.Polaris.ItemRecordDetails AS [ird] WITH (NOLOCK)
ON cir.ItemRecordID = ird.ItemRecordID
WHERE bs.Data IS NOT NULL
AND ird.Price IS NULL
AND cir.AssignedBranchID = '29'

NOTE: In this specific example, additional massaging of the data would be necessary since the bib data isn't in the required numerical format.

Q: Is there a way to do an "append" bulk change to a block/notes field via SQL?
A: Yes!  You can use the CONCAT function to update a field to be itself+new data.

UPDATE Polaris.Polaris.PatronFreeTextBlocks SET FreeTextBlock = CONCAT(FreeTextBlock,'Hello World')

Q: Why should I learn SQL?  What are some of the things it can do for me?
A: In addition to being able to do bulk updates (most of which can be accomplished with a bit of elbow grease via the client), you can use SQL to retrieve reports that are otherwise impossible to get unless you pay for custom work from Innovative. Below is the SQL statement for a report which pulls circulation by item assigned branch, breaking it down by collection, material type, and shelf location.  The data includes checkouts, renewals, a total circ, and the number of unique items that account for the circ in any given category.

SELECT io.Abbreviation AS [Library], ISNULL(c.Name,'None') AS [Collection], mt.Description AS [Material Type], ISNULL(sl.Description,'None') AS [Shelf Location], CASE WHEN td2.numValue IS NULL THEN 'Checkout' ELSE 'Renewal' END AS [Activity], COUNT(*) AS [Circs]
INTO ##Circs
FROM PolarisTransactions.Polaris.TransactionHeaders AS [th] WITH (NOLOCK)
--material type
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td0] WITH (NOLOCK)
ON th.TransactionID = td0.TransactionID AND td0.TransactionSubTypeID = '4'
INNER JOIN Polaris.Polaris.MaterialTypes AS [mt] WITH (NOLOCK)
ON td0.numValue = mt.MaterialTypeID
--collection code
LEFT OUTER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td1]
ON th.TransactionID = td1.TransactionID AND td1.TransactionSubTypeID = '61'
LEFT OUTER JOIN Polaris.Polaris.Collections AS [c] WITH (NOLOCK)
ON td1.numValue = c.CollectionID
--renewals
LEFT OUTER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td2]
ON th.TransactionID = td2.TransactionID AND td2.TransactionSubTypeID = '124'
--item assigned branch
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td3]
ON th.TransactionID = td3.TransactionID AND td3.TransactionSubTypeID = '125'
INNER JOIN Polaris.Polaris.Organizations AS [io] WITH (NOLOCK)
ON td3.numValue = io.OrganizationID
--shelf location
LEFT OUTER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td4]
ON th.TransactionID = td4.TransactionID AND td4.TransactionSubTypeID = '296'
LEFT OUTER JOIN Polaris.Polaris.ShelfLocations AS [sl] WITH (NOLOCK)
ON td4.numValue = sl.ShelfLocationID AND td3.numValue = sl.OrganizationID
WHERE th.TransactionTypeID ='6001'
AND th.TranClientDate BETWEEN @StartDate AND DATEADD(day,1,@EndDate)
AND io.OrganizationID IN (@OrgID)
GROUP BY io.Abbreviation, ISNULL(c.Name,'None'), mt.Description, ISNULL(sl.Description,'None'),CASE WHEN td2.numValue IS NULL THEN 'Checkout' ELSE 'Renewal' END

SELECT io.Abbreviation AS [Library], ISNULL(c.Name,'None') AS [Collection], mt.Description AS [Material Type], ISNULL(sl.Description,'None') AS [Shelf Location], COUNT(DISTINCT(td5.numValue)) AS [Unique Items]
INTO ##Uniqs
FROM PolarisTransactions.Polaris.TransactionHeaders AS [th] WITH (NOLOCK)
--material type
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td0] WITH (NOLOCK)
ON th.TransactionID = td0.TransactionID AND td0.TransactionSubTypeID = '4'
INNER JOIN Polaris.Polaris.MaterialTypes AS [mt] WITH (NOLOCK)
ON td0.numValue = mt.MaterialTypeID
--collection code
LEFT OUTER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td1]
ON th.TransactionID = td1.TransactionID AND td1.TransactionSubTypeID = '61'
LEFT OUTER JOIN Polaris.Polaris.Collections AS [c] WITH (NOLOCK)
ON td1.numValue = c.CollectionID
--renewals
LEFT OUTER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td2]
ON th.TransactionID = td2.TransactionID AND td2.TransactionSubTypeID = '124'
--item assigned branch
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td3]
ON th.TransactionID = td3.TransactionID AND td3.TransactionSubTypeID = '125'
INNER JOIN Polaris.Polaris.Organizations AS [io] WITH (NOLOCK)
ON td3.numValue = io.OrganizationID
--shelf location
LEFT OUTER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td4]
ON th.TransactionID = td4.TransactionID AND td4.TransactionSubTypeID = '296'
LEFT OUTER JOIN Polaris.Polaris.ShelfLocations AS [sl] WITH (NOLOCK)
ON td4.numValue = sl.ShelfLocationID AND td3.numValue = sl.OrganizationID
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td5] WITH (NOLOCK)
ON th.TransactionID = td5.TransactionID AND td5.TransactionSubTypeID = '38'
WHERE th.TransactionTypeID ='6001'
AND th.TranClientDate BETWEEN @StartDate AND DATEADD(day,1,@EndDate)
AND io.OrganizationID IN (@OrgID)
GROUP BY io.Abbreviation , ISNULL(c.Name,'None'), mt.Description, ISNULL(sl.Description,'None')

SELECT cc.Library, cc.Collection, cc.[Material Type], cc.[Shelf Location], cc.Activity, cc.Circs, uu.[Unique Items]
FROM ##Circs AS [cc]
INNER JOIN ##Uniqs AS [uu]
ON cc.Library = uu.Library AND cc.Collection = uu.Collection AND cc.[Material Type] = uu.[Material Type] AND cc.[Shelf Location] = uu.[Shelf Location]
ORDER BY cc.Library, cc.Collection, cc.[Material Type], cc.[Shelf Location], cc.Activity

DROP TABLE ##Circs
DROP TABLE ##Uniqs