<<< These scripts were tested on MS SQL Server  2019 >>> From time to time we need to generate huge data for testing. Without any data generating tool we can achieve it by using some SQL Scripts. these scripts are different from regular query scripts. Generate list of data CREATE TABLE #accounts ( fname VARCHAR(20), lname VARCHAR(20)) GO INSERT #accounts VALUES ('Fred', 'Flintstone') GO 1000 SELECT TOP 10 * FROM #accounts fname lname Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone ALTER TABLE #accounts ADD id INT IDENTITY(1,1) SELECT TOP 10 * FROM #accounts GO fname lname     id Fred Flintstone 1 Fred Flintstone 2 Fred Flintstone 3 Fred Flintstone 4 Fred Flintstone 5 Fred Flintstone 6 Fred Flintstone 7 Fred Flintstone 8 Fred Flintstone 9 Fred Flintstone 10 ALTER TABLE #accounts ADD id INT DECLARE @id INT SET @id = 0 UPDATE #accounts SET @id = id = @id + 1 OPTION ( MAXDOP 1 ) GO SELECT * FROM #accounts GO fname lname     id Fred Flintstone 1 Fred Flintstone 2 Fred Flintstone 3 Fred Flintstone 4 Fred Flintstone 5 Fred Flintstone 6 Fred Flintstone 7 Fred Flintstone 8 Fred Flintstone 9 Fred Flintstone 10 Note: MAXDOP is max degree of parallelism, set it to 1 to avoid duplicate value if running on multiple processors machine. Read article 1 for detail. Generate sequence numbers Using sys.all_columns table SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns Generate huge Rows using CROSS JOIN SELECT ROW_NUMBER()…

Split array column into rows Use OPENJSON feature in sql server (2016+) to parse json array string into rows. Let's first look this: select * from openjson('["small.jpg","mid.jpg","big.jpg"]') select * from openjson('{"A":"small1.jpg","B":"mid1.jpg","C":"big1.jpg"}') Result: key value type 0 small.jpg 1 1 mid.jpg 1 2 big.jpg 1 key value type A small1.jpg 1 B mid1.jpg 1 C big1.jpg 1 Now look at more complehensive example: create table parts (id int, name varchar(255), images varchar(max)) insert into parts (id,name,images) values (1, 'nail','["small.jpg","mid.jpg","big.jpg"]'), (2, 'screw','{"A":"small1.jpg","B":"mid1.jpg","C":"big1.jpg"}') --split array column to rows declare @tmp table(id int, img varchar(4000)) declare @id int,@img varchar(max) declare csr cursor for select id,images from parts open csr fetch next from csr into @id,@img while @@fetch_status=0 begin insert into @tmp (id,img) select @id,value from openjson(@img) fetch next from csr into @id,@img end close csr deallocate csr select * from @tmp Result: Concat rows to a column Using OPENXML feature in sql server to concat rows into a column. Reuse to script above: --concat rows to a column --using above example select id, '_product_image_gallery', stuff((select ','+img from @tmp where id=t.id for xml path('')),1,1,'') from @tmp t group by id Result:  1,358 total views

Error handling in sql server was a pain before SQL Server 2005. Starting from  Sql Server 2005 try ... catch blocks was introduced. It was not only giving us a beauty of coding, but greatly improved our development performance. It comes with the following build-in functions: ERROR_NUMBER() ERROR_SEVERITY() ERROR_STATE() ERROR_PROCEDURE() ERROR_LINE() ERROR_MESSAGE() When these functions is called in a CATCH block, they will returns the error information. After working on many projects, I found a best pattern for error handling in Sql Server as the following steps. 1. Create a log table to store error information catch by try...catch blocks, CREATE table sys_log( id int identity primary key, procName varchar(255), essionID int, errorNumber int, severity smallint, errorState smallint, errorLine int, description nvarchar(255), info nvarchar(max), mslangid smallint default 1033, --1033 English, 2052 Chinese timeStemp datetime default getdate()) GO 2. create a stored procedure to catch and log error information: CREATE proc sys_LogError @procname sysname, @info nvarchar(max)='' AS INSERT INTO sys_log(ProcName,SessionID,ErrorNumber,Severity,ErrorState,ErrorLine,Description,info) values ( @procname, @@spid, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE(), @info ) 3. in all stored procedures that need to handle exepctions in transaction using this template: CREATE proc proc_name AS BEGIN tran BEGIN try -- do something print 1/0 commit tran END try BEGIN catch rollback tran declare @info nvarchar(max)='@table='+@table+', @urls='+@urls declare @procname sysname = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) exec sys_LogError @procname,@info ---- re-throw exeception if you need --declare @msg nvarchar(2048) = error_message() --declare @severity smallint = error_severity() --declare @state smallint = error_state() --raiserror (@msg, @severity, @state) END catch ERROR_MESSAGE() can be referenced with MASTER.dbo.sysmessages table, mslangid is for identifying…

Post_name in wordpress is urlencoded, and not showing Chinese chars. urlencode DELIMITER ; DROP FUNCTION IF EXISTS urlencode; DELIMITER | CREATE FUNCTION URLENCODE(str VARCHAR(4096) CHARSET utf8) RETURNS VARCHAR(4096) CHARSET utf8 DETERMINISTIC CONTAINS SQL BEGIN -- the individual character we are converting in our loop -- NOTE: must be VARCHAR even though it won't vary in length -- CHAR(1), when used with SUBSTRING, made spaces '' instead of ' ' DECLARE sub VARCHAR(1) CHARSET utf8; -- the ordinal value of the character (i.e. ñ becomes 50097) DECLARE val BIGINT DEFAULT 0; -- the substring index we use in our loop (one-based) DECLARE ind INT DEFAULT 1; -- the integer value of the individual octet of a character being encoded -- (which is potentially multi-byte and must be encoded one byte at a time) DECLARE OCT INT DEFAULT 0; -- the encoded return string that we build up during execution DECLARE ret VARCHAR(4096) DEFAULT ''; -- our loop index for looping through each octet while encoding DECLARE octind INT DEFAULT 0; IF ISNULL(str) THEN RETURN NULL; ELSE SET ret = ''; -- loop through the input string one character at a time - regardless -- of how many bytes a character consists of WHILE ind <= CHAR_LENGTH(str) DO SET sub = MID(str, ind, 1); SET val = ORD(sub); -- these values are ones that should not be converted -- see http://tools.ietf.org/html/rfc3986 IF NOT (val BETWEEN 48 AND 57 OR -- 48-57 = 0-9 val BETWEEN 65 AND 90 OR -- 65-90 = A-Z val BETWEEN 97 AND 122 OR -- 97-122 =…

Joining all column values into one string with concat function is a pain, especially for a table with large numbers of columns. This script concats all columns quickly. declare @sql varchar(max) SELECT @sql = ISNULL( @sql+ '' + '+'',''+', '') + 'convert(varchar(50), [' + c.name + '])' FROM sys.all_columns c join sys.tables t ON c.object_id = t.object_id WHERE t.name = 'herb' and c.name!='id' EXEC( 'SELECT ' + @sql + ' FROM herb') Result The script generates long string value of @sql as following: convert(varchar(50), [alias])+','+convert(varchar(50), [application])+','+convert(varchar(50), [attachment])+','+convert(varchar(50), [category])+','+convert(varchar(50), [caution])+','+convert(varchar(50), [chemical])+','+convert(varchar(50), [desc])+','+convert(varchar(50), [english])+','+convert(varchar(50), [enterdate])+','+convert(varchar(50), [extract])+','+convert(varchar(50), [form])+','+convert(varchar(50), [form1])+','+convert(varchar(50), [grow])+','+convert(varchar(50), [guijing])+','+convert(varchar(50), [image_all])+','+convert(varchar(50), [image_leaf])+','+convert(varchar(50), [image_root])+','+convert(varchar(50), [indication])+','+convert(varchar(50), [indication1])+','+convert(varchar(50), [locations])+','+convert(varchar(50), [medicalprop])+','+convert(varchar(50), [name])+','+convert(varchar(50), [notes])+','+convert(varchar(50), [origin])+','+convert(varchar(50), [pharmacology])+','+convert(varchar(50), [pinyin])+','+convert(varchar(50), [process])+','+convert(varchar(50), [source])+','+convert(varchar(50), [store])+','+convert(varchar(50), [url])+','+convert(varchar(50), [usage])  1,863 total views

Problem The second line contains pingying and alias. html code: Zhōngfǔ&nbsp;&nbsp;&nbsp;肺之募穴 First try get data roughly: alias = p.Select(Selectors.XPath("//span[@style]"))?.Value.Replace("&nbsp;", " "); Result: Zhōngfǔ   肺之募穴 Split the alias column in MySQL: UPDATE `xuewei` set pingying=substring_index(alias,' ',1); UPDATE `xuewei` set bieming=substring_index(alias,' ',-1); Split the column in process: alias = p.Select(Selectors.XPath("//span[@style]"))?.Value.Replace("&nbsp;", " "); var arr = alias.Split(' '); pingying = arr[0]; alias = arr[arr.Length-1]; //get last item in the array for the space in between is uncertain. Save to database: await conn.ExecuteAsync( $"INSERT IGNORE INTO xuewei (name,pingying,alias,position,anatomy,indication,operation,imagegeneral,imagepoint) VALUES " + $"('{data.Name}', '{data.Pingying}', '{data.Alias}', '{data.Position}', '{data.Anatomy}', '{data.Indication}', '{data.Operation}', '{data.ImageGeneral}', '{data.ImagePoint}');");  1,433 total views

新冠疫情Covid-19公开的数据源 Novel Coronavirus (COVID-19) Cases Data https://data.humdata.org/dataset/novel-coronavirus-2019-ncov-cases COVID-19 开放研究数据集地址： https://pages.semanticscholar.org/coronavirus-research COVID-19 开放研究数据集挑战赛地址： https://www.kaggle.com/allen-institute-for-ai/CORD-19-research-challenge 北美新型冠状肺炎COVID-19疫情实时动态| 一亩三分地 https://coronavirus.1point3acres.com/?from=groupmessage  1,953 total views

Installed SQL Server 2017, but New Diagram is not allowed. Resolution ref: SSMS Cannot execute as database principal (Microsoft SQL Server, Error: 15517) Result  1,421 total views

Scenario: We have tool_ip table holds visitor ip log with city and country information, now we have a requirement to generate an DML to update country information in another table. This is not a regular transformation into DML for sure, due to multiple values across rows in the source table. We use the following script to discover the intermediate result - city list. SELECT count(DISTINCT city) citycount, LOCATE('\',\'',GROUP_CONCAT(DISTINCT city order by city SEPARATOR '\',\'')) pos, GROUP_CONCAT(DISTINCT city order by city SEPARATOR '\',\'') cities FROM tool_ip where trim(country) !='()' and trim(city)!='' group by country HAVING GROUP_CONCAT(DISTINCT city order by city SEPARATOR '\',\'') != '' Query result look like this: Now we can get the final sql script: SET SESSION group_concat_max_len = 1000000; SELECT concat( 'update wp_activity_log set country=\'', SUBSTRING_INDEX(SUBSTRING_INDEX(country, '(', -1), ')', 1), '\' where city in (\'', GROUP_CONCAT(DISTINCT city order by city SEPARATOR '\',\''), '\');' ) `sql` FROM tool_ip WHERE trim(country) !='()' and trim(city)!='' GROUP BY country HAVING GROUP_CONCAT(DISTINCT city order by city SEPARATOR '\',\'') != '' Result: Key points gained: Country format is like 'Canada (CA)' in database, so to get ISO country code like this: SUBSTRING_INDEX(SUBSTRING_INDEX(country, '(', -1), ')', 1) data contains empty city and country that we need to filter out in order to reduce interference factors; Using GROUP_CONCAT() function to combine cities into one string in order to build update statement in SQL. In order to overcome the string length limit of GROUP_CONCAT() function add SET SESSION group_concat_max_len = 1000000; on top of the script. Done.  1,812 total views

Slowly changing dimension (SCD) was introduced by Ralph in 1996 for data warehouse to solve slowly changing dimensions problem. However, it also includes rapidly changing dimension. There are 4 basic types of SCD, and 3 complex types which are combination of the basic types. SCD Type 0: never change In this type, all dimension attributes retains original, no change accepted. SCD Type 1: overwriting history Dimension attributes are overwritten, always keep the current value and therefore does not track historical data. So, for query on SCD0 and SCD1 is straight forward, nothing fancy. SCD Type 2: preserving history SCD type 2 writes a record with the new attribute information and preserves a record of the old dimensional data. SCD Type 2 data model Example: create table fact_picked(id int identity, Emp_Name varchar(30),[Date] date,Apples_Picked int); alter table fact_picked add primary key(id) insert into fact_picked(emp_name,[date],apples_picked) values ('John','10/17/15',175), ('John','05/01/17',100); create table dim_company (Emp_Name varchar(30) not null, Company varchar(30) not null, Effective_Since date); alter table dim_company add primary key(Emp_Name,Company) insert into dim_company values ('John','Blue_Apples', '06/01/2015'), ('John','Apple_N_Stuff','01/01/2016'), ('John','Da_Big_Apple', '03/17/2017'), ('John','Big_Tech', '10/20/2017'); select fp.Emp_Name, fp.[Date],fp.Apples_Picked,dc.Company,dc.Effective_Since from fact_picked fp left join dim_company dc on dc.Emp_Name = fp.Emp_Name and dc.Effective_Since = ( select max(Effective_Since) from dim_company dc2 where dc2.Emp_Name = fp.Emp_Name and dc2.Effective_Since <= fp.[Date]) order by 1,2,5; Result: SCD Type 2 example result SCD Type 3: preserving aversion of history In Type 3, additional attributes are added to store historical data. It let you preserve historical data, but only limited data can be stored. SCD Type 3 data model Example (continue with the previous one): create table dim_scd3(emp_name…