Archive
SQL SERVER | Merge Statement | The insert column cannot contain multi-part identifiers
Other day while executing SQL ‘MERGE’ statement on 2 tables, I ran in to following error.
The insert column list used in the MERGE statement cannot contain multi-part identifiers
Before we understand the reason and fix for this error, lets get the basics of ‘MERGE’ statement.
‘MERGE’ statement:
- ‘MERGE’ statement can be used, if you have 2 SQL tables and to synchronize both two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
- ‘MERGE’ runs insert, update, or delete operations on a target table from the results of a join with a source table.
Step by Step using ‘MERGE’:
Lets understand how MERGE works with an example.
- Create 2 tables, ‘Cust_Source’ and ‘Cust_Trg’. Column ‘CID’ acts as joining column between 2 tables.

- Lets use MERGE and sync ‘Cust_Trg’ using ‘Cust_Source’ table by covering following points:
- Update the matching records in ‘Cust_Trg’ using the ‘Cust_Source’ rows.
- Create (i.e., INSERT) the unavailable records in ‘Cust_Trg’ from ‘Cust_Source’.
- Delete the excessive records from ‘Cust_Trg’ by comparing with ‘Cust_Source’ rows.

Query:
MERGE [dbo].[Cust_Trg] AS Trg
USING [dbo].[Cust_Source] AS Src
ON Trg.CID=Src.CID
WHEN MATCHED — Update the Target record fields from Source values.
THEN UPDATE SET Trg.Salary=Src.Salary,Trg.WorkExp=Src.WorkExp
WHEN NOT MATCHED BY TARGET — INSERT the record in Target as its not part of Target yet.
THEN INSERT (CID, [Name], Salary, WorkExp)
VALUES (CID, [Name], Salary, WorkExp)
WHEN NOT MATCHED BY SOURCE — Delete the record from Target as its not part of Source.
THEN DELETE
OUTPUT $action, –INSERT/UPDATE/DELETE
DELETED.CID AS TargetCID,
DELETED.Name AS TargetName,
DELETED.Salary AS TargetSalary,
DELETED.WorkExp AS TargetWorkExp,
INSERTED.CID AS SourceCID,
INSERTED.Name AS SourceName,
INSERTED.Salary AS SourceSalary,
INSERTED.WorkExp AS SourceWorkExp;
SELECT @@ROWCOUNT ‘No ofAffected rows’
- Execute the query and you would get results as below.
Issue encountered with MERGE:
- When I ran my initial MERGE query, ran in to following exception.

- Issue was due to the usage of ‘Alias’ in INSERT statement (i.e., Trg.Salary, etc.)
- Remove ‘Alias’ and rerun the query should solve the issue.
Notes:
- MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it doesn’t exist, or updating a row if it matches.
- When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements.
đ
Working with JSON data in SQL Server
In this article, lets see how to work with JSON data in SQL Server using SSMS. Following features will be covered in this article.
- Format SQL table data in JSON format (Using FOR JSON PATH clause)
- Store JSON data in SQL table
- Query JSON data in SQL server (Using JSON_VALUE and JSON_QUERY functions)
- Update JSON data in SQL table (Using JSON_MODIFY function)
- Convert JSON data in to SQL table format (Using OPENJSON function)
- Validate JSON data format (Using ISJSON function)
Format SQL table data in JSON format:
Lets see how to format SQL query tabular results in JSON format using FOR JSON clause.
- Create a simple ‘Employee’ table in SQL server.
- When you query ‘Employee’ table you would get the result in tabular format.

- Lets convert the result set in to JSON format using FOR JSON clause.
-- JSON PATH
select *
from Employee
FOR JSON PATH
- Use WITHOUT_ARRAY_WRAPPER to get a single JSON object instead of an array. Use this option if the result of query is single object.
-- JSON PATH and WITHOUT_ARRAY_WRAPPER
select *
from Employee
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

- We can also form inline JSON object format. Lets see how to show ‘Address’ as a separate object.
-- JSON PATH, WITHOUT_ARRAY_WRAPPER and 'Address' as seperate object
select
ID,Name,
Street1 as [Address.Street1], Street2 as [Address.Street2],City as [Address.City], ZipCode as [Address.ZipCode]
from Employee
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Store JSON data in SQL table:
- JSON is a textual format that can be used like any other string type in SQL Database. JSON data can be stored as a standard NVARCHAR.
- In the example below SQL table has ‘JSONData’ column which is NVARCHAR(MAX).

Query JSON data in SQL server:
- Lets query the JSON data using JSON_VALUE and JSON_QUERY.
- JSON_VALUE:
- JSON_VALUE function extracts a value from JSON text stored in the SQL column.
- The extracted value can be used in any part of SQL query.
- JSON_QUERY:
- JSON_QUERY function extracts complex sub-object such as arrays or objects that are placed in JSON text.
- Example ‘Tags’ column in above SQL Table.

Update JSON data in SQL table:
- JSON_MODIFY function can be used to update the JSON text without re-parsing the entire structure.
- Below is the query to update the ‘Price’ of all ‘Yellow’ color products to 1000.

Update Products
set JSONData = JSON_MODIFY(JSONData,'$.Price',1000)
where
JSON_VALUE(JSONData,'$.Color') = 'Yellow'
- Post query execution, ‘Price’ updated to 1000 as shown below.

Convert JSON data in to SQL table format
- Using OPENJSON function JSON data can be transformed in to SQL table format.
- OPENJSON
- Its a table-value function that parses JSON text, locates an array of JSON objects, iterates through the elements of the array, and returns one row in the output result for each element of the array.

Validate JSON data format
- Since JSON is stored in a standard text, there are no guarantees that the values stored in text columns are properly formatted using ISJSON function.
- ISJSON function returns the value 1 if the data properly formatted in JSON.
ISJSON(SQL_Cell) > 0
đ
SQL Server- Troubleshoot ‘String or binary data would be truncated’ error
In one of my Data Migration requirement, I had to join data from multiple tables and move to a single table. Requirement is complex as data is around 20 GB and table has around 300 columns.
So, I built a Stored Procedure to join tables and move data to single table, the query ran for an hour and finally failed with below error:
String or binary data would be truncated.
Reason:
- Length of the data I was trying to insert to is more than the defined column size in target table.
Fix:
- Identifying the problematic column was difficult as my source tables had 300 columns.
- To trouble the issue, I ran a query on my source tables to get max data length of all columns, which helped me to identify the mismatched column.
- Below query helped me to identify the maximum data length of source table columns
DECLARE @SQLStatement VARCHAR(MAX);
DECLARE @TableName sysname;
–Set your table name here
SET @TableName = ‘Product‘;
SET @SQLStatement = ”;SELECT @SQLStatement = @SQLStatement + ‘SELECT ‘ + QUOTENAME(cols.name, ””) + ‘ AS ”Column Name”, ‘ + QUOTENAME(types.name, ””) + ‘ AS ”Data Type”, ‘ +
QUOTENAME(cols.max_length, ””) + ‘ AS ”Defined Length”, MAX(DATALENGTH(‘ + QUOTENAME(cols.name) + ‘)) AS ”Max Data Length” FROM ‘+@TableName+ char(10) +’ UNION ‘
FROM
SYS.COLUMNS cols
JOIN
SYS.TYPES types
ON
types.system_type_id = cols.system_type_id and types.name != ‘sysname’
WHERE
cols.OBJECT_ID = OBJECT_ID(@TableName);SET @SQLStatement = LEFT(@SQLStatement, LEN(@SQLStatement)-6)
— Execute SQL statement
EXEC(@SQLStatement)
- To simplify the understanding I created a ‘Product’ table with 4 columns and added few rows
- Execute the query and it lists out ‘Column Name’,’Data Type’, ‘Defined Column Length’ and ‘Max Data Length’.
- In the below result, my ‘Description’ column’s max data length is 142
đ
SQL Server- Drop If Exists (DIE) Objects
Other day, I generated my Database SQL Script including script to create Tables, Procedures, Types, Views etc., using Tasks -> Generate Scripts… option which I had to deploy on my test instance.
‘Generate Scripts’ generate the SQL script with ‘CREATE’ statement (i.e., It will have ‘CREATE Table’, ‘CREATE PROCEDURE’ etc…)
Issue is, what if I already had Database script ran previously and want to only update the Database objects, you would get script error if you run the generated SQL Script.
Solution is, we need to check existence of the object (i.e.,Table, Procedure, View etc.) and Drop if exists the object and run the CREATE statement generated using ‘Generate Scripts…’ option.
Drop If Exists (DIE) statement in SQL Server 2016:
From SQL Server 2016 CTP3 we can use DIE Â statements to check if the Object exists and Drop before run the CREATE statements.
- Dropping ‘Procedure’ if exists:
DROP PROCEDURE IF EXISTS [Stored_Procedure_Name]
- Dropping ‘Table’ if exists:
DROP TABLE IF EXISTS [Table_Name]
- Dropping ‘Type’ (i.e.,User Defined Table Type/User Defined Data Types) if exists:
DROP TYPE IF EXISTS [Type_Name]
- Delete Table’s COLUMN if Exists.
ALTER TABLE DROP COLUMNÂ IF EXISTS
- Delete Table’s CONSTRAINT if exists
ALTER TABLE DROP CONSTRAINTÂ IF EXISTS
Below are the list of Objects that can be included in DIE statements.
Note:
- If your SQL version is before 2016 below is the syntax
IF OBJECT_ID(‘Table_Name‘, ‘U’) IS NOT NULL
DROP TABLE Table_Name;
- You can also use ‘Check for object existence‘ and ‘Script DROP and CREATE‘ options while ‘Generating Script’ which delete and recreate objects.
đ
SqlError: The backup set holds a backup of a database other than the existing database
I got below error, when I was trying restore a database from Azure Blob Storage to my local SQL server using ‘SQL Server Management Studio (SSMS)’.
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘{DB_Name}’ database. (Microsoft.SqlServer.SmoExtended)
Reason:
- I created a new Database (DB) first and tried to restore the DB from Azure blob to this new DB.
Fix:
Below approach solved the issue
- Don’t create a new Database before hand.
- Right click ‘Databases’ and select ‘Restore Database’
- Pick your ‘Source’ Database
- In the ‘Destination’ section, provide a new Database name as highlighted below
- Click ‘OK’ to complete the Restore process.
If you want to create a Blank Database first and restore.
- Set below settings in ‘Options’ tab of ‘Restore Database’ window
- Pick existing Database
- Click ‘OK’ to complete the Restore process.
đ
[Step by Step] Connecting to Azure SQL Server using OLEDB Connection from SSIS
Connecting to Azure SQL Server from SSIS using âOLEDB Connection managerâ Â is not a straight forward way, if you are to connect using your âAzure Accountâ credentials.
The only way to connect using âAzure Accountâ is by using âADO.NET connectionâ manager.
But then, how to connect to Azure SQL using OLEDB? Answer is by using âSQL Userâ. And yes, we need to create âSQL Userâ on the Azure DB which you are connecting to and use the same in SSIS OLEDB.
Below are the steps to create âSQL Userâ and use that to connect to âAzure SQL Serverâ from SSIS OLEDB.
Create a âSQL Loginâ on Azure SQL Server:
- Connect to Azure SQL Server using SQL Server Management Studio (SSMS) using the Azure Account. âAzure Accountâ must be Administrator to âAzure SQL Serverâ
- Select âMasterâ Database and open a âNew Queryâ window.
- Note that, âSQL Loginâ creation query must only run on âMasterâ database
- Create a âLogin Accountâ using below query. In my query I am creating a Login Account by name âMyLoginâ
Create a âSQL Userâ on required Databases:
- Post creation of âSQL Loginâ, now we have to create âSQL Userâ account against all the Databases, which you want access from SSIS OLEDB.
- From the SSMS -> Object Explorer, select the Database and open a âNew Queryâ window.
- Create a âSQL Userâ using below query. In my query I am creating a SQL User by name âMyUserâ
- Add âdb_datareaderâ and âdb_datawriterâ roles to the âMyLoginâ Login Account using below queries.
- Note: You can combine and run ‘Create SQL Userâ and âGrant Data Reader and Writer Rolesâ queries together.
- Also, run ‘Create SQL Userâ and âGrant Data Reader and Writer Rolesâ queries against âMasterâ Database as well.
- Refer this article for list of ‘Roles’
Connect to Azure SQL from SSIS OLEDB:
As we have âSQL Userâ created and granted access against the required databases, to connect to Azure SQL from OLEDB.
- Open the âOLEDB Connection Managerâ
- Set âServer nameâ to âAzure SQL Serverâ name
- Set âAuthenticationâ to âSQL Server Authenticationâ
- Set âUser nameâ and âPasswordâ to âSQL Userâ credentials created in above section
đ
SQL Server Installation – VS Shell installation has failed with exit code 1638
I was getting below error during the SQL server 2017 installation.
VS Shell installation has failed with exit code 1638
Reason:
- Exit code 1638 denotes “Another version of this program is already installed”.
- In my case, I already had Visual Studio 2017 installed on my machine and there was conflict due to “Microsoft Visual C++ 2017 Redistributable” components which got installed with Visual Studio 2017.
Fix:
This issue was annoying and had to spend couple hours to find the actual reason and fix. Below fix worked in my case.
- Uninstall Visual Studio 2017
- Go to Control Panel and uninstall below ‘Microsoft Visual C++ 2017 Redistributable‘ components
- Restart the machine
- Try installing SQL Server
- Proceed with Visual Studio 2017 installation
đ
C# – SQL – Bulk Insert records from Data Table and CSV
In my previous article I provided the steps to update records in single transaction using SQL âTable-Valued Parameter Typesâ.
In this article, lets see an easy way to bulk insert the records using SQLBulkCopy
What is SQLBulkCopy:
- SQLBulkCopy class comes with ‘System.Data’ namespace and can be used to write/Insert data to SQL Server tables.
- Though there are other ways to load data into a SQL Server table (i.e., INSERT statements, for example) but ‘SQLBulkCopy’ offers a significant performance advantage.
Below are the steps to use SQLBulkCopy with the combination of ‘Data Table’
Steps:
To simplify the explanation, I am taking a âStudentâ table with 3 columns.
- Create a ‘Student’ table.
- In C# console application, prepare a ‘Data Table’ with the structure similar to ‘Student’ SQL table.
tableStudents = new DataTable(âStudentâ);
tableStudents.Columns.Add(âStudentIDâ, typeof(string));
tableStudents.Columns.Add(âStudentNameâ, typeof(string));
tableStudents.Columns.Add(âCityâ, typeof(string));// Add âStudentâ data as Rows to the Data Table
DataRow rowStudent = tableStudents.NewRow();
rowStudent[âStudentIDâ] = âRJ001â
rowStudent[âStudentNameâ] = âRajeevâ;
rowStudent[âCityâ] = âHydâ;//Add the Data Row to Table
tableStudents.Rows.Add(rowStudent);
- Instantiate and execute the ‘SQLBulkCopy’ by passing the Data Table
using (SqlBulkCopy bulkCopy = new SqlBulkCopy({SQLConnection}, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null))
{
bulkCopy.DestinationTableName = “Student“; // Your SQL Table name//Optional mappings. Not required, if your ‘Data Table’ column names match with ‘SQL Table’ column names
bulkCopy.ColumnMappings.Add(“StudentID”, “StudentID”);
bulkCopy.ColumnMappings.Add(“StudentName”, “StudentName”);
bulkCopy.ColumnMappings.Add(“City”, “City”);
bulkCopy.WriteToServer(tableStudents);
isSuccuss = true;
}
- Execute the code which inserts rows from ‘Data Table’ to ‘Student’ SQL table.
Now lets see how to insert the CSV data to SQL table with query.
Note: You can also do the same with SQL Import Wizard
Bulk insert records from CSV to SQL Table:
- Assume that you have data available in a CSV file and want to insert them as records in SQL Table.
- Below is the query
Bulk Insert {SQL Table Name}
from ‘CSV File Path’
with
(rowterminator=’\n’,fieldterminator=’,’)
C# – Bulk Update Records – Pass Data Table to SQL Stored Procedure
I was building a console application to transact with large set of data in a SQL table and got a requirement to bulk update records.
Below are the options I got.
Option 1:
- Loop through the records in Console and make SQL update call for each record.
- This is a tedious and non-scalable for huge volumes data, as we need to make one SQL server transaction for each record from client (i.e, Console).
Option 2:
- Populate the records to update as rows in a ‘Data Table’ and pass it to a Stored Procedure, and have the Update statement in Stored Procedure.
- This is optimal as the heavy SQL updates are off loaded from Console to SQL server.
Lets see how to implement options 2.
To make the Stored Procedure accept Data Table, we need to define “Table-Valued Parameter Types” in SQL first.
What is a ‘Table-Valued Parameter Type”:
- Table-valued parameters are based on strongly-typed table structures that are defined by using SQL CREATE TYPE statements.
- You need create a “Table-Valued Type” (i.e.,StudentTableType from below scenario) with the structure similar to your Table (i.e., Student table from below scenario).
Steps to implement Option 2:
To simplify the explanation, I am taking a ‘Student’ table with 3 columns.
- Create ‘Student’ Table
- Create a ‘User-Defined Table Type’ (i.e., ‘StudentTableType‘) with ‘Student’ columns.
- Note: You don’t need to include all columns. Add columns only you need to update.
- Create a Stored Procedure with StudentTableType as Parameter
- In the Console, populate a ‘Data Table’ with ‘Student’ records as ‘Data Row’
tableStudents = new DataTable(“Student”);
tableStudents.Columns.Add(“StudentID”, typeof(string));
tableStudents.Columns.Add(“StudentName”, typeof(string));
tableStudents.Columns.Add(“City”, typeof(string));// Add ‘Student’ data as Rows to the Data Table
DataRow rowStudent = tableStudents.NewRow();
rowStudent[“StudentID”] = “RJ001”
rowStudent[“StudentName”] = “Rajeev”;
rowStudent[“City”] = “Hyd”;//Add the Data Row to Table
tableStudents.Rows.Add(rowStudent);
- Execute the Stored Procedure from Console by Passing ‘DataTable’ (i.e.,tableStudents) as Parameter
using (SqlConnection connection = ConnectionManager.SQLConnection)
{
SqlCommand cmdUpdateStudents = new SqlCommand(“BulkUpdateStudents”, connection)
{
CommandType = CommandType.StoredProcedure
};SqlParameter tvpParam = cmdUpdateStudents.Parameters.AddWithValue(“@tvpStudents”, tableStudents);
tvpParam.SqlDbType = SqlDbType.Structured;cmdUpdateStudents.ExecuteNonQuery();
}