Life with SQL server

 GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: KrishanG -- Create date: 23/10/2023 -- Description: Check if the desired tables get populated with a set of records -- ============================================= Create PROCEDURE spShowTableRecords  @tableSchemaName varchar(200) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @SQL varchar(max)   SELECT @SQL=STRING_AGG( 'select '''+table_name+''' as TableName,count(*) as NoOfRecords  from '+TABLE_SCHEMA+'.'+ table_name, ' union ') FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND TABLE_SCHEMA=@tableSchemaName   --print @SQL   exec (@SQL);     END GO

In the realm of data analysis and database management, the challenge often arises when comparing fields between two tables. This becomes particularly daunting when the tables comprise an extensive list of over 100 fields each, and to top it off, the field names don't match between the two tables. In such scenarios, manual comparisons could be time-consuming and prone to errors. To tackle this conundrum, I devised a solution that efficiently addresses the task at hand: a dynamically generated function that automates field comparisons based on a field mapping table. This approach not only minimizes the room for error but also enhances the overall efficiency of the comparison process. The Core Idea: Utilizing Field Mapping The foundation of this approach lies in the concept of field mapping. Essentially, a field mapping table acts as a bridge between the two tables, defining the correspondence between fields that might not have the same names. The field mapping table includes entries

 The following will update the date time to your servers system date time adjusted with correct time offset. Select   CONVERT(datetime,                 SWITCHOFFSET(CONVERT(datetimeoffset,                                     MYSQLDateField),                              DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as UpdatedDate from YourMYSQLtable;

-- Convert JSON Key-Value Pair Dataset into HTML -- -- Function: fnJsonToUlList -- Author: KrishanG -- Date: 13/06/2023 -- Description: Converts a JSON key-value pair dataset into an HTML unordered list (UL) -- Example usage of the @json parameter: -- SET @json = '{"Title":"Mrs","FirstName":"Grace","LastName":"Gibbs","SurnameAtSchool":"Miles","email":"gibbs@outlook.com","PhoneNumber":"0402 935 000","YearLeft":"2013","ContactYesNo":"No","ProfTopic":"N\/A","MoreDetails":"N\/A"}' -- ================================================ -- Function Definition -- ================================================ ALTER FUNCTION fnJsonToUlList ( -- Parameters @json nvarchar(max) ) RETURNS nvarchar(max) AS BEGIN -- Variable declaration DECLARE @Result nvarchar(max) -- C

 CREATE FUNCTION dbo.Split_On_Upper_Case  (      @String VARCHAR(4000)  ) RETURNS VARCHAR(4000) AS BEGIN DECLARE @Char CHAR(1); DECLARE @i    INT = 0; DECLARE @OutString VARCHAR(4000) = ''; WHILE (@i <= LEN(@String)) BEGIN     SELECT @Char = SUBSTRING(@String, @i,1)     IF (@Char = UPPER(@Char) Collate Latin1_General_CS_AI)         SET @OutString = @OutString + ' ' + @Char;     ELSE         SET @OutString = @OutString +  @Char;      SET @i += 1; END  SET @OutString =  LTRIM(@OutString);  RETURN @OutString; END 

Let's say you have a MYSQL database that stores form data as a JSON string. To read the data and manipulate it as SQL, you can use SQL link services. Here's how it works: First, you create a temporary table to store the data from MYSQL. You can use the following SQL script to do this: sql /****** Object: Table [dbo].[tbljson] Script Date: 29/03/2023 10:37:48 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[#tbljson]( [ID] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , [form_id] [ int ] NOT NULL , [params] [nvarchar](max) NULL , CONSTRAINT [PK_tbljson] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ] GO Once you've created the temporary table, you can insert the values from the MYSQL database into it using the following SQL script: insert into #tbljson s