Questions tagged [t-sql]

T-SQL, short for Transact Structured Query Language, is an advanced SQL functionality supported by Sybase ASE and Microsoft SQL Server. Please refrain from using this tag for MySQL, PostgreSql, or Oracle related queries. Also, note that SQL code written with LINQ will not fall under this category. This tag is specifically intended for advanced SQL programming utilizing Microsoft SQL Server.

Tips for avoiding fields containing the symbol "$" in their name

Is it possible to query a JSON document that contains field names with special characters like "$id"? SELECT [ID] FROM OPENJSON( '[{"$id":42},{"$id":43}]', '$' ) WITH ([ID] NVARCHAR(25) '$.$id') WHERE ID = ...

sqljsonsql-servert-sql

Put JSON data into SQL database table

I have the SQL Table 1 shown below: id name gender age country ambition 1 Peter Male 20 Italy Doctor 2 Angeli Female 30 Australia Lawyer I need to insert data into another table using a similar approach. Here is how I want the output in SQL Tabl ...

sqljsonsql-servert-sql

Extracting an array of integers from JSON using SQL Server 2016

Upon receiving a valid JSON string from the client side, which contains an array of integer values: declare @JSON nvarchar(max) = N'{"Comments": "test", "Markets": [3, 151]}' The question arises on how to accurately select the market IDs. When attemptin ...

jsonsql-servert-sql

Converting Basic JSON Data into an SQL SELECT Query

Presented is the subsequent JSON data: DECLARE @json NVARCHAR(MAX) SET @json = N'[ {"@odata.context":"http://www.example.com","value":[ {"financialmovements_ID":1,"Data":"2020-02-10T00:00:00Z","ES":"E","Descri\u00e7\u00e3o":"FIV-005 3& ...

sqljsonazuret-sql

Unraveling JSON in TSQL

I am having an issue with my JSON data that contains square brackets in the key. When I use OpenJSON to parse it, I encounter an error related to the square brackets. Can you please assist me with this? DECLARE @Json VARCHAR(MAX) SET @Json = '{"[key ...

jsont-sql

Making changes to a specific JSON value within a JSON Array in SQL

I am looking to make changes to an existing JSON value within a JSON array. I have the ability to add a new JSON object to the array using JSON_MODIFY. For example, if my JSON looks like this: [{"id":"101","name":"John"}, {"id":"102","name":"peter"}] How ...

sqljsonsql-servert-sql

Retrieve numerous rows by utilizing the forthcoming SQL Server TSQL command for extracting data from JSON format

I'm attempting to fetch multiple rows using the SQL Server 2017 TSQL code from a JSON object. DECLARE @json NVARCHAR(MAX); SET @json = N'{ "DateCreated": "2020-08-02T16:04:59.3558001+01:00", "Name": "Bolts", &q ...

sqlarraysjsonsql-servert-sql

Issue with storing a SQL time(7) field in a database using Node.js

I am currently utilizing the nodemssql package to interact with a SQL Server Azure database. Field time(7) > RecurrenceType.columns.add('StartTime', mssql.Time(7)); > RecurrenceType.columns.add('EndTime', mssql.Time(7)); Values Start time ...

sql-servernode.jst-sqlsql-server-2016node-mssql

Transform rows or table records into JSON documents within SQL Server

Here is some sample input data for your reference: if object_id('tempdb.dbo.#store_data') is not null drop table #store_data create table #store_data ([key] nvarchar(max),[value] nvarchar(max), storeid varchar(100) ) INSERT INTO #store_data VALUES ('sid ...

sqljsonsql-servert-sqlsql-server-2017