Using JSON in SQL server

Selecting data using JSON in sql server:

SELECT gid, email  FROM tempEmails FOR json auto 

Selecting Column Values From JSON Format in sql server:

For Example the below is the JSON format:

[{"gid":"0000036285","email":"tetsuya.ono@gmail.com,tetsuya.ono@gmail.com"},{"gid":"0000047244","email":"Miyuki.Yamamoto@gmail.com"}]
select JSON_Value(JSON_F52E2B61-18A1-11d1-B105-00805F49916B,'$.gid') as gid
JSON_Value(JSON_F52E2B61-18A1-11d1-B105-00805F49916B,'$.email') as email

;WITH CTE ([JSON_F52E2B61-18A1-11d1-B105-00805F49916B])
as
(
SELECT TOP 2 gid, email  FROM tempEmails FOR json auto
)
,CTE1 (JSONValue) AS
(
select [JSON_F52E2B61-18A1-11d1-B105-00805F49916B] JSONValue from cte
)
SELECT JSON_Value(JSONValue,'$[0].gid') as gid,
JSON_Value(JSONValue,'$[0].email') as email FROM CTE1

Read More »

Using SQL query Insert Data From CSV File in to the SQL server database

drop table #test1
CREATE TABLE #test1(
[GID] [nvarchar](MAX) NULL,
[Staff_Name] [nvarchar](MAX) NULL,
[E_mail_Address] [nvarchar](MAX) NULL,
[OrgLevel1] [nvarchar](MAX) NULL,
[OrgLevel2] [nvarchar](MAX) NULL,
[OrgLevel3] [nvarchar](MAX) NULL,
[OrgLevel4] [nvarchar](MAX) NULL,
[OrgLevel5] [nvarchar](MAX) NULL,
[OrgLevel6] [nvarchar](MAX) NULL,
[HeaOrDeputy] [nvarchar](MAX) NULL,
[RoleType] [nvarchar](MAX) NULL,
[OrgLevelId] [nvarchar] (MAX) NULL
)

--BULK INSERT #test1

--   FROM 'D:\Master.csv'

--BULK INSERT #test1
--FROM 'D:\Master.csv'
--WITH
--(
--    FIELDTERMINATOR = ' '           -- add this
--);


--BULK INSERT #test1
--FROM 'D:\Master.csv'
--WITH (
--FIELDTERMINATOR = ',',
--ROWTERMINATOR = '\n',
--ERRORFILE = 'D:\myRubbishData.log'
--);

--BULK
--INSERT #test1
-- FROM 'D:\tempApproverMst.txt'
--WITH
--(
-- FIRSTROW = 1,
-- FIELDTERMINATOR = ' ',
-- ROWTERMINATOR = '\n', --0x0a
-- Lastrow = 30000003
--)
--GO

INSERT INTO #test1 ([GID], [Staff_Name], [E_mail_Address], [PhoneN],OrgLevel1,OrgLevel2,OrgLevel3,OrgLevel4,OrgLevel5,OrgLevel6,HeaOrDeputy,OrgLevelId)
SELECT A.[GID], A.[Staff_Name], A.[E_mail_Address], A.[PhoneN],A.OrgLevel1,A.OrgLevel2,A.OrgLevel3,A.OrgLevel4,A.OrgLevel5,A.OrgLevel6,A.HeaOrDeputy,A.OrgLevelId
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=D:\Master.csv', 'select * from [TableName]') AS A;

SELECT * FROM #test1

Read More »

Capitalize only the first letter of each word of each sentence in SQL Server

BEGIN TRAN UPDATE T_MST_Skills SET Name =  stuff((select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
       from (select cast(replace((select Name as '*' for xml path('')), ' ', '') as xml).query('.')) as T1(X)
         cross apply T1.X.nodes('text()') as T2(X)
         cross apply (select T2.X.value('.', 'varchar(1000)')) as T3(V)
       for xml path(''), type
       ).value('text()[1]', 'varchar(1000)'), 1, 1, '') from T_MST_Skills where [Name] not like '%(%' and [Name] not like '%.%';

Rollback:

select Name,stuff((
       select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
       from (select cast(replace((select Name as '*' for xml path('')), ' ', '') as xml).query('.')) as T1(X)
         cross apply T1.X.nodes('text()') as T2(X)
         cross apply (select T2.X.value('.', 'varchar(1000)')) as T3(V)
       for xml path(''), type
       ).value('text()[1]', 'varchar(1000)'), 1, 1, '') as [Capitalize first letter only] from T_MST_Skills where [Name] not like '%(%' and [Name] not like '%.%';

Read More »

SQL Database Cloning (Clone an existing database to a new database) Create database replica

Duplicating SQL Database

I need to essentially copy all of these tables and the data and the constraints exactly from one database to another.

The simplest backup command would be:

BACKUP DATABASE [db-prod] TO DISK = 'D:\dbbackup\db-prod.bak' WITH INIT;
Now to restore this as a different database, you need to know the file names because it will try to put the same files in the same place. So if you run the following:

EXEC [db-prod].dbo.sp_helpfile;
You should see output that contains the names and paths of the data and log files. When you construct your restore, you'll need to use these, but replace the paths with the name of the new database, e.g.:

RESTORE DATABASE [db-local] FROM DISK = 'D:\dbbackup\db-prod.bak'

WITH MOVE 'db-prod' TO 'C:\Program Files\Microsoft SQL 

Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\db-local.mdf',

MOVE 'db-prod_log' TO 'C:\Program Files\Microsoft SQL 

Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\db-local_log.ldf';

You'll have to replace dbname and newname with your actual database names, and also some folder and C:\path_from_sp_helpfile_output\ with your actual paths. I can't get more specific in my answer unless I know what those are.

Of course if the clone target (in this case DB-B) already exists, you'll want to drop it:

USE [master];

GO

IF DB_ID('db-local') IS NOT NULL

BEGIN

ALTER DATABASE [db-local] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [db-local];

END

GO

Read More »

What is the use of static variable in C#? When to use it? Why can't I declare the static variable inside method?

Static variable in C#? static variable value is shared among all instances of that class.

A static variable shares the value of it among all instances of the class.

Example without declaring it static:

public class Variable
{
    public int i = 5;
    public void test()
    {
        i = i + 5;
        Console.WriteLine(i);
    }
}


public class Exercise
{
    static void Main()
    {
        Variable var = new Variable();
        var.test();
        Variable var1 = new Variable();
        var1.test();
        Console.ReadKey();
    }
}

Explanation: If you look at the above example, I just declare the int variable. When I run this code the output will be 10 and 10. Its simple.

Now let's look at the static variable here; I am declaring the variable as a static.

Example with static variable:

public class Variable
{
    public static int i = 5;
    public void test()
    {
        i = i + 5;
        Console.WriteLine(i);
    }
}


public class Exercise
{
    static void Main()
    {
        Variable var = new Variable();
        var.test();
        Variable var1 = new Variable();
        var1.test();
        Console.ReadKey();
    }
}

Now when I run above code, the output will be 10 and 15. So the static variable value is shared among all instances of that class.

Read More »