SharePoint Online Connector

SELECT * FROM Lists

SELECT * FROM MyList 
--WHERE Id = 1234

INSERT INTO MyList(Name, Surname)
VALUES ('John', 'Doe') WITH (Output=1)

--new version --				
INSERT INTO MyList(Title, ManyPersonsLookupId, ManyPersonsLookupIdOdataType)
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)

--old version --				
INSERT INTO MyList(Title, ManyPersonsLookupId, "fields.ManyPersonsLookupId@odata\u002Etype")
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)
--Considering "ManyPersons" is the name of the field in SharePoint List

--new version --				
INSERT INTO MyList(Title, MultiChoiceField, MultiChoiceFieldODataType)
VALUES ('John Doe', '["Choice1","Choice5"]', 'Collection(Edm.String)')
WITH (Output=1)

--old version --				

INSERT INTO MyList(Title, MultiChoiceField, "fields.MultiChoiceField@odata\u002Etype")
VALUES ('John Doe', '["Choice1","Choice5"]', 'Collection(Edm.String)')
WITH (Output=1)

--Considering "MultiChoiceField" is the name of the field in SharePoint List

UPDATE MyList
SET Name = 'Elizabeth', Surname = 'Johnson'
WHERE Id = '123'

UPDATE MyList
SET Title = 'Elizabeth Johnson',
	--Method#1 (Pass type info in the same value)
    ManyPersonsLookupId = '[11,22,33]', "fields.ManyPersonsLookupId@odata\u002Etype" = 'Collection(Edm.Int32)'
    --Method#2 (Pass type info via ODataField)
	--OR--
	--ManyPersonsLookupId = '[11,22,33]',
    --ManyPersonsLookupIdODataType" = 'Collection(Edm.Int32)
	
WHERE Id = '123'

UPDATE  "Travel Requests" --list name
SET   
	 Title='MyTitle @ <<FUN_NOW>>'  
	 --Method#1 Pass Value and Type in 2 fields
	, CustomText = 'This is text'
	, CustomTextLarge = 'This is really long text ....'	                     
	, CustomNumber = 10000.12345
	, CustomDateTime ='2012-12-31T23:59:59.123Z' --End with Z to treat as UTC time (avoid local-UTC conversion)
	, CustomChoice = 'BB'
	
	--Choice Multi-Select
	, CustomChoiceMulti = '["AA", "BB"]'
	, CustomChoiceMultiODataType='Collection(Edm.String)' --for multi select must supply OData Type 
	--OR-- append like below in one field
	--, CustomChoiceMulti = '["AA", "BB"], "CustomChoiceMulti@odata.type" : "Collection(Edm.String)"'
	
	--Lookup
	--field with Lookup type must be set to [<field-name>LookupId] field (suffix with LookupId)
	, CustomLookupSingleLookupId = 1 
	
	--Lookup Multi-Select
	, CustomLookupMultiLookupId = '[1,3]' --for multi select must supply OData Type 
	, CustomLookupMultiLookupIdODataType='Collection(Edm.Int32)'
	--OR-- append like below in one field
	--, CustomLookupMultiLookupId = '[1,3], "CustomLookupMultiLookupId@odata.type" : "Collection(Edm.String)"'
	
	--Person 
	,CustomPersonLookupId = 11  
	
	--Person Multi-Select
	, CustomPersonMultiLookupId = '[11, 22]' 
	, CustomPersonMultiLookupIdODataType='Collection(Edm.Int32)'
	--OR-- append like below in one field
	--, CustomPersonMultiLookupIdODataType = '[11,22], "CustomPersonMultiLookupIdODataType@odata.type" : "Collection(Edm.Int32)"'
	
Where Id=1 --List Item Id

--- Insert Example ----

INSERT INTO "Travel Requests" --list name
(
	 Title
	, CustomText 
	, CustomTextLarge 
	, CustomNumber 
	, CustomDateTime 
	, CustomChoice 
	
	, CustomChoiceMulti 
	, CustomChoiceMultiODataType

	, CustomLookupSingleLookupId 
	
	--Lookup Multi-Select
	, CustomLookupMultiLookupId 
	, CustomLookupMultiLookupIdODataType
	
	--Person 
	,CustomPersonLookupId 
	
	, CustomPersonMultiLookupId 
	, CustomPersonMultiLookupIdODataType
)
VALUES(
	  'MyTitle @ <<FUN_NOW>>'  
	, 'This is text'
	, 'This is really long text .. '	                     
	, 10000.12345
	, '2012-12-31T23:59:59.123Z' --End with Z to treat as UTC time (avoid local-UTC conversion)
	, 'BB'

	--Choice Multi-Select
	, '["AA", "BB"]'
	, 'Collection(Edm.String)' --for multi select must supply OData Type 
	
	--Lookup
	,  1 
	
	--Lookup Multi-Select
	, '[1,3]' --for multi select must supply OData Type 
	, 'Collection(Edm.Int32)'
	
	--Person 
	,11  
	
	--Person Multi-Select
	, '[11, 22]' 
	, 'Collection(Edm.Int32)'
)

DELETE FROM MyList WHERE Id=888
--OR--        
DELETE FROM MyList WITH (ListItemId='123')

--ListItemId can be retrieved by selecting from named list table, e.g. 'MyList'.

SELECT * FROM get_main_site

SELECT * FROM Sites

SELECT * FROM Drives

SELECT * FROM get_drive
WITH (DriveId='b!GtLQTMU726LE0eY5F2BBNi14-XXXXXXXXXXX-GuQ4DORpHy-XXXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.

SELECT * FROM list_folders
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.

SELECT * FROM list_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.

SELECT * FROM list_excel_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.

SELECT * FROM get_list_fields
WITH (ListId='2e1d58e4-eced-4d1c-9279-XXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.

SELECT * FROM get_list_items
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.

SELECT * FROM get_list_items_dynamic
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.

INSERT INTO create_list_item(Title, OrderId)
VALUES ('My super title', 12345)
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
     ,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.

UPDATE update_list_item
SET Title = 'My super title', OrderId = 12345
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXX'
     ,ListItemId='775'
     ,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.

DELETE FROM delete_list_item
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
     ,ListItemId='775')
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.

SELECT * FROM list_excel_worksheets
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXXX'
     ,FileId='01SUOJPKECYDDVFZWXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.

SELECT * FROM get_excel_worksheet_autodetect
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
  	    ,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
	    ,SheetId='Sheet1' --Id or Name
	    ,AutoDetectByValue='true'
		,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
		)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.

SELECT * FROM get_excel_worksheet
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
  	 ,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
	   ,SheetId='Sheet1' --Id or Name
	   ,Range='A1:K10000'
	   ,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
	   )
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
--Range should be set to Excel-type of range where data is located.

SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
	 ,DiskFilePath='C:\My files\Employees\List.xls'
	 ,SharePointFilePath='List.xlsx'	 --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
   --,SharePointFilePath='myfolder/List.xlsx' --Upload under some folder
   --,SharePointFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
   --,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
   --,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date   
   )
--DriveId can be retrieved by selecting from 'Drives' table.

SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
     ,DiskFilePath='C:\My files\Employees\List.xls'
	 ,SharePointFilePath='List.xlsx'	 --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
   --,SharePointFilePath='myfolder/List.xlsx' --Upload under some folder
   --,SharePointFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
     ,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
     --,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
     )
--DriveId can be retrieved by selecting from 'list_drives' endpoint.

SELECT * FROM download_file_to_disk
    WITH(
      DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMxxxxxxxxxxxxx'
    , FileId='01SUOJPKDXTQL2XI2EIFDYZxxxxxxxxxxxxx'
    , TargetFilePath='C:\temp\List.xlsx'
    --, FileOverwriteMode='FailIfExists' //change default overwrite mode from AlwaysOverwrite to FailIfExists
    )
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.

SELECT Data as BYTES_AS_BASE64_STRING, BASE64_TO_FILE('C:\My files\Employees\List.xls', Data) FILE_SAVED_TO_DISK_FROM_BASE64 
FROM download_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX'
	 ,FileId='01SUOJPKHXMPKD2UXXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.

SELECT *,(JSON_VALUE(MyImageField,'$.serverUrl') || JSON_VALUE(MyImageField,'$.serverRelativeUrl')) as DevicePhotoUrl,
    FROM "My SharePoint List Name"

SELECT * FROM list_files
WITH(
     Filter='$.value[*]' --list both files and folders
   --Filter='$.value[?(@.file.mimeType != null)]' --list only files
   --Filter='$.value[?(@.folder.childCount != null)]' --list only folders
	  ,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
  --,SearchPath='/root'	--default
  --,SearchPath='/root:/myfolder:'	--folder path
	  ,SearchPath='/root:/myfolder/subfolder:' --folder path nested
    ,OrderBy='lastModifiedDateTime desc'
)		
--You can get DriveId by selecting from 'Drives' table.

SELECT * FROM list_files
WITH(
     Filter='$.value[*]' --list both files and folders
   --Filter='$.value[?(@.file.mimeType != null)]' --list only files
   --Filter='$.value[?(@.folder.childCount != null)]' --list only folders
	  ,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
	  ,SearchType='children' --must use this type if you like to list items by FolderId
    ,SearchPath='/items/01SUOJPKBVT5ZC2KVSXJAYSTTMA4I3GMHX'
)		
--You can get DriveId by selecting from 'Drives' table.

INSERT INTO FileReaderWriterCsv
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
	    DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
	  , FileId='root:/test_out.csv:'
	--, FileId='root:/subfolder/test_out.csv:'
	--, FileId='01N3NxxxxxxxWZYSDJ'  --exising File ID
	--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ...
	--, RowDelimiter='{NEWLINE}' --{LF}, {TAB}, | , \x0009 ...
	--, HasColumnHeaderRow=0 --set for header less file
  --, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'  
  --See Query Builder for more options
)

INSERT INTO FileReaderWriterJson
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
	    DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
	  , FileId='root:/test_out.json:'
	--, FileId='root:/subfolder/test_out.json:'
	--, FileId='01N3NxxxxxxxWZYSDJ'  --exising File ID
	  , JsonOutputFormat='Multicontent' --Default , Array2D, ArrayLines ...
	--, DoNotOutputNullProperty='True'
	--, Encoding='UTF8' --UTF8WithoutBOM
  --, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'
	
	--Example#1: Output all columns
	, LayoutMap='<?xml version="1.0"  ?>
<settings>
  <dataset id="root" main="True" readfrominput="True" />
  <map src="*" />  
</settings>'

	--Example#2: Nested JSON (Records under an array)
	/*
	, LayoutMap='<?xml version="1.0" ?>
<settings singledataset="True">
  <dataset id="root" main="True" readfrominput="True" />
  <map name="MyArray" dataset="root" maptype="DocArray">
    <map src="OrderID" name="OrderID" />
    <map src="OrderDate" name="OrderDate" />
  </map>
</settings>'
   */
   
	--Example#3: Nested JSON (Records under a sub document section)
	/*	
	, LayoutMap='<?xml version="1.0" ?>
<settings>
  <dataset id="dsRoot" main="True" readfrominput="True" />
  <map name="NestedSection">
    <map src="OrderID" name="OrderID_MyLabel" />
    <map src="OrderDate" name="OrderDate_MyLabel" />
  </map>
</settings>'
	*/
)

SELECT * from get_csv_file
WITH(
	  DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
	, FileId='root:/MyFolder/dump.csv:'
	--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ... default is comma (",")
	--, HasColumnHeaderRow=0 --set for header less file
	--, FileId='root:/MyFolder/test_out.csv.gz:'
	--, FileCompressionType='GZip' --None, GZip, Zip
)

SELECT * from get_csv_file
WITH(
	  DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
	, FileId='root:/MyFolder/dump.csv.zip:'
	--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ... default is comma (",")
	--, HasColumnHeaderRow=0 --set for header less file
	--, FileId='root:/MyFolder/dump.csv.gz:'
	, FileCompressionType='Zip' --None, GZip, Zip
)

SELECT * from get_json_file 
WITH(
	  DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
	, FileId='root:/MyFolder/dump.json:'
  , Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
  --to read compressed file use below way
	--, FileId='root:/MyFolder/dump.json.gz:'
	--, FileCompressionType='GZip' --None, GZip, Zip
)

SELECT * from get_json_file
WITH(
	  DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
	, FileId='root:/MyFolder/dump.json.zip:'
  , Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
  , FileCompressionType='Zip' --None, GZip, Zip
)

SELECT * from generic_request
WITH(
	  Url='https://graph.microsoft.com/v1.0/sites/root/lists/1d3126af-14ca-46c7-a82a-4865873756c6/items/1'
    , RequestMethod='PATCH'
    , Filter='$.fields'
    , Headers='Content-Type: application/json || x-header2: abcd'	  
	, Body='{
   "fields": {
      "MyMultiSelectLookupColLookupId@odata.type": "Collection(Edm.Int32)" 
     ,"MyMultiSelectLookupColLookupId": [ 1 ,  2 ]
	 
     ,"MyMultiChoiceColumn@odata.type": "Collection(Edm.String)" 
     ,"MyMultiChoiceColumn": [ "AAA" ,  "BBB" ]	 
	 
     ,"MySingleChoiceColumnLookupId":1
    }
  }'
)

SELECT * FROM move_rename_item
WITH(
	--Source Site (Leave blank for default)
	-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
	
	--Source Drive
	, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'
	
	--Item Id (File or Folder) you like to move or rename
	, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'

	--Target Site Id (Optional): Only needed if moving file to a different Site
	--, TargetSiteId='xyz.sharepoint.com,5304daff-xxxxxxxxxx'      
		  
	--Target Drive Id (Optional): Only needed if moving file to a different Drive
	--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
	
	--Target Folder Id
	, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
	--OR (for moving to root)
	--, TargetFolderId='root'

	--Target Item Name after Move (Optional): If you do not wish to change name after Move then do not supply below
	--, TargetItemName='new_file_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf'  

	--Uncomment Below if you get error. Usually needed for large file or cross site operations
	--, Prefer='respond-async'
)

SELECT * FROM move_rename_item
WITH(
	--Source Site (Leave blank for default)
	-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
	
	--Source Drive
	, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'

	--Source Item you like to rename (File or Folder)
	, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'

	--Target Site Id (Optional): Only needed if moving file to a different Site
	--, TargetSiteId='xyz.sharepoint.com,5304daff-xxxxxxxxxx'      
		  
	--Target Drive Id (Optional): Only needed if moving file to a different Drive
	--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'

	--Target Folder (Optional): If you are doing just rename and moving to different location then keep it keep blank
	--, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
	--, TargetFolderId='root'

	--Target Item Name (Required for rename)
	, TargetItemName='new_file_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf' 
	
	--Uncomment Below if you get error. Usually needed for large file or cross site operations
	--, Prefer='respond-async'
)

SELECT * FROM copy_item
WITH(
	--Source Site (Leave blank for default)
	-- SiteId='xxxx'
	
	--Source Drive
	, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'

	--Source Item you like to copy
	, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'

	--Target Site (Optional)
	-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
	
	--Target Drive Id (Optional): Only needed if copying file to a different Drive (e.g. another User's drive)
	--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
	
	--Target Folder where item will be copied
	, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
	--OR--
	--, TargetFolderId='root'

	--Target item name (Optional) - Only needed if new name needed after copy
	, TargetItemName='new_file_or_folder_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf' 

	--,ConflictBehavior='replace' --fail, rename (Ignored if TargetItemName is not supplied)

	--Uncomment Below if you get error. Usually needed for large file or cross site operations
	--, Prefer='respond-async'	
)