SQL 2012 :: Scramble Data While Inserting Onto Secondary Table

Sep 24, 2014

Do we have any way to insert,update,delete data from one table and update the changes onto second table. Also, while updating records into second table, can the data be encrypted.

I tried using view and it can insert, update, delete without any issues. But if i tried to encrypt any fields after inserting data into view, I am unable to do it.

For ex:

Table A:
EMPID LNAME FNAME
1 ABC DEF
2 DCE SKL
3 CKL DOP

Expecting output: by scrambling Lname FILED

Table B - vIEW TABLE

EMPID LNAME FNAME
1 CBD DEF
2 ECD SKL
3 LKC DOP

Tried below methods

CREATE Tableb_vw ON TableB
Instead of Insert
AS
Begin
update TableA
set Lname = (
--UserName = 'User' + substring(convert(varchar(32), UsersTrID), 1, 8)
SELECT REPLACE(LEFT(Lname, 2), '''', 'Z') AS LNAME)
FROM TableA
end

What I would like to get:

1. Can we update base tables and encrypt second table data while inserting or updating data
2. If not supported using base tables, can we do using views to encrypt view data [Some fields]

View 0 Replies


ADVERTISEMENT

SQL 2012 :: Inserting Data Into Temp Table Using 2 While Loop

Apr 21, 2015

I want to insert data (month&year) from 2014 till now - into temp table using 2 while loop.

drop table #loop
create table #loop
(
seq int identity(1,1),
[month] smallint,
[Year] smallint

For some reason I cant not get 2015 data .

View 4 Replies View Related

SQL 2012 :: How To Transfer Login To Secondary Server And Assign Same Permission To Secondary DB

Jan 24, 2015

i have created a new login in primary server and provided dbowner permission to primary db.how do i transfer this login to secondary server and assign the same permission to secondary db ?

View 2 Replies View Related

Inserting Data Into Two Tables (Getting ID From Table 1 And Inserting Into Table 2)

Oct 10, 2007

I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
 Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
 This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!

View 3 Replies View Related

SQL Server 2012 :: Inserting Row From A Table Into Another Table From SSIS

Sep 18, 2014

I have a 2010 SSIS package where I am reading csv files with different fields and formatted data, I have created separate packages for each file and I am formatting the data to fit the final destination tables data elements, I've been instructed to create 7 separate packages to read each file and the format the data from the 7 csv files, and insert into their table tbl1, tbl2, tbl3...etc then, I'm taking a execute sql task and wanting to insert the tbl1, tbl2, tbl3...etc into destination table that will be the final table for all reports and other uses.

1- should I create a ID?
2- these files will be read once a month
3- I want to append the data, not drop and recreate each run,
4- It's 2012 SQL and 2010 SSIS

Each csv file is in a different format, some have 15 columns, other have 8 I have to parse the data, in SP to align with the fields in the destination table.

5- Can I force RowID to be the next auto gen number from tbl1,for the start of insert for tbl2, then last row of tbl2 for insert of tbl3???

View 0 Replies View Related

SQL Server 2012 :: Get Empty Data Set For Inserting Data?

Nov 11, 2014

I have 2 tables in my database.

one is Race table and 2nd one is Age Range.

I want to write a query where I can see all races and age range as column.

TblRace

ID, RaceName

TblAgeRange

ID,AgeRange.

There is no connection between this two table. I need to display result like below.

Race 17-20 21-30 31-40

A

B

I

W

How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.

View 1 Replies View Related

SQL Server 2012 :: Inserting Information From One Table Into Another

Aug 12, 2015

I am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into).

The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN, Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12.

The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc.

The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.

Example:

Old Table
Name | ID | Component1 | Component1_PIN | Component1_SN | Component1_Description | Component2 | Component2_PIN | Component2_SN | Component2_Description
Nest8 | 5682 | Case | 901834 | 237848117 | Black, rectangular | Memory | 9081234 | 5398798134 | NULL
Nest8 | 5978 | Case | 901463 | 237848138 | Black, rectangular | Processor | 2394875 | 2903857809 | Bad
Reds3 | 5683 | Memory | 2405 | 89752342 | Crucial | HardDrive | 92387595 | 457982234 | NULL
Bass | 5644 | HardDrive | 79872346 | 5321675789 | NULL | Case | 10984528 | 3498769872 | NULL

I am not sure how to loop through and grab each part and place it in the column it needs to be while keeping it with the ID.

View 4 Replies View Related

SQL 2012 :: Deadlock When Inserting And Deleting Items From Same Table

Nov 3, 2015

I am getting a number of deadlocks when inserting and deleting items from the same table.

The delete statement has a U lock and awaiting an IX lock on an index that covers the column in the where clause.

The insert statement has a IX lock and awaiting a U lock on the same index.

The delete statement is deleting about 5000 rows, where as the insert statement is inserting a single row.

Both these statements are found in stored procedures being called from LINQ to SQL.

I am wondering if there is a way I can prevent the delete statement taking the U lock out?My thinking being if the delete didn't take out the U lock then it would not deadlock with the insert. Are there any hints I could use to avoid the particular lock above?

I have seen various examples of multiple updates causing a deadlock, which can be fixed by adding multiple indexes. However, as I am inserting and deleting rows I imagine that all the indexes will need to be updated by both operations.

I have inherited the architecture and don't have the time to redesign everything at present. My backup plan is to deprioritize the delete and build in a retry mechanism.

However, it would be really good if I could find a more elegant way to handle deleting and inserting rows at the same time.

Deadlock trace information below...

11/02/2015 22:21:26,spid21s,Unknown,waiter id=process1cc9c68558 mode=IX requestType=wait
11/02/2015 22:21:26,spid21s,Unknown,waiter-list
11/02/2015 22:21:26,spid21s,Unknown,owner id=process203f31b498 mode=U
11/02/2015 22:21:26,spid21s,Unknown,owner-list
11/02/2015 22:21:26,spid21s,Unknown,pagelock fileid=1 pageid=721673 dbid=6 subresource=FULL objectname=PerforceReports_Staging.dbo.DebugReport id=lock1663f5d900 mode=U

[Code] ....

View 1 Replies View Related

SQL 2012 :: Delete Old Data From Multiple Tables Before Inserting New

Jun 9, 2014

My requirement is before inserting new data, we need to delete the old data based on the input in 4 tables.

For this one I need to write 4 individual delete statements.

Is it possible to delete rows from multiple tables in single statement in SQL Server 2012 by using joins?

[URL] .....

I am looking similar, I tried by keeping 4 table aliases in delete statement but it is throwing synatx error

View 2 Replies View Related

SQL 2012 :: Store Procedure - Inserting Same Data Into Two Tables

Nov 13, 2014

In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB

insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp

And then got an error for code below.

"Multi-part identifier "dbo.orderB.OrderCity" could not be bound

IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END

View 5 Replies View Related

SQL Server 2012 :: Inserting Record In Table - Trigger Error

Aug 6, 2014

I am inserting a record in XYZ table(DB1). Through trigger it will update ABC table(DB2).

I am getting error when doing above thing. What are the roles to be set to user to avoid above problem.

View 3 Replies View Related

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

SQL Server 2008 :: Inserting Data From Staging Table To Main Table

Feb 23, 2015

I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.

View 2 Replies View Related

SQL 2012 :: Secondary Replica In AG

May 27, 2015

in my secondary server the database which is in restoring state , when i checked in always on dash board "This secondary database is not joined to the availability group" ,

View 3 Replies View Related

Inserting Distinct Data From One Table In Another Table, How?!?Really Urgent And Needing Help!!!

May 24, 2007

 Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin    select @idAp =idAp from OLTPLocalization  where idAp not in (select idAp from dimLocalization)    select @macAp = macAp from OLTPLocalization where idAp = @idAp    select @building = building from OLTPLocalization where idAp = @idAp    select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin    insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!

View 1 Replies View Related

Transact SQL :: Table Structure - Inserting Data From Other Temp Table

Aug 14, 2015

Below is my table structure. And I am inserting data from other temp table.

CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,

[Code] ....

Now in a next step I am deleting the records from #revf table. Please see the delete code below

DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2

[Code] ...

I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:

SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],

[Code] ....

If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.

View 5 Replies View Related

SQL 2012 :: AlwaysOn Two Secondary Replicas

Apr 14, 2014

While configuring SQL Server 2012 Cluster with Always on, why do we need two secondary replicas.

The attached link will describe the actual query. [URL] ....

View 2 Replies View Related

SQL 2012 :: Load Log File To Secondary DB

Mar 11, 2015

I am after T-SQL code which will simply load the next T-log backup file from a network share folder to a warm standby db on a secondary server.What is needed is a Third server (server x), to participate in log shipping (MULTIPLE TARGETS).

Primary SERVER (SERVER A)
Secondary SERVER (SERVER B) Log shipped to via GUI.
THIRD SERVER (SERVER X) which will contain the same log shipped db from server A.

This will simply restore the logs from a network share to keep the db up to date.

View 3 Replies View Related

SQL 2012 :: AG Making Secondary The New Primary

Sep 23, 2015

"If we fail over a SQL AG group on a failover cluster from one node to another making the secondary the new primary, is there any reason why we would have to fail it back over to the old primary node?"

View 2 Replies View Related

SQL 2012 :: Slow Backup On HA Secondary

Nov 2, 2015

2 Boxes in HA, identical hardware (VMs) and both utilising identical storage systems (Nimble SANs).Was needing to do a copy only backup of a few databases for an escrow environment we have, so decided to perform this on the secondary box.Small ~10GB ish database first, seemed to be really slow, so opened resource monitor and noticed that disk read / write was getting limited to about 15MB/s so took a little while (the big one is over 1TB)

Tried this again on the primary it was done in seconds disk rate in the order of 250MB/s (same settings re compression etc).. I decided to make a copy of the backup file on the secondary, the copy disk to disk went in the region of 150MB/s , so it appears that SQL Server is the bottleneck?

View 3 Replies View Related

SQL 2012 :: AlwaysOn - Readable Secondary Option

Aug 21, 2014

In always on under availability group server name properties can see the option Readable Secondary. In that for secondary server the Readable Secondary Option is YES and for Primary it is Read-Intent. I believe Read-Intent allows only read only connections and YES allows all user connections.

View 0 Replies View Related

SQL 2012 :: AlwaysOn - Readable Secondary Options

Aug 21, 2014

In always on under availability group server name properties can see the option Readable Secondary. In that for secondary server the Readable Secondary Option is YES and for Primary it is Read-Intent. I believe Read-Intent allows only read only connections and YES allows all user connections.

What exactly it means for the primary and secondary?

View 3 Replies View Related

SQL 2012 :: Create Secondary Database On The Server?

Dec 12, 2014

How can I create secondary database on the server i.e. .ndf file?

View 2 Replies View Related

SQL 2012 :: Connecting To Readable Secondary With SSRS

Dec 21, 2014

I have been working with a BI colleague to access the readable secondary through SSRS. For some reason it keeps complaining that ApplicationIntent is not recognized keyword. I am starting to think it's something to do with the driver for SSRS.

View 1 Replies View Related

SQL 2012 :: Dump Every 1 Minute In Secondary Replica?

Jun 9, 2015

SQL is generating dump every 1 min with error

EXCEPTION_ACCESS_VIOLATION reading address 0000000000000020 at

Server is running SP2 CU2.

View 1 Replies View Related

Replication :: Remove Secondary Available Replica From 2012?

Oct 14, 2015

Environment:-

Windows 2012 R2,
SQL 2012 (Primary Replica)
SQL 2012 (Seondary Replica)
SQL 2012 (Secondary Replica over WAN site)

ThereĀ are database replicating on three SQL servers. WAN line is having performance issue because of limited bandwidth I have to remove SQL secondary replica over WAN site temporarily and add it again later when the WAN line is upgraded with between bandwidth What is the best practice to remove secondary replica and replicating database and add later from SQL management studio without interruptionsĀ on databases?

View 6 Replies View Related

Inserting Data From One Table To Another

Aug 5, 2007

Hi, I have two tables in a data base and i'm inserting the data from one into the other...no probs. What i was wondering is, in table1 i have an column of ID numbers. In the table2 i have a matching set of ID numbers. There are 5 PersonID numbers in table one and 10 in table two, the same 5 numbers as in table1 but each ID has a duplicate with different data in the two rows.

SAMPLE:

INSERT
INTO Table1 (PersonID, level1, Level2, Level3, Level4)
SELECT PersonID,level1, Level2, Level3, Level4
FROM Table2

When i insert the data into table1 it leaves the first 5 rows of data as null and then populates the table with all the data from table two. Is there anyway of preventing these first 5 columns from remaining empty....

I hope that makes sense

View 3 Replies View Related

Inserting Data Into Another Table

Jan 22, 2008



Hi, I'm fairly new to SQL Server 2005.
i have a table that creates customer id's along with other data (let's call it Customer)
I would like to take the same customer_id data and import it into a different table (HQ_Customer) the new table also has different column names.

Is there a script that can be used for this problem?

View 5 Replies View Related

Inserting Data From One Table To Another

May 15, 2006

Well, I think this should be an easy question, but here goes:
I'm taking data from one table and inserting it into another. According to the SQL Server Mobile Book Online, the syntax goes like this:
INSERT INTO Table1 (col1, col2) SELECT (col1, col2) from Table2

So while I can do this with my tables:
INSERT INTO sensor_stream (sensor_stream_id) SELECT (sensor_stream_id) FROM sensor_stream_temp

If I add any more columns, I get an error. Like this:
INSERT INTO sensor_stream (sensor_stream_id, sensor_stream_type_id) SELECT (sensor_stream_id, sensor_stream_type_id) FROM sensor_stream_temp
The error is "There was an error parsing th equery. [ Token line number =1, Token line offset = 98, Token in error = ',' ]"

Anyone have any ideas about why I cannot do more than one column at a time?
TIA,
-Dana

View 4 Replies View Related

How To Create Table A By Inserting All Data From Table B?

Jan 16, 2005

Hi,

Anyone can help me?

How to create Table A by inserting all the data from Table B?

Cheers,
Daniel.

View 1 Replies View Related

Inserting Data Into A Table Referencing PK From Another Table

May 12, 2008

How do i insert data into multiple tables. Lets say i have 2 tables: Schedules and Event

Schedules data is entered into the Schedules Table first

then now i need to insert Event table's data by refrencing the (PK ID) from the schedules table.

How do i insert data into Event table referencing the (PK ID) from Schedules Table ?


Fields inside each of the tables can be found below:




Event Table
(PK,FK) ScheduleID
EventTitle
AccountManager
Presenter
EventStatus
Comment

Schedule Table
(PK) ID


AletrnateID
name
UserID
UserName
StartTime
EndTime
ReserveSource
Status
StatusRetry
NextStatusDateTime
StatusRemarks

View 2 Replies View Related

SQL 2012 :: AlwaysOn Secondary Replica - Resolving State

May 21, 2013

I have an AlwaysOn Availability group configured between 2 nodes (Synchronous)

Automatic failover was working fine until recently

I can failover between the nodes manually but automatic failover doesn't seem to be working. In my earlier test, I would shut down the SQL Service on the primary and within seconds, the secondary replica would take over. Recently I have performed the same test and the secondary replica enters the resolving state and the DB in unavailable.

I have tried everything here: [URL] ....

The only change I made was changing the availability mode from Synchronous to Asynchronous - Could that be the cause?

View 3 Replies View Related

SQL 2012 :: Clustering AlwaysOn - Primary And Secondary Replica

Apr 11, 2014

Looking for info on ole/requirement of primary and secondary replica under SQL Server 2012 AlwaysOn.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved