Wednesday, August 28, 2013

Convert .mdb to SQL database

There's also the SQL Server Import and Export Wizard 
  1. Open SSMS.
  2. Create a new empty database.
  3. Right-click the database in the Object Explorer.
  4. Select Tasks | Import Data... from the context menu.
  5. Change the data source to Microsoft Access.
  6. Browse for the file name.
  7. Click Next...
  8. That's it :-)

Tuesday, August 27, 2013

Create a Trigger

Create a trigger that will insert the old and new Passwords into a Table.
Open SSMS--Select database[Master]-->Select tablename[Logins]-->Expand the table--> you can find the Triggers-->select that and right click to create New Trigger--> change the trigger as we need
In this example below am showing how can we store/insert the Password into Audit table[Old/New].
Audit is the new table with columns[OldPassword,NewPassword,DateTime].
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,K.Sai Krishna Yadav>
-- =============================================
CREATE TRIGGER AuditInfo 
   ON  Login
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
declare @OldValue Varchar(50)
declare @NewValue Varchar(50)
select @OldValue=password from deleted
select @NewValue=password from inserted
Insert into Audit(AuditInfo,OldValue,NewValue) values (GETDATE(),@OldValue,@NewValue)
SET NOCOUNT ON;
    -- Insert statements for trigger here
END
GO
The above Trigger will fire when an INSERT,DELETE,UPDATE happens on Logins Table.

Friday, August 9, 2013

Query to update multiple rows with particular data/string in SQL

update tablename set columnname=REPLACE(tablename.columnname,'string to search','string to replace')

Consider the below table with name 'Employee'
ID Name
1 sa
2 sa krishna
3 sa kumar
4 sa ganesh

After executing the update statement
update Employee set Name=REPLACE(Employee.Name,'sa','sai')  
then the final result will be
ID Name
1 sai
2 sai krishna
3 sai kumar
4 sai ganesh