Saturday, December 3, 2016

SQL Server - Providing Demo Data (temporary)

Today I found a new solution to provide temporary data! Additionally to creating a table (not really temporary), creating a temp table (create table #xy) and declare a table variable (declare @xy table(...)) there is also the possibility to put values directly into a select statement:

select * from
) as myValues (nr1, nr2, nr3);

this looks quite easy and can be very useful in a with block, because you can use this adhoc-values like a table and if it is necessary you can move them very easily into a table.

with config as (
  select * from ( values ('dataFolder', '/home/...') as config (key, val)
  dataFolder.Value + data.FileName, data.Content
  data, config dataFolder
   dataFolder.Key = 'dataFolder'

... but this is probably not the best show case... I think demonstration / training about SQL would be a better use-case (e.g.: showing how join statements work or stuff like this... would be the first training explaining "with" before "join" :-) ... nevertheless I was fascinated and love this feature...).


No comments: