RcBuilder@walla.com
call centre: 054-5614020

SQL Categories

Posted by: RcBuilder  /   Category: CTE / DBA / SQL / SQL Function / Stored Procedure / System Function   /   No Comments
27
Nov
2014

SQL Categories - OPTION 1 - declare @tbl table(Id int,Name nvarchar(50),Parent int,SubCategories nvarchar(500)) insert into @tbl select 1,'111',0,'' union all select 2,'222',1,',2,5,6,7,13,14,' union all select 3,'333',1,'' union all select 4,'444',1,'' union all select 5,'555',2,'' union all select 6,'666',2,'' union all select 7,'777',2,',7,13,14,' union all select 8,'888',3,'' union all select 9,'999',3,'' union all select 10,'aaa',3,'' union all select 11,'bbb',3,'' union all select 12,'ccc',3,'' union all select 13,'ddd',7,'' union all select 14,'eee',7,'' ------------------------------------ declare @Id int set.

Read more

Entity Framework Model First

Posted by: RcBuilder  /   Category: ADO.NET / Entity Framework / SQL / Stored Procedure   /   No Comments
30
Oct
2014

Entity Framework Model First Create model * Add -> New Item -> (Data Tab) ADO.NET Entity Data Model (.edmx) -> set mame -> ok * in the wizard -> choose 'Empty Model' option * by right clicking within the edmx area we can choose which elements to add to our model - Entity : add a new POCO object (Book,.

Read more

SQL COLLAPSE example

Posted by: RcBuilder  /   Category: SQL / SQL Data Types / SQL Function / Stored Procedure / System Function   /   No Comments
20
Oct
2014

SQL COLLAPSE - multiple rows to single value we can use the COLLAPSE sql function to concatenation values into a single string data declare @tbl2 table(Name nvarchar(10), Quantity int, CreatedDate smalldatetime) insert into @tbl2 SELECT 'Roby', 100, '2009-12-05' UNION ALL SELECT 'Avi', 40, '2011-11-05' UNION ALL SELECT 'Avi', 60, '2010-05-03' UNION ALL SELECT 'Avi', 15, '2009-06-11' UNION ALL SELECT 'Ron', 50, '2010-04-11' UNION ALL SELECT 'Roby', 77,.

Read more

SQL PIVOT

Posted by: RcBuilder  /   Category: DATES / DBA / SQL / SQL Data Types / SQL Function / Stored Procedure   /   No Comments
07
Oct
2014

SQL PIVOT creates an horizontal group by. MUST define the groups names. structure pivot(<aggregation function> FOR <group by column> IN (<group by column values>)) USING example 1: declare @tbl1 table(Name nvarchar(10), Quantity int) insert into @tbl1 SELECT 'Roby', 100 UNION ALL SELECT 'Avi', 40 UNION ALL SELECT 'Avi', 60 UNION ALL SELECT 'Avi', 15 UNION ALL SELECT 'Ron', 50 UNION ALL SELECT 'Roby', 77 UNION ALL SELECT 'Roby', 12 UNION.

Read more

SQL COUNT NULLIF

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Function / Stored Procedure / System Function   /   No Comments
28
Sep
2014

SQL COUNT NULLIF // nullif return null if the condition is true // use this technique to count multiple counters in a single group by clause declare @tbl table(Id int, isOK bit, Grade tinyint) insert into @tbl select 1, 1, 50 union all select 1, 1, 75 union all select 4, 0, 25 union all select 6, 1, 54 union all select 6, 0,.

Read more

Split SQL FUNCTION

Posted by: RcBuilder  /   Category: SQL / SQL Data Types / SQL Function / Stored Procedure / System Function   /   No Comments
23
Sep
2014

Split SQL FUNCTION  create FUNCTION Split ( @String nvarchar (MAX), @Delimiter nvarchar (5) = ',' -- use the keyword 'DEFAULT' in order to use the default parameter ) RETURNS @ValueTable table ( nvarchar(50)) AS BEGIN if(RTRIM(LTRIM(@String)) = '') return declare @NextString nvarchar(50) declare @Pos int declare @NextPos int declare @CommaCheck nvarchar(1) --Initialize set @NextString = '' set @CommaCheck = right(@String,1) --Check for trailing Comma, if not exists, INSERT --if (@CommaCheck <> @Delimiter ) set @String =.

Read more

make database tables copy using fetch next

Posted by: RcBuilder  /   Category: FETCH NEXT / Master / Replication / SQL / SQL Data Types / SQL Function / Stored Procedure   /   1 Comment
01
Jul
2014

make database tables copy using fetch next declare _cursor CURSOR FOR SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.tables OPEN _cursor declare @currTABLE nvarchar(50), @currSCHEMA nvarchar(50) FETCH NEXT FROM _cursor INTO @currTABLE, @currSCHEMA -- first row -- WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY exec('select * into copyDB.' + @currSCHEMA + '.' + @currTABLE + ' from '+ @currTABLE) --select @currSCHEMA + '.' + @currTABLE END TRY BEGIN CATCH PRINT('ERROR :.

Read more

SQL UNION Example

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Function / Stored Procedure   /   No Comments
28
May
2014

SQL UNION Example declare @t1 table(Name varchar(20)) insert into @t1 select 'ROBY' UNION ALL select 'STEVE' UNION ALL select 'YOSSI' UNION ALL select 'RON' UNION ALL select 'OFIR' declare @t2 table(Name varchar(20)) insert into @t2 select 'TAL' UNION ALL select 'ITAY' UNION ALL select 'SHIRLY' ;with temp(Name) AS( select * from @t1 UNION ALL select * from @t2 ) select * from.

Read more
12
  • פיתוח מערכות
  • פתרונות טכנולוגים
  • קידום אתרים
  • בניית אתרים