Today I have to record very small tip. During development of on SSRS report for Dynamics Ax 2012 R3, End user wants a row at the report footer for analysis. he wants some kind of percentage calculation with '%'. A For this I have to use CStr ssrs expression function like Ctr(((sum(DataSet!field.Value) / … [Read more...] about float value conversion in string SSRS expression and decimal points SSRS dynamics ax 2012 R3.
Suppose we have to insert data into VendGroup table through TSQL, this scenario usually appear when integration or data migration team inject data into AX using TSQL without using AIF. There are two tables are important SQLDICTONARY and SYSTEMSEQUENCES. SQLDictonary table contains the table id for example Vendtable has 490 id. SYSTEMSEQUENCES table contains … [Read more...] about How to get next recid in TSQL Dynamics Ax 2012
Currently I was searching describe like function, which used in Oracle PLSQL to get the detail of table. I found equvilent function in TSQL "sp_help". you can used it as sp_help <table_name> i.e sp_help studentdet … [Read more...] about describe table tsql
I am dropping the rows but insertion start with next to last max identity . Something was missing in my knowledge. To reset Identity i have use one extra statement, other option is use trunc statement to drop rows. but if i have too drop rows with delete statement and reset the identity what should do, i have to use DBCC CHECKIDENT("table_name", RESEED, "reseed_value") This … [Read more...] about Delete with identity reset
During my assignment I have to generate Integer value from datetime filed for Date Dimension . I found excellent id this way. CAST(CONVERT(varchar(8),StartDATE,112) AS int) DateDateKey, The whole Query is as follow. SELECT CAST(CONVERT(varchar(8),BEGDATE,112) AS int) DateDateKey, ,Name ,Address ,Joindate ,EndDate FROM dbo.from … [Read more...] about How to convert date to interger In TSQL
Delete Part of ETL Process If any row is deleted at source table, but destination table rows exits. So I did to make a inner join with source table with destination table I used right join in this case, so I got all rows from destination which I placed at right side of query. The result set I got with null at left side form source table. I filter the result set and got all … [Read more...] about Using a Common Table Expression (CTE) with an Delete statement
For update part of ETL Process I have to write another Common table expression to get all records where are changed at source to run update query on destination table My update part of basic ETL using TSQL as with ChangedRows as ( SELECT sourceEmp.empid, sourceEmp.lastname, sourceEmp.firstname, sourceEmp.title, sourceEmp.titleofcourtesy, … [Read more...] about Using a Common Table Expression (CTE) with an UPDATE statement
My current assignment was to write ETL Process using TSQL. So I have to get all rows which are newly added to Source Table and are not part of destination table. I wrote simple Common Table Expression to get all rows and insert into destination table. So part of ETL Process using CTE as with NewRows as ( SELECT sourceEmp.empid, sourceEmp.lastname, … [Read more...] about Using a Common Table Expression (CTE) with an INSERT INTO statement
DECLARE @PageNum AS INT; DECLARE @PageSize AS INT; SET @PageNum = 2; SET @PageSize = 10; WITH OrdersRN AS ( SELECT ROW_NUMBER() OVER(ORDER BY DimProduct.ProductKey) AS RowNum, DimProduct.ProductKey, DimProduct.EnglishProductName as Product, DimProductSubcategory.ProductSubcategoryKey as SubCategoryKey, … [Read more...] about T-SQL: How to do SQL Server paging with ROW_NUMBER()?
You can use the list down all tables in specific database with this query select TABLE_NAME from information_schema.tables where Table_Type = 'BASE TABLE' … [Read more...] about How to get list of tables in Database TSQL
Nolock is T-Sql hint, That used to ignore the locks on table during transactions. It allows retrieving data and did not wait to complete the transaition applied on table. It has some pros can cons Pros: NoLock provides significant improvements on large table, where upserts commands take time. You can retirve data during the same time while others are performing Insert and … [Read more...] about Using WITH (NOLOCK) in T-SQL?
Main object is sys.tables. you can get all information about tables by query on sys.tables. User databasename Select * from sys.tables;. SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_Name FROM sys.tables; SCHEMA_NAME convert schema_id into schema name. … [Read more...] about How to get Tables list in database TSQL
In my seven years of development experience, I read my tutorial and books on programming as well as. But when we talk about sql server from developer ‘s perspective. The following books are best. From novice developer to expert Tsql developer I recommend these books. All these books are from Itzik Ben-gan From Microsoft learning site Microsoft® SQL Server® … [Read more...] about Three TSQL books which every software developer must read