How do I UPDATE from a SELECT in SQL Server?

[Origin]: https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server

UPDATE
    Table_A
SET Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'
shareedit
Advertisements

Multiple rows to one comma-separated value [duplicate]

[Origin]: https://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value

I want to create a table valued function in SQL Server, which I want to return data in comma separated values.

For example table: tbl

ID | Value
---+-------
 1 | 100
 1 | 200
 1 | 300     
 1 | 400 

Now when I execute the query using the function Func1(value)

SELECT Func1(Value) 
FROM tbl 
WHERE ID = 1

Output that I want is: 100,200,300,400

shareimprove this question

Test Data

DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)

Query

SELECT ID
       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
       FROM @Table1 WHERE ID = t.ID
       FOR XML PATH(''), TYPE)
       .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID

Result Set

╔════╦═════════════════════╗
 ID      List_Output     
╠════╬═════════════════════╣
  1   100, 200, 300, 400 
╚════╩═════════════════════╝
shareimprove this answer

SQL Server Management Studio – tips for improving the TSQL coding process – Stack Overflow

[Origin]: https://stackoverflow.com/questions/101079/sql-server-management-studio-tips-for-improving-the-tsql-coding-process

community owned wiki Answer – feel free to edit or add comments:

Keyboard Shortcuts

  • F5CTRL + E or ALT + X – execute currently selected TSQL code
  • CTRL + R – show/hide Results Pane
  • CTRL + N – Open New Query Window
  • CTRL + L – Display query execution plan

Editing Shortcuts

  • CTRL + K + C and CTRL + K + U – comment/uncomment selected block of code (suggested by Unsliced)
  • CTRL + SHIFT + U and CTRL + SHIFT + L – changes selected text to UPPER/lower case
  • SHIFT + ALT + Selecting text – select/cut/copy/paste a rectangular block of text

Addons

Other Tips

  • Using comma prefix style (suggested by Cade Roux)
  • Using keyboard accelerators (suggested by kcrumley)

Useful Links

share

Sql Server ‘Saving changes is not permitted’ error ► Prevent saving changes that require table re-creation

[Origin]: https://stackoverflow.com/questions/6810425/sql-server-saving-changes-is-not-permitted-error-prevent-saving-changes-that

When I create a table in SQL Server and save it, if I try to edit the table design, like change a column type from int to real, I get this error:

Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can’t be recreated or enabled the option prevent saving changes that require the table to be re-created.

Why do I have to re-create the table? I just want to change a data type from smallint to real.

The table is empty, and I didn’t use it until now.

shareimprove this question

From Save (Not Permitted) Dialog Box on MSDN :

The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created.

The following actions might require a table to be re-created:

  • Adding a new column to the middle of the table
  • Dropping a column
  • Changing column nullability
  • Changing the order of the columns
  • Changing the data type of a column <<<<

To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

See Also Colt Kwong Blog Entry:
Saving changes is not permitted in SQL 2008 Management Studio

shareimprove this answer

Converting Comma Separated Value to Rows and Vice Versa in SQL Serve

[Origin]: http://www.databasejournal.com/features/mssql/converting-comma-separated-value-to-rows-and-vice-versa-in-sql-server.html

Posted May 21, 2015

Converting Comma Separated Value to Rows and Vice Versa in SQL Server

By Arshad Ali

Introduction

Often while reporting you will encounter a situation where you will have comma separated (or separated with some other character) values in a single column but you want to report them in rows whereas in some other cases you might have values in multiple rows and want them to be a single value separated by comma or some other character. In this article, I am going to demonstrate how you can write queries in SQL Server to handle these scenarios quickly.

Converting Comma Separated Value to Rows

For converting a comma separated value to rows, I have written a user defined function to return a table with values in rows. It takes comma separated values as the input parameter, iterates through it as long as it finds a comma in the value, takes each value before the comma, inserts into a table and finally returns the inserted data from that table.

It makes use of CHARINDEX inbuilt function to search for the existence of commas in the input parameter and returns the starting position. It does that as long as the position is greater than zero. Then it makes use of the STUFF inbuilt function to replace a part of the main input parameter (which has already been inserted into table) with a zero length string; effectively removing the value before the comma, which has already been extracted from the main input parameter and inserted into the table. It also uses LTRIM and RTRIM functions to remove any extra spaces from the beginning or end of the value if there are any.

CREATE FUNCTION dbo.BreakStringIntoRows (@CommadelimitedString   varchar(1000))
RETURNS   @Result TABLE (Column1   VARCHAR(100))
AS
BEGIN
        DECLARE @IntLocation INT
        WHILE (CHARINDEX(',',    @CommadelimitedString, 0) &gt; 0)
        BEGIN
              SET @IntLocation =   CHARINDEX(',',    @CommadelimitedString, 0)      
              INSERT INTO   @Result (Column1)
              --LTRIM and RTRIM to ensure blank spaces are   removed
              SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString,   0, @IntLocation)))   
              SET @CommadelimitedString = STUFF(@CommadelimitedString,   1, @IntLocation,   '') 
        END
        INSERT INTO   @Result (Column1)
        SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed
        RETURN 
END
GO
 
--Using the UDF to convert comma separated values into rows
SELECT * FROM dbo.BreakStringIntoRows('Apple,Banana,Orange')
SELECT * FROM dbo.BreakStringIntoRows('Apple   ,    Banana,    Orange')

Result Screenshot

Converting Rows to Comma Separated Values

Before I go into detail and demonstrate converting rows into comma separated values, let’s first create a table and load some data with the script given below and as shown in the image.

CREATE TABLE Fruits
(
Name   VARCHAR(50)
)
GO
INSERT   Fruits(Name) VALUES ('Apple')
INSERT   Fruits(Name) VALUES ('Banana')
INSERT   Fruits(Name) VALUES ('Orange')
GO
SELECT * FROM Fruits
GO

Result Screenshot

Now we have values in rows and to convert them to a single comma separated value, you can use the script below, which uses the COALESCE inbuilt function.

DECLARE   @ConcatString VARCHAR(4000)
SELECT   @ConcatString = COALESCE(@ConcatString + ', ', '') + Name FROM Fruits 
SELECT   @ConcatString AS Fruits
GO

Result Screenshot

The COALESCE function takes a list of parameters, separated by commas, evaluates them and returns the value of the first of its input parameters that is not NULL.

Though COALESCE and ISNULL functions have a similar purpose, they can behave differently. For example, ISNULL function is evaluated only once whereas the input values for the COALESCE function can be evaluated multiple times or until it reaches to the first not-NULL value to return.

In the above example, I have considered the comma as a character to separate values; though you can modify the above script to have any other character like $ or |, etc. for separating values.

Conclusion

In this article, I talked about how you can write a query using inbuilt SQL Server functions to convert comma separated values into rows or convert multiple rows values to be a single value separated by a comma. I have demonstrated using a comma for separating values but you can use any other character for value separation.

Resources

CONCAT and STUFF Functions in SQL Server 2012

COALESCE function

CHARINDEX function

See all articles by Arshad Ali

Getting an odd error, SQL Server query using `WITH` clause

[Origin]: http://stackoverflow.com/questions/2746916/getting-an-odd-error-sql-server-query-using-with-clause

always use with statement like ;WITH then you’ll never get this error. The WITH command required a ; between it any any previous command, by always using ;WITH you’ll never have to remember to do this.

see WITH common_table_expression (Transact-SQL), from the section Guidelines for Creating and Using Common Table Expressions:

When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

shareimprove this answer

JSON Data (SQL Server)

[Origin]: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

JSON is a popular textual data format used for exchanging data in modern web and mobile applications. JSON is also used for storing unstructured data in log files or NoSQL databases like Microsoft Azure DocumentDB. Many REST web services return results formatted as JSON text or accept data formatted as JSON. For example, most Azure services such as Azure Search, Azure Storage, and Azure DocumentDb have REST endpoints that return or consume JSON. JSON is also the main format for exchanging data between web pages and web servers using AJAX calls.

Here’s an example of JSON text:

[{
    "name": "John",
    "skills": ["SQL", "C#", "Azure"]
}, {
    "name": "Jane",
    "surname": "Doe"
}]

SQL Server provides built-in functions and operators that let you do the following things with JSON text.

  • Parse JSON text and read or modify values.
  • Transform arrays of JSON objects into table format.
  • Run any Transact-SQL query on the converted JSON objects.
  • Format the results of Transact-SQL queries in JSON format.

    Overview of built-in JSON support

Key JSON capabilities of SQL Server

Here’s more info about the key capabilities that SQL Server provides with its built-in JSON support.

Extract values from JSON text and use them in queries

If you have JSON text that’s stored in database tables, you can use built-in functions to read or modify values in the JSON text.

  • Use the JSON_VALUE function to extract a scalar value from a JSON string.
  • Use JSON_QUERY to extract an object or an array from a JSON string.
  • Use the ISJSON function to test whether a string contains valid JSON.
  • Use the JSON_MODIFY function to change a value in a JSON string.

Example

In the following example, the query uses both relational and JSON data (stored in the jsonCol column) from a table:

SELECT Name,Surname,
 JSON_VALUE(jsonCol,'$.info.address.PostCode') AS PostCode,
 JSON_VALUE(jsonCol,'$.info.address."Address Line 1"')+' '
  +JSON_VALUE(jsonCol,'$.info.address."Address Line 2"') AS Address,
 JSON_QUERY(jsonCol,'$.info.skills') AS Skills
FROM PeopleCollection
WHERE ISJSON(jsonCol)&gt;0
 AND JSON_VALUE(jsonCol,'$.info.address.Town')='Belgrade'
 AND Status='Active'
ORDER BY JSON_VALUE(jsonCol,'$.info.address.PostCode')

Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. You can use values from JSON text in any part of a Transact-SQL query (including WHERE, ORDER BY, or GROUP BY clauses, window aggregates, and so on). JSON functions use JavaScript-like syntax for referencing values inside JSON text.

For more info, see Validate, Query, and Change JSON Data with Built-in Functions (SQL Server), JSON_VALUE (Transact-SQL), and JSON_QUERY (Transact-SQL).

Change JSON values

If you have to modify parts of JSON text, you can use the JSON_MODIFY function to update the value of a property in a JSON string and return the updated JSON string. The following example updates the value of a property in a variable that contains JSON.

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=JSON_MODIFY(@jsonInfo,'$.info.address[0].town','London') 

Convert JSON collections to a rowset

You don’t need a custom query language to query JSON in SQL Server. To query JSON data, you can use standard T-SQL. If you have to create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more info, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

The following example calls OPENJSON and transforms the array of objects stored in the @json variable to a rowset that can be queried with a standard SQL SELECT statement:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob')  

Results

id firstName lastName age dateOfBirth
2 John Smith 25
5 Jane Smith 2005-11-04T12:00:00

OPENJSON transforms the array of JSON objects into a table in which each object is represented as one row, and key/value pairs are returned as cells. The output observes the following rules.

  • OPENJSON converts JSON values to the types specified in the WITH clause.
  • OPENJSON can handle both flat key/value pairs and nested, hierarchically organized objects.
  • You don’t have to return all the fields contained in the JSON text.
  • OPENJSON returns NULL values if JSON values don’t exist.
  • You can optionally specify a path after the type specification to reference a nested property or to reference a property by a different name.
  • The optional strict prefix in the path specifies that values for the specified properties must exist in the JSON text.

For more info, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server) and OPENJSON (Transact-SQL).

Convert SQL Server data to JSON or export JSON

Format SQL Server data or the results of SQL queries as JSON by adding the FOR JSON clause to a SELECT statement. Use FOR JSON to delegate the formatting of JSON output from your client applications to SQL Server. For more info, see Format Query Results as JSON with FOR JSON (SQL Server).

The following example uses PATH mode with the FOR JSON clause.

SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth as dob  
FROM People  
FOR JSON PATH  

The FOR JSON clause formats SQL results as JSON text that can be provided to any app that understands JSON. The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results.

Results

[{
    "id": 2,
    "info": {
        "name": "John",
        "surname": "Smith"
    },
    "age": 25
}, {
    "id": 5,
    "info": {
        "name": "Jane",
        "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
}] 

For more info, see Format Query Results as JSON with FOR JSON (SQL Server) and FOR Clause (Transact-SQL).

Combine relational and JSON data

SQL Server provides a hybrid model for storing and processing both relational and JSON data using standard Transact-SQL language. You can organize collections of your JSON documents in tables, establish relationships between them, combine strongly-typed scalar columns stored in tables with flexible key/value pairs stored in JSON columns, and query both scalar and JSON values in one or more tables using full Transact-SQL.

JSON text is typically stored in varchar or nvarchar columns and is indexed as plain text. Any SQL Server feature or component that supports text supports JSON, so there are almost no constraints on interaction between JSON and other SQL Server features. You can store JSON in In-memory or Temporal tables, you can apply Row-Level Security predicates on JSON text, and so on.

If you have pure JSON workloads where you want to use a query language that’s customized for the processing of JSON documents, consider Microsoft Azure DocumentDB.

Here are some use cases that show how you can use the built-in JSON support in SQL Server.

Return data from a SQL Server table formatted as JSON

If you have a web service that takes data from the database layer and returns it in JSON format, or JavaScript frameworks or libraries that accept data formatted as JSON, you can format JSON output directly in a SQL query. Instead of writing code or including a library to convert tabular query results and then serialize objects to JSON format, you can use FOR JSON to delegate the JSON formatting to SQL Server.

For example, you might want to generate JSON output that’s compliant with the OData specification. The web service expects a request and response in the following format.

  • Request: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName
  • Response: {"@odata.context":"http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}

    This OData URL represents a request for the ProductID and ProductName columns for the product with id 1. You can use FOR JSON to format the output as expected in SQL Server.

SELECT 'http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity'
 AS '@odata.context',   
 ProductID, Name as ProductName   
FROM Production.Product  
WHERE ProductID = 1  
FOR JSON AUTO  

The output of this query is JSON text that’s fully compliant with OData spec. Formatting and escaping are handled by SQL Server. SQL Server can also format query results in any format such as OData JSON or GeoJSON – for more info, see Returning spatial data in GeoJSON format.

Analyze JSON data with SQL queries

If you have to filter or aggregate JSON data for reporting purposes, you can use OPENJSON to transform JSON to relational format. Then use standard Transact-SQL and built-in functions to prepare the reports.

SELECT Tab.Id, SalesOrderJsonData.Customer, SalesOrderJsonData.Date  
FROM   SalesOrderRecord AS Tab  
          CROSS APPLY  
     OPENJSON (Tab.json, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData  
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'  
ORDER BY JSON_VALUE(Tab.json, '$.Group'), Tab.DateModified  

Both standard table columns and values from JSON text can be used in the same query. You can add indexes on the JSON_VALUE(Tab.json, '$.Status') expression to improve performance of query. For more info, see Index JSON data.

Import JSON data into SQL Server tables

If you have to load JSON data from an external service into SQL Server, you can use OPENJSON to import the data into SQL Server instead of parsing the data in the application layer.

DECLARE @jsonVariable NVARCHAR(MAX)

SET @jsonVariable = N'[  
        {  
          "Order": {  
            "Number":"SO43659",  
            "Date":"2011-05-31T00:00:00"  
          },  
          "AccountNumber":"AW29825",  
          "Item": {  
            "Price":2024.9940,  
            "Quantity":1  
          }  
        },  
        {  
          "Order": {  
            "Number":"SO43661",  
            "Date":"2011-06-01T00:00:00"  
          },  
          "AccountNumber":"AW73565",  
          "Item": {  
            "Price":2024.9940,  
            "Quantity":3  
          }  
       }  
  ]'

INSERT INTO SalesReport  
SELECT SalesOrderJsonData.*  
FROM OPENJSON (@jsonVariable, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData;

The content of the JSON variable can be provided by an external REST service, sent as a parameter from a client-side JavaScript framework, or loaded from external files. You can easily insert, update or merge results from JSON text into a SQL Server table. For more info about this scenario, see the following blog posts.

Load JSON files into SQL Server

Information stored in files can be formatted as standard JSON or Line-Delimited JSON. SQL Server can import the contents of JSON files, parse it using the OPENJSON or JSON_VALUE functions, and load it into tables.

  • If your JSON documents are stored in local files, on shared network drives, or in Azure File Storage locations that can be accessed by SQL Server, you can use bulk import to load your JSON data into SQL Server. For more info about this scenario, see Importing JSON files into SQL Server using OPENROWSET (BULK).
  • If your line-delimited JSON files are stored in Azure Blob Storage or the Hadoop file system, you can use Polybase to load JSON text, parse it in Transact-SQL code, and load it into tables.

Test drive built-in JSON support

Test drive built-in JSON support with the AdventureWorks sample database. To get the AdventureWorks sample database, download at least the database file and the samples and scripts file from here. After you restore the sample database to an instance of SQL Server 2016, unzip the samples file and open the “JSON Sample Queries procedures views and indexes.sql” file from the JSON folder. Run the scripts in this file to reformat some existing data as JSON data, run sample queries and reports over the JSON data, index the JSON data, and import and export JSON.

Here’s what you can do with the scripts included in the file.

  1. Denormalize the existing schema to create columns of JSON data.
    1. Store information from SalesReasons, SalesOrderDetails, SalesPerson, Customer, and other tables that contain information related to sales order into JSON columns in the SalesOrder_json table.
    2. Store information from EmailAddresses/PersonPhone tables into the Person_json table as arrays of JSON objects.
  2. Create procedures and views that query JSON data.
  3. Index JSON data – create indexes on JSON properties and full-text indexes.
  4. Import and export JSON – create and run procedures that export the content of the Person and the SalesOrder tables as JSON results, and import and update the Person and the SalesOrder tables using JSON input.
  5. Run query examples – run some queries that call the stored procedures and views created in steps 2 and 4.
  6. Clean up scripts – don’t run this part if you want to keep the stored procedures and views created in steps 2 and 4.

Learn more about built-in JSON support

Topics in this section

Format Query Results as JSON with FOR JSON (SQL Server)
Use the FOR JSON clause to delegate the formatting of JSON output from your client applications to SQL Server.

Convert JSON Data to Rows and Columns with OPENJSON (SQL Server)
Use OPENJSON to import JSON data into SQL Server, or to convert JSON data into relational format for an app or service that can’t currently consume JSON directly, such as SQL Server Integration Services.

Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)
Use these built-in functions to validate JSON text and to extract a scalar value, an object, or an array.

JSON Path Expressions (SQL Server)
Use a path expression to specify the JSON text that you want to use.

Index JSON data
Use computed columns to create collation-aware indexes over properties in JSON documents.

Solve common issues with JSON in SQL Server
Find answers to some common questions about the built-in JSON support in SQL Server.

Microsoft blog posts

Reference topics