Insert Select in SqlServer 2005

Where you use?

Simple example of user registration (first insert all data in the temporary table after the confirmation of something we insert that data in to permanent table.)

Steps:
1) Create the two tables

1.1) TempUser

Field Name Data-type
UserTempId int(primary key Auto Increment)
UserName varchar(50)
Password varchar(50)
Address1 varchar(50)
EmailAddress varchar(50)
IsUserRegister bit

1.2) User

Field Name Data-type
UserId int(primary key Auto Increment)
UserName varchar(50)
Password varchar(50)
EmailAddress varchar(50)
Address1 varchar(50)

2) Data In to table (TempUser)

After the completion of the registration one link to send to the user’s email address.

UserTempId UserName Password Address1 EmailAddress IsUserRegister
1 A A A test@test.com false
2 B B B test@test1.com false

3) Now user click on the link at that time we transfer the data from TempUser to User (here is the case only one record we be transfer.).

4) Create the simple store procedure. Put the below query into.

–-in the insert table (table name User)

–-destination table where the data (table name TempUser)

–-UserTempId pass

–-check the IsUserRegister Flag

insert into [User](UserName,Password,Address1,EmailAddress)

select UserName,Password,Address1,EmailAddress from TempUser where UserTempId =1 and IsUserRegister =‘false’

–-return the latest inserted row identity

select scope_identity()

Thanks

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s