Tag Archives: sql

SQL Collation conflict

Posted on by Standard Respond

While attempting to copy  records from older database backup

update a
set latitude = b.latitude, longitude = b.Longitude
from newtable a
inner join [OldDb].[dbo].[LegacyTable] b on
b.name = a.name

I’ve encoutered the following :

Msg 468, Level 16, State 9, Line 5
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Possibly caused because the file comes from SQL Server 2008 while now I’m on the 2012. 

Luckly easy to fix by adding COLLATE DATABASE_DEFAULT on the columns used by the equals operator:


update a
set latitude = b.latitude, longitude = b.Longitude
from newtable a
inner join [OldDb].[dbo].[LegacyTable] b on
b.name = a.name
b.name COLLATE DATABASE_DEFAULT = a.name COLLATE DATABASE_DEFAULT

SQL – Remove chars from string

Posted on by Standard Respond

To be used when there’s the need or removing one or more chars from a record in a SQL table:

UPDATE TableName SET ColumnName = replace( ColumnName, '"', '' ); -- Remove commas

UPDATE TableName SET ColumnName = replace( replace(ColumnName, ',', ''), '"', '' );  -- Remove multiple chars: comma and quotes



			

	
	

		



				
					


				
					


				
					




	

		
Import data in SQL Express Management Studio


				

			Posted on  by  Standard					Respond
				

			


		

			
One of the restriction of the Sql Express Managent Studio is not allowing import/export of data. To overcome to this limitation we can either download the Sql Server Express Edition Toolkit or we can use the BULK INSERT statement in order to import a file.


This will be our document:




Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt -- john
%
ut labore et dolore magna aliqua. Ut enim ad minim veniam -- mario
%
sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaera -- ugo
%
quis nostrum exercitationem ullam corporis suscipit laboriosam -- anne
%
vel illum qui dolorem eum fugiat quo voluptas nulla pariatur -- vera




We create first a temporary table and store the content of the file in it:




CREATE TABLE #TempTbl
(
    Phrase nvarchar(max)
)

BULK INSERT #TempTbl
FROM 'C:\temp\sampleCommaSep.csv'
WITH
(
    ROWTERMINATOR='%'
)




It is possible to specity the column delimiter of the csv specifing  FIELDTERMINATOR in the query:




CREATE TABLE #TempTbl
(
    Phrase nvarchar(max),
    Author nvarchar(150)
)

BULK INSERT #TempTbl
FROM 'C:\temp\sampleCommaSep.csv'
WITH
(
    FIELDTERMINATOR = '--',
    ROWTERMINATOR='%'
)