BCP utility for data importing

With UserId/Pwd
bcp pubs.dbo.authors out c: emppubauthors.bcp –n –Sstevenw –Usa –P
bcp pubs2.dbo.authors in c: emppubauthors.bcp –n –Sstevenw –Usa –P

For Trusted
bcp pubs.dbo.authors out c: emppubauthors.bcp –n –Sstevenw –T
bcp pubs2.dbo.authors in c: emppubauthors.bcp –n –Sstevenw –T

*-S - Server Name -U - User Name -P Pwd

Get the list of Stored Procs related to a table

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%'

SQL Script for creating a New Database with Security Login and Password

////Create a new database, if does not exist
if db_id('DBNAME') is null
begin
create database [DBNAME]
end

=====================================================================================
////Once the Database is Created we can create the User login for this Database

////If user does not exist
if DATABASE_PRINCIPAL_ID('[DBNAME]') is null
begin
use [DBNAME]
if DATABASE_PRINCIPAL_ID('USERNAME') is null
CREATE LOGIN [USERNAME]
WITH PASSWORD='PASSWORD',
DEFAULT_DATABASE=[DBNAME],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
end

if DATABASE_PRINCIPAL_ID('USERNAME') is null
begin
use [DBNAME]
CREATE USER USERNAME
Grant Insert,Update,Delete,Execute,Select,Create Procedure to USERNAME
end


DBNAME = Database Name
USERNAME = User Login Name
PASSWORD = Password for the DB Login

Getting the Numerics from a String Value

Declare @sVal varchar(100)
Select @sVal= 'Here is where15234Numbers'
Select @sVal= SubString(@sVal,PATINDEX('%[0-9]%',@sVal),Len(@sVal))
Select @sVal= SubString(@sVal,0,PATINDEX('%[^0-9]%',@sVal))
Select @sVal

OutPut
-------
15234

if the string is 'Here is where15.234Numbers'
Then use
Select @sVal= SubString(@sVal,0,PATINDEX('%[^0-9,.]%',@sVal))

OutPut
------
15.234

Getting the Date Difference in Years, Months and Days

CREATE PROCEDURE dbo.CalculateAge
@dayOfBirth datetime
AS

DECLARE @today datetime, @thisYearBirthDay datetime
DECLARE @years int, @months int, @days int

SELECT @today = GETDATE()

SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)

SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END)

SELECT @months = MONTH(@today - @thisYearBirthDay) - 1

SELECT @days = DAY(@today - @thisYearBirthDay) - 1

select @thisYearBirthDay
SELECT @years [Years], @months [Months], @days [Days]

Getting Integer value from Varchar Column

Declare @str varchar(20)
Set @str = 'SARAN01'
select Substring(@str,PATINDEX('%[0-9]%',@str),len(@str))

OUTPUT
-------
01

Creating a CSV Text from Table Columns

Select Name from Users


Name
----
Ramu
Saju
Nattu


SELECT SUBSTRING((SELECT ',' + s.Name FROM Users s ORDER BY s.Name FOR XML PATH('')),2,200000) AS CSV

Splitting Text with Extra Characters to a Table

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function fn_ParseText2Table
(
@p_SourceText varchar(8000)
,@p_Delimeter varchar(100) = ',' --default to comma delimited.

)
RETURNS @retTable TABLE
(
Position int identity(1,1)
,Int_Value int
,Num_value Numeric(18,3)
,txt_value varchar(2000)
)
AS

BEGIN
DECLARE @w_Continue int
,@w_StartPos int
,@w_Length int
,@w_Delimeter_pos int
,@w_tmp_int int
,@w_tmp_num numeric(18,3)
,@w_tmp_txt varchar(2000)
,@w_Delimeter_Len tinyint
if len(@p_SourceText) = 0
begin
SET @w_Continue = 0 -- force early exit

end
else
begin
-- parse the original @p_SourceText array into a temp table

SET @w_Continue = 1
SET @w_StartPos = 1
SET @p_SourceText = RTRIM( LTRIM( @p_SourceText))
SET @w_Length = DATALENGTH( RTRIM( LTRIM( @p_SourceText)))
SET @w_Delimeter_Len = len(@p_Delimeter)
end
WHILE @w_Continue = 1
BEGIN
SET @w_Delimeter_pos = CHARINDEX( @p_Delimeter
,(SUBSTRING( @p_SourceText, @w_StartPos
,((@w_Length - @w_StartPos) + @w_Delimeter_Len)))
)

IF @w_Delimeter_pos > 0 -- delimeter(s) found, get the value

BEGIN
SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos
,(@w_Delimeter_pos - 1)) ))
if isnumeric(@w_tmp_txt) = 1
begin
set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
end
else
begin
set @w_tmp_int = null
set @w_tmp_num = null
end
SET @w_StartPos = @w_Delimeter_pos + @w_StartPos + (@w_Delimeter_Len- 1)
END
ELSE -- No more delimeters, get last value

BEGIN
SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos
,((@w_Length - @w_StartPos) + @w_Delimeter_Len)) ))
if isnumeric(@w_tmp_txt) = 1
begin
set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
end
else
begin
set @w_tmp_int = null
set @w_tmp_num = null
end
SELECT @w_Continue = 0
END
INSERT INTO @retTable VALUES( @w_tmp_int, @w_tmp_num, @w_tmp_txt )
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



select * from dbo.fn_ParseText2Table('101, 201, 223, 443', ',')

Position Int_Value Num_value txt_value
----------- ----------- -------------------- ----------
1 NULL NULL 101
2 NULL NULL 201
3 NULL NULL 223
4 NULL NULL 443

Date Formats Available in SQL Server

ID	Date Formats
0 or 100	mon dd yyyy hh:miAM (or PM)
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
13 or 113	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
20 or 120	yyyy-mm-dd hh:mi:ss(24h)
21 or 121	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

SQL convert number to text

CREATE FUNCTION fnMoneyToEnglish(@Money AS money)

RETURNS VARCHAR(1024)

AS

BEGIN

DECLARE @Number as BIGINT

SET @Number = FLOOR(@Money)

DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

INSERT @Below20 (Word) VALUES

( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

('Eighteen' ), ( 'Nineteen' )

INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

DECLARE @English varchar(1024) =

(

SELECT Case

WHEN @Number = 0 THEN ''

WHEN @Number BETWEEN 1 AND 19

THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

WHEN @Number BETWEEN 20 AND 99

-- SQL Server recursive function

THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +

dbo.fnMoneyToEnglish( @Number % 10)

WHEN @Number BETWEEN 100 AND 999

THEN (dbo.fnMoneyToEnglish( @Number / 100))+' Hundred '+

dbo.fnMoneyToEnglish( @Number % 100)

WHEN @Number BETWEEN 1000 AND 999999

THEN (dbo.fnMoneyToEnglish( @Number / 1000))+' Thousand '+

dbo.fnMoneyToEnglish( @Number % 1000)

WHEN @Number BETWEEN 1000000 AND 999999999

THEN (dbo.fnMoneyToEnglish( @Number / 1000000))+' Million '+

dbo.fnMoneyToEnglish( @Number % 1000000)

ELSE ' INVALID INPUT' END

)

SELECT @English = RTRIM(@English)

SELECT @English = RTRIM(LEFT(@English,len(@English)-1))

WHERE RIGHT(@English,1)='-'

IF @@NestLevel = 1

BEGIN

SELECT @English = @English+' Dollars and '

SELECT @English = @English+

convert(varchar,convert(int,100*(@Money - @Number))) +' Cents'

END

RETURN (@English)

END

GO


SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 67)

-- Sixty-Seven Dollars and 0 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 947.54)

-- Nine Hundred Forty-Seven Dollars and 54 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 1266.04)

-- One Thousand Two Hundred Sixty-Six Dollars and 4 Cents