i have two databases with name "Student" and "Student1"
The "studentDetails" table is bellow
SdtId | StuName
|
numeric | varbinary(100)
(100) |
|
|
a Certificate name("stuCert") and Symmetric("stuKey") Key are exist
for encripting the datas in sql server
code for insert
@StdName NVARCHAR(50)
@StdClass NVARCHAR(50)
ALTER PROCEDURE [dbo].[StudentInsert]NVARCHAR(50) ,
BEGIN
DECLARE @OMK nvarchar(1000)
DECLARE @OSK nvarchar(1000)
DECLARE @CMK nvarchar(100)
SET @OMK = 'OPEN MASTER KEY DECRYPTION BY PASSWORD =''abc'''
SET @OSK = 'OPEN SYMMETRIC KEY stuKey DECRYPTION BY CERTIFICATE stuCert'
SET @CMK = 'CLOSE MASTER KEY'
EXEC (@OMK);
IF @@error = 0
BEGIN
EXEC (@OSK);
INSERT INTO Student VALUES (@StdName,ENCRYPTBYKEY
(KEY_GUID('stuKey'),@StdClass))
EXEC (@CMK)
END
END
code for select
DECLARE @openMasterKey nvarchar(4000);
SET @openMasterKey='OPEN MASTER KEY DECRYPTION BY PASSWORD =''abc''';
EXEC (@openMasterKey);
IF @@error = 0
BEGIN
OPEN SYMMETRIC KEY stuKey DECRYPTION BY CERTIFICATE stuCert
SELECT dbo.Student1.StdId, dbo.Student1.StdName, cast(CONVERT(NVARCHAR(50),DECRYPTBYKEY(StdClass)) as NVARCHAR(50)) AS StdClassFROM dbo.Student1END
i want to copy datas from table "studentDetails" from database "Student" to database "Student1"
please help me...
Loading

Sam HobbsPosted Mar 31, 2011, 8:42 PM
Suthish NairPosted Mar 31, 2011, 3:29 PM
Mahesh ChandPosted Mar 31, 2011, 10:32 AM
INSERT INTO .... FROM SQL query.
Mahesh ChandPosted Mar 31, 2011, 10:30 AM
1. Open connection with Student Database
2. Open connection with Student1 Database
3. Get all studentDetails table data in a DataTable
4. Loop through DataTable from first row to the last row
for each row in studentDetailsTable.Rows
// Get row
// Create a new row or SQL statement or call SP
// Execute this against new database Student1
next