SENARIO_2

My input table is like…………

Stuno Marks address
1001 1 US
1002 1 US
1003 1 UK
1004 1 UK
1005 2 LANDON
1006 2 LONDON
1007 3 BRAZAIL
1008 3 KENADA

MY OUPUT LIKE……………….

Stuno Marks address
1001 1 US
1003 1 UK
1005 2 LANDON
1007 3 BRAZAIL
1008 3 KENADA

ANS:

IF EXISTS(SELECT *FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='STU_DE')

 BEGIN

   DROP TABLE STU_DE

 END

CREATE TABLE STU_DE(STU_ID INT,MARKS INT,ADDR VARCHAR(50))

INSERT INTO STU_DE VALUES (1001,1,'US'),(1002,1,'US'),(1003,1,'UK')

INSERT INTO STU_DE VALUES (1004,1,'UK'),(1005,2,'LONDON'),(1006,2,'LONDON'),(1007,3,'BRAZAIL'),(1008,3,'KENADA')

SELECT STU_ID,MARKS,ADDR from(

select STU_ID,MARKS,ADDR, row_number() Over( partition by MARKS,ADDR order by STU_ID )R from STU_DE)a WHERE a.R =1 

;WITH CTE AS( SELECT STU_ID,MARKS,ADDR,ROW_NUMBER() OVER (PARTITION BY MARKS,ADDR ORDER BY STU_ID) AS ROW_NO FROM STU_DE)

SELECT STU_ID,MARKS,ADDR FROM CTE WHERE ROW_NO=1

SIMPLE SENORIO_1

Inputs
--------
Column 1 Column 2
01-Jan-2015 20
02-Jan-2015 30
04-Jan-2015 40
05-Jan-2015 10
07-Jan-2015 10

Write a Query to Display All Input values (Column A & Column B) + All missing values without using any TEMP table.

Output Expected
-----------------------
Column 1 Column 2
01-Jan-2015 20
02-Jan-2015 30
03-Jan-2015 Null
04-Jan-2015 40
05-Jan-2015 10
06-Jan-2015 Null
07-Jan-2015 10

ANS:

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='sample_b')

begin

drop table sample_b

end

create table sample_b(column1 date,column2 int)

insert into sample_b values('01-Jan-2015',20),('02-Jan-2015',30),('04-Jan-2015',40),('05-Jan-2015',10),('07-Jan-2015',10)

declare @min_date date

 select @min_date=MIN(column1) from sample_b

--print @min_date

if object_id('tempdb..#tempe') is not null

begin

drop table #tempe

end

create table #tempe(dat date,id int)

declare @max_date date 

select @max_date=MAX(column1) from sample_b

declare @colummn int

--select @date

while (@min_date < = @max_date)

begin

   select @colummn=column2 from sample_b where column1= @min_date

insert into #tempe (dat,id)

select distinct  @min_date,@colummn 

 select @min_date=(select dateadd(dd,1,@min_date))

end

--select * from sample_b

select DISTINCT A.DAT,b.COLUMN2 from #tempe a

left join 

   sample_b b

   on b.column1=a.dat

   --where b.column2 is  null

HOW TO GET ALL COLUMN DATA IN A SINGLE ROW BY USING XML PATH & STUFF TRICK

DECLARE @CodeNameString varchar(100)

SELECT 
   @CodeNameString = STUFF( (SELECT ',' + CodeName 
                             FROM dbo.AccountCodes 
                             ORDER BY Sort
                             FOR XML PATH('')), 
                            1, 1, '')

EXAMPLE: to get dynamic sript in a single column and 

         execute 

        some other way looping throw row by row execution

 If Exists (select * from sysobjects where id = object_id(N'[dbo].[table_list]') 

and OBJECTPROPERTY(id, N'IsUserTABLE') = 1)
DROP TABLE table_list

CREATE TABLE [table_list]
(    
    
    createtab NVARCHAR(MAX)
)
INSERT INTO table_list(createtab)
SELECT ' IF EXISTS(SELECT * FROM  Staging_Data.dbo.sysobjects 

WHERE ID = Object_Id(N''dbo.tbl_tkn_'+ CAST(token_schema_details_id as varchar(5)) + ''' ) 

and OBJECTPROPERTY(id, N''IsUserTable'') = 1)'+CHAR(10)+'BEGIN '
      + 'DROP TABLE Staging_Data.dbo.tbl_tkn_' + CAST(token_schema_details_id as varchar(5)) + ' END '
 +'  CREATE TABLE Staging_Data.dbo.tbl_tkn_' + CAST(token_schema_details_id as varchar(5)) +

 ' ( primary_key_value VARCHAR(50), card_acct_no VARCHAR(100), token_value VARCHAR(100) )'+CHAR(10) AS create_table
 FROM Token_Management.dbo.Token_Schema_Details WITH(NOLOCK)
 
 WHERE  [server] = 'E2PRDDBS01'
 


DECLARE @CodeNameString VARCHAR(max)
SELECT 
   @CodeNameString = STUFF( (SELECT '   ' + createtab 
                             FROM table_list
                             
                             FOR XML PATH('')), 
                            1, 1, '')
                            
select @CodeNameString as create_temptable

--EXEC( @CodeNameString) 

HOW TO GET ALL STORED PROCEDURES DYNAMICALLY IN A SERVER

CREATE TABLE #x(db SYSNAME, s SYSNAME, p SYSNAME);

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'INSERT INTO #x SELECT ''' + name + ''',s.name, p.name
  FROM ' + QUOTENAME(name) + '.sys.schemas AS s
  INNER JOIN ' + QUOTENAME(name) + '.sys.procedures AS p
  ON p.schema_id = s.schema_id;
' FROM sys.databases WHERE database_id > 4:

EXEC sp_executesql @sql;

SELECT db,s,p FROM #x ORDER BY db,s,p;

DROP TABLE #x;

GET ALL TABLES IN A SERVER BY USING SYSTABLES:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'INSERT INTO #x SELECT ''' + name + ''',s.name, p.name
  FROM ' + QUOTENAME(name) + '.sys.schemas AS s
  INNER JOIN ' + QUOTENAME(name) + '.sys.tables AS p
  ON p.schema_id = s.schema_id;
' FROM sys.databases WHERE database_id > 4

 print @sql

impotant sys table to get sp's details:

SELECT [Routine_Name] 
FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE [ROUTINE_TYPE] = 'PROCEDURE'

 SELECT OBJECT_NAME(object_id) FROM sys.sql_modules 
  WHERE definition LIKE '%ES_PURGE%'

  SELECT OBJECT_NAME(ID) AS SP_NAME FROM syscomments 

 WHERE text LIKE '%ES_PURGE%' 

SELECT [Name] FROM [sys].[procedures]

SELECT [Name] FROM [sys].[objects] WHERE [type] = 'P'

SELECT [Name] FROM [sys].[all_objects] WHERE [Type] = 'P' AND [Is_MS_Shipped] = 0

SELECT [Name] FROM [dbo].[sysobjects] WHERE [XType] = 'P'

HOW TO LOOP ALL DATABASES TO GET REQUIRED COLUMN DETAILS

SET ANSI_WARNINGS OFF;
SET FMTONLY OFF;
SET NOCOUNT ON
DECLARE  @Db_name TABLE( database_name  NVARCHAR(MAX),id INT IDENTITY(1,1))

INSERT INTO @Db_name
SELECT name  FROM sys.databases WHERE name NOT IN  ('SQL_ADMIN_DB','master','EDWARD','Policy_Accounting_Production'
,'Lookup_Tables_Db','ignite_repository','AdventureWorks')

--SELECT * FROM  @Db_name

DECLARE @cnt INT
SELECT@cnt = COUNT(*)  FROM @Db_name

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

CREATE TABLE  #temp( database_name  VARCHAR(MAX),table_name  VARCHAR(MAX),column_name VARCHAR(MAX))

--select @cnt
While (@cnt>0)
BEGIN
DECLARE @ddbname VARCHAR(100)
DECLARE @sql NVARCHAR(max) =' '


    SELECT @ddbname=database_name FROM @Db_name WHERE id=@cnt
  
      --SELECT @ddbname
    SET @sql=@sql+' INsert into #temp( database_name,table_name,column_name) Select  table_catalog,table_name,column_name  from   '+  @ddbname+'.information_schema.columns where column_name like ''%card%''  '

      EXEC (@sql )

     DELETE FROM @Db_name WHERE id=@cnt
  
  
SET @cnt=@cnt-1
END

;WITH cte_table AS(
SELECT database_name ,table_name,column_name FROM #temp)
--INSERT INTO Token_Management.dbo.Token_Schema_details
--(Database_name,Table_name,column_name)
SELECT Database_name,Table_name,column_name   FROM cte_table
WHERE table_name NOT LIKE 'sync%'
AND column_name NOT IN ( 'card_account_type','card_type','Card_type_id','pm_card_type','card_category',
'cardholder_name','card_payment_account_id','previous_card_type_id','card_type_cd','card_type_desc'
,'timecard_acknowledged_flag','CreditCardID','CreditCardApprovalCode','card_number_is_scrambled','CardType')


------------------------------Another simple way to use msforeachdb 



Create table #yourcolumndetails(DBaseName varchar(100)
                                                      , TableSchema varchar(50)
                                                     , TableName varchar(100)
                                                     ,ColumnName varchar(100)
                                                     , DataType varchar(100)
                                                     , CharMaxLength varchar(100)
                                                   )

EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE Table_Name like ''%StringMapBase%'''  

select * from #yourcolumndetails
Drop table #yourcolumndetails
 

SQL COMMANDS

• SQL commands are instructions, coded into SQL statements, which are used to communicate with the database to perform specific tasks, work, functions and queries with data. 

• SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create table, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:        

There are four types of sqlcommands ,that are 

tsql     1.DDL(DATA DEFINITION LANGUAGE)
           2.DML(DATA MANIPULATION LANGUAGE)
           3.DCL(DATA CONTROL LANGUAGE)
Trans   4.TCL(TRANSACTION CONTROL LANGUAGE)

1.DATA DEFINITION LANGUAGE(DDL):






 

LINKED SERVERS IN SQL SERVER

• Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server.

• Linked server is a concept in SQL Server to access external data sources. This external data sources can be Access, Oracle, Excel, SQL Server or almost any other data system that can be accessed by OLE or ODBC.

  Advantages:

Linked serves mojorly used in Real Time to acess data from external data sources like   oracle,excel,some other instaces in sql server. and also

                           

•  Remote server access.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise. 

create linked servers:

• A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

Generally two ways to create linked servers

         

            1. SQL Server Management studio(by wizard)

            2. Transact-SQL