Insert Multiple record with different data using ABS,CheckSum,NEWID SQL

we always need  tricky solutions in sql server to make our business more easier
in this tutorial we will use SQL server 2012

first we will explain meaning of of some words may be functions,we will use

A-  we have Created database called "AnasTest" which contains table with name "employees" and another table "EmployeeType"

"EmployeeType" table contains four records

Employees Table Columns
   .EmployeeId
   .EmployeeName
   .Email
   .EmployeeGuid
   .EmployeeTypeID
   .Phone

1. if we need to insert 5000 record for testing and need each employee guid is different and randomly set employee type to one of the predefined in "EmployeeType" table 

simply we need to know use of some functions 

• NEWID() this function generated guid will not be duplicated ever because it uses machine mac address and apply some equations on it so don't worry it will be unique  

             example: select NEWID()

         

• CHECKSUM('') functions is to build a hash index based on an expression or a column list                    example select CHECKSUM('12') you will get result of 2262
• ABS () function is used to get the positive value of number                                                              example select abs(-120) you will get 120

2- now we need yo insert employees with employee type (1,2,3,4) and with unique guid

   WE USED combination of function abs(CHECKSUM(NEWID())%4)+1
to getting  random guid and hashing it's  number will generate number from -2 billion to +2 billion .
and use % operator "Modulus - Divides left hand operand by right hand operand and returns remainder" and got positive reminders but we don't need 0 so we added 1 and

now if we select from "employee" table we  will got 5000 record with different data

Enjoy
Anas Saad
Senior Software Engineer

Convert comma separated string into table

We all use stored procedure ,
And Some Time we need to convert comma separated string into table.

assume that u have Cars table and primary key is Called CarID

and you want to write query that select all Cars that have Ids (11,12,13)
then you will write

SELECT * FROM [lkp_Cars] WHERE CarID  IN (11,12,13)

you will got BMW,Hyundai,Honda

assume you have scenario -> user select from interface multiple cars and you to run query

select * from lkp_cars where carID in (Selected cars)

 So you need to pass all selected cars from interface to the query by concatting  all carID+","+carID +","...

the point is that  i need to convert comma separated string  into table
so create the next function and use in any sql query

Create FUNCTION ConvertCommaSepratedToTable
(
      @IDsList varchar(500 )
)
RETURNS
@ParsedList table
(
      ID int
)
AS
BEGIN
       DECLARE @ID varchar (10), @Pos int

       SET @IDsList = LTRIM( RTRIM(@IDsList ))+ ','
       SET @Pos = CHARINDEX( ',', @IDsList , 1)

       IF REPLACE (@IDsList, ',', '') <> ''
       BEGIN
             WHILE @Pos > 0
             BEGIN
                   SET @ID = LTRIM( RTRIM(LEFT(@IDsList , @Pos - 1)))
                   IF @ID <> ''
                   BEGIN
                         INSERT INTO @ParsedList ( ID)
                         VALUES (CAST (@ID AS int)) --Use Appropriate conversion
                   END
                   SET @IDsList = RIGHT(@IDsList , LEN (@IDsList) - @Pos )
                   SET @Pos = CHARINDEX( ',', @IDsList , 1)

             END
       END   
       RETURN
END

annd now modify you query to

select * from lkp_cars where carID in (ConvertCommaSepratedToTable(IdsString))

Enjoy

Anas Saad
Senior Software Engineer