Posts

get Identity column value from Table

Image
hi friends, today i find one of the good feature to find Identity column from table.. go though with this example create Table T1(Col1 int identity(1,5),Col2 int) Go Insert into T1(Col2) Select 100 Go 100 Select $Identity,* From T1 so at here i am creating one table with having two columns one is my identity column and another is used to store value (100) now after creating table at here i am inserting record in it.. Insert into T1(Col2) Select 100 Go 100 Inserting Identity value starting from 1 and seeds to 5 element like 1,6,11,16,21,26 and so on.... and Go 100 is used for looping that insert statement 100 times.. and i am also selecting 100 value at here.. by using $Identity i can come to know about Identity column value.

SQL Function VS StoredProcedure

Question What is the difference between SQLStored Procedure and SQLFunction? Answer 1) A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value. 2) Stored procedure in Sql Server can not we executed within the DML statement.It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement.

CTE Common Table Expression

The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope. The syntax of the CTE is the following. WITH name (Alias name of the retrieve result set fields) AS ( //Write the sql query here ) SELECT * FROM name Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE. CTE 1: Simple CTE WITH ProductCTE AS ( SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price] FROM Products ) SELECT * FROM ProductCTE Here all the product details like ID, name, category ID and Unit Price will be retrieved and stored as temporary result set in the ProductCTE. This result set can be retrieved like table or view. CTE2:Simple CTE with alias WITH ProductCTE(ID,Name,Category,Price) AS ( SELE

Regular Expression for strong password

hi friend,  what is strong password?  Ans :: it contains at least  1 small letter character  1 capital letter character  1 numeric character  1 special symbol  and length of password is between 4 to 8 Character  that is called strong password..  regex for Strong password  ^(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%&?]).{4,8}$ how to work with asp.net regex library  bool IsMatch1 = Regex.IsMatch(passwordString, @"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%&?]).{4,8}$"); thanks  jayeshl  http://sqlassistant.blogspot.com  

Generate password dynamically

Generate Dyanmic Password using Asp.net protected void btnGenerate_Click(object sender, EventArgs e) { string allowedChars = ""; allowedChars = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,"; allowedChars += "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,"; allowedChars += "1,2,3,4,5,6,7,8,9,0,!,@,#,$,%,&,?"; char[] sep ={ ',' }; string[] arr = allowedChars.Split(sep); string passwordString = ""; string temp = ""; Random rand = new Random(); for (int i = 0; i < Convert.ToInt32(txtPassLength.Text); i++) { temp = arr[rand.Next(0, arr.Length)]; passwordString += temp; } txtPassword.Text = passwordString; }

bulk insert into sqlserver using textfile

Image
bulk insert into sqlserver using textfile Bulk insert sql server 2008 example I find so many issues while inserting bulk record in database.. Need to create temp table than I has to map all fields which is in file and in table than Insert it in to actual table but here is one Master command to insert record in bulk through simple statement.. Step 1 ) Create Table. create table AddressBook (id int,name varchar(50),address varchar(50)) Step 2) Create Text file with “,” delimiter. 1,John,India 2,Raj,India 3,Sonal,India 4,Roshan,India Step 3) Bulk insert record in to AddressBook table bulk insert AddressBook from 'c:\Addressbook.txt' with ( fieldterminator = ',' , rowterminator = '\n' )

SQL - ISO Country Names and Codes

SQL - ISO Country Names and Codes This document provides the necessary SQL statements for ISO 3166 country names and two and three letter codes. This page and the data are in UTF-8 encoding. Create Country Table Table is called base_country as it follows Edoceo naming conventions, change as necessary. This is for a PostgreSQL database, CREATE TABLE statements may need to be altered for your RDBMS. create table base_country ( id integer primary key, iso2 char(2), iso3 char(3), name_en varchar(64), name_fr varchar(64), name_de varchar(64) ); Create indexes as necessary, which may not be as this table is so small. Insert the Country Data This SQL should work on any SQL-99 compliant RDBMS. INSERT INTO base_country (id, iso2, iso3, name_en, name_fr) VALUES (1, 'AF', 'AFG', 'Afghanistan', NULL); INSERT INTO base_country (id, iso2, iso3, name_en, name_fr) VALUES (2, 'AX', 'ALA', 'Ă…land Islands', NULL); INSERT INTO ba