CREATE TABLE dbo.M_Bank
(
ID INT IDENTITY NOT NULL,
MB_NAME NVARCHAR (50),
MB_SNAME NVARCHAR (20),
MB_ADDUSER INT,
MB_ADDDate DATETIME CONSTRAINT DF_M_Bank_MB_ADDDate DEFAULT (getdate()),
MB_MODUSER INT,
MB_MODDate DATETIME,
MB_ISActive BIT CONSTRAINT DF_M_Bank_ISActive DEFAULT ((1)),
MB_ISDeleted BIT CONSTRAINT DF_M_Bank_ISDeleted DEFAULT ((0)),
MB_IP VARCHAR (30),
CONSTRAINT PK_M_Bank PRIMARY KEY (ID)
)
GO
--Create Function
CREATE FUNCTION [dbo].[nop_splitstring_to_table]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(
data NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (data)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
GO
DECLARE @BankNameDetails NVARCHAR(4000)=
'Axis Bank,Allahabad Bank,American Express,Andhra Bank,Arab Bangladesh,Bank of Baroda India,Bank Muscat,Bank of America
Bank of India,Bank of Maharashtra,Bank of Punjab,Bank of Rajasthan,Barclays Bank PLC,Bharat Overseas,Canara Bank,Catholic Syrian
,Centurion,Ceylon,Citibank,Corporation,Cosmos Co-operative Bank,DBS,Dena,Deutsche Bank,Development Credit,
Dhanlakshmi,Export-Import Bank Of India,Federal Bank India,Global Trust,HDFC,Hongkong Shanghai Banking,
ICICI Bank,IDBI Bank,Ind Bank Housing,Indian Overseas,IndusInd Bank,Industrial Development,ING Vysya,Jammu and Kashmir
,JP Morgan Chase,Karnataka,Karur vysya,Kotak Mahindra,Lakshmi Vilas,Lord Krishna,Mizuho Corporate,Mudra Bank,
The Nainital Bank Ltd.,North Knara G.S.B. Co-op.,Oriental Bank of Commerce,Punjab and Sind,Punjab National,Ratnakar,
Reserve Bank of India,Royal Bank of Scotland,SBI Commercial,Shamrao Vithal Co-operative,South Indian,
Standard Chartered,State Bank Of Bikaner & Jaipur,State Bank of Hyderabad,State Bank of India,State Bank of Indore,
State Bank of Mysore,State Bank of Patiala,State Bank of Travancore,Syndicate Bank,Tamilnad Mercantile,Union Bank Of India,
UCO Bank,United Bank of India,Vijaya Bank,Yes Bank'
SELECT row_number() OVER(ORDER BY data ASC) AS ROWnno, data into #temp FROM dbo.nop_splitstring_to_table (@BankNameDetails, ',')
DECLARE @MB_NAME NVARCHAR(50)='Allahabad Bank'
DECLARE @TOTALV BIGINT
DECLARE @InitialValue BIGINT
SET @InitialValue=1
SET @TOTALV=12
SELECT @TOTALV=Count(*) from #temp
WHILE (@InitialValue<=@TOTALV)
BEGIN
SELECT @MB_NAME=data from #temp WHERE ROWnno=@InitialValue
IF NOT exists(SELECT 1 FROM M_Bank WHERE MB_NAME=@MB_NAME)
BEGIN
INSERT INTO dbo.M_Bank (MB_NAME, MB_SNAME, MB_ISActive, MB_ISDeleted, MB_IP)
VALUES (rtrim(ltrim(@MB_NAME)), '', 1, 0, '127.0.0.1')
SELECT -200
END
print @InitialValue
SET @InitialValue=@InitialValue+1
END
drop table #temp
