Category: SQL

Posted on

JSON Data in SQL Server

The write way to declare JSON column
- min NVARCHAR(4000) is required to identify as JSON DATA
CREATE TABLE BLOG
(
    blogID  INT                   IDENTITY(1,1),
    blogTitle  NVARCHAR(100),
    blogText  NVARCHAR(MAX),
    blogDetails    NVARCHAR(4000) 
    CONSTRAINT pk_blog_blodID             PRIMARY KEY(blogID),
    CONSTRAINT chk_blog_blogDetails CHECK(ISJSON(blogDetails) = 1 )
)

JSON_VALUE(<field>,<path>)

JSON_QUERY(<field>,<path>)

JSON_MODIFY(<field>,<path>,<new value>)

SELECT * FROM OPENROWSET(BULK N’C:\data\data.json’ SINGLE_CLOB) as jData
— returns a table with ‘BulkColumn’ field

OPENJSON & CROSSAPPLY

SELECT * FROM OPENROWSET(BULK N’C:\data\data.json’ SINGLE_CLOB) as jData
CROSS APPLY OPENJSON(BulkColumn)

https://bertwagner.com/posts/the-ultimate-sql-server-json-cheat-sheet/
Basics of Working with JSON in SQL Server
https://codingsight.com/json-for-sql-server-part-1/
https://codingsight.com/sql-server-json-part-2/
Calculating Running Total with OVER Clause and PARTITION BY Clause in SQL Server
Grouping Data using the OVER and PARTITION BY Functions
Posted on

Enforce Unique Nullable Column With ‘IGNORE_DUP_KEY = ON’

CREATE TABLE [identigy].[patron](
	[ID] [bigint] NOT NULL,
	[DISID] [nvarchar](20) NOT NULL,
	[PKID] [nvarchar](20) NULL,
	[DISHID] [nvarchar](20) NULL,
	[FIRSTNAME] [nvarchar](250) NULL,
	[LASTNAME] [nvarchar](250) NULL,
	[CLASSIFICATION] [nvarchar](250) NULL,
	[FLAGS] [nvarchar](500) NULL,
	[SHIFTS] [nvarchar](50) NULL,
	[ACTION] [nvarchar](10) NULL,
	[ACTIVE] [bit] NOT NULL CONSTRAINT [DEFAULT_Identigy_Patron_Active]  DEFAULT ((1))
	,[DISIDnullbuster] AS (CASE WHEN [DISID] IS NULL THEN CAST(ID AS VARCHAR(20)) ELSE DISID END)
	,[PKIDnullbuster] AS (CASE WHEN [PKID] IS NULL THEN CAST(ID AS VARCHAR(20)) ELSE PKID END)
 ,CONSTRAINT [PK_Identigy_Patron] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
 ,CONSTRAINT [UX_Identigy_Patron_DISID] UNIQUE ( [DISIDnullbuster] ASC ) WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
 ,CONSTRAINT [UX_Identigy_Patron_PKID] UNIQUE ( [PKIDnullbuster] ASC ) WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Posted on

SQL Bulk Upload

How can we avoid duplicate values while we are doing bulk insert to a table

--BEGIN TRAN
CREATE TABLE [tlg].[UserVisitLogs](
	[TStamp] 			DATETIME,
	[SessionID] 		NVARCHAR(20),
	[CorrelationID] 	NVARCHAR(20),
	[SchoolCode] 		NVARCHAR(20),
	[SiteExternalID] 	NVARCHAR(20),
	[WebType] 			NVARCHAR(20),
	[WebUrl] 			NVARCHAR(100),
	[UserSid] 			NVARCHAR(50),
	[UserName] 			NVARCHAR(20),
	[UserType] 			NVARCHAR(20),
	[CachedUser] 		NVARCHAR(20)
)
GO

-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicate
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-Logs] ON [tlg].[UserVisitLogs]
(
	[TStamp]			DESC,
	[SessionID] 		ASC,
	[CorrelationID] 	ASC,
	[SchoolCode] 		ASC,
	[SiteExternalID] 	ASC,
	[WebType] 			ASC,
	[WebUrl] 			ASC,
	[UserSid] 			ASC,
	[UserName] 			ASC,
	[UserType] 			ASC,
	[CachedUser] 		ASC
) WITH (IGNORE_DUP_KEY = ON)
GO
--ROLLBACK TRAN

/*
Thanks to dbatools.io : https://www.youtube.com/watch?v=kQYUrSlb0wg
*/