HOW TO SPLITE COMMA SEPERATED STRING AND INSERT INTO TEMP TABLE USING PROCEDURE

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[splite_insert_into_table]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].splite_insert_into_table
GO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].splite_insert_into_table (@autostates_in_mlp NVARCHAR(4000))
AS
BEGIN

       DECLARE @OrderID varchar(10), @Pos int

    SET @autostates_in_mlp = LTRIM(RTRIM(@autostates_in_mlp))+ ','
    SET @Pos = CHARINDEX(',', @autostates_in_mlp, 1)
    --PRINT @autostates_in_mlp
    --PRINT @Pos


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

    CREATE TABLE #temp_auto_states_in_ML(state_cd CHAR(2))
    IF REPLACE(@autostates_in_mlp, ',', '') <> ''
    BEGIN
        WHILE( @Pos > 0)
        BEGIN
            SET @OrderID = LTRIM(RTRIM(LEFT(@autostates_in_mlp, @Pos - 1)))--WY
            IF @OrderID <> ''
            BEGIN
                INSERT INTO #temp_auto_states_in_ML (state_cd)
                VALUES (CAST( @OrderID AS VARCHAR(10))) --Use Appropriate conversion
            END
            SET @autostates_in_mlp = RIGHT(@autostates_in_mlp, LEN(@autostates_in_mlp) - @Pos)
            SET @Pos = CHARINDEX(',', @autostates_in_mlp, 1)

END
    SELECT * FROM  #temp_auto_states_in_ML
END

END

TESTING :
EXEC splite_insert_into_table @autostates_in_mlp='WY'
EXEC  splite_insert_into_table  @autostates_in_mlp='WY,NA,BA,NA'

--------------------another simple  way

DECLARE @autostates_in_mlp VARCHAR(256)
SET @autostates_in_mlp='WY,NA'
DECLARE @starting_position INT
   
     DECLARE @Previous_position INT

     SET @starting_position=1
     SET @Previous_position=1
      SET @autostates_in_mlp = LTRIM(RTRIM(@autostates_in_mlp))+ ','

      SELECT @starting_position= CHARINDEX(',',@autostates_in_mlp,@Previous_position)

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

    CREATE TABLE #temp_auto_states_in_ML(state_cd CHAR(2))


WHILE (@starting_position>0)
BEGIN
     INSERT INTO #temp_auto_states_in_ML(state_cd)
     SELECT SUBSTRING (@autostates_in_mlp,@Previous_position,@starting_position-@Previous_position)

     SET @Previous_position=@starting_position+1
     SELECT @starting_position = CHARINDEX (',',@autostates_in_mlp,@Previous_position)
END



           

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):