How To Put Only Changed Fields Into A New Table

Mar 6, 2008

Ha all again.
I have this issue:
I have 2 tables:
t1 (id,field1,field2,field3)
and t2 (id,field_name,field_value)

is possible with trigger to insert only changed fields on t1 to t2 like

(changing field1 on t1 with id 21 cause a new row in t2 with fields id=21,field_name="field1", field_value=field1.value)

i hope you understand what i look for.

Is this trigger solution ?

CREATE TRIGGER MAP_CHANGESON t1AFTER INSERT,UPDATEBEGIN
DECLARE @ID SOMEDATA;DECLARE @FIELD1 SOMEDATA;DECLARE @FIELD2 SOMEDATA;DECLARE @FIELD3 SOMEDATA;SET @ID=(SELECT ID FROM INSERTED)SET @FIELD1=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD1<>T2.FIELD1)SET @FIELD2=(SELECT FIELD2 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD2<>T2.FIELD2)SET @FIELD3=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD3<>T2.FIELD3)IF @FIELD1<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD1')IF @FIELD2<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD2','@FIELD2')IF @FIELD3<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD3')RETURNEND

View 7 Replies


ADVERTISEMENT

Auditing:Extracting Changed Fields From Inserted Table

Jan 15, 2007

Hello,I'm creating an audit table and associated triggers to be able to captureany updates and deletes from various tables in the database. I know how tocapture the records that have been updated or deleted, but is there any waythat I can cycle through a changed record, look at the old vs new values andcapture only the values that have changed?To give you a better idea of what I'm trying to do, instead of creating acopy of the original table (some tables have many fields) and creating awhole record if a type or bit field has been changed, I'd like to onlycapture the change in a single audit table that will have the followingfields;AuditID int INDENTITY(1,1)TableName varchar(100)FieldName varchar(100)OldValue varchar(255)NewValue varchar(255)AuditDate datetime DEFAULT(GetDate())Any direction would be greatly appreciated.Thanks!Rick

View 6 Replies View Related

Updating Only Changed Fields

Sep 6, 2007

My client wants to minimize concurrency issues by only updating fields that the user actually changed. Whereas most update stored procedures I have created just updated all of the fields WHERE key=value, they want to update individual fields.

What I came up with so far does not look very efficient and I was wondering if anyone has suggestions for improvement.

Here is a snippet from the stored proc script:



Code Snippet
CREATE PROCEDURE dbo.DriverSaveChangedColumns
@DriverNumber VarChar(10) OUTPUT,
@CompanyName VarChar(50),
@DriverName VarChar(50),
@Address1 VarChar(50),
...
IF @CompanyName <> @Org_CompanyName

BEGIN

UPDATE Driver
SET DR_CompanyName = @CompanyName
WHERE DR_DriverNumber = @DriverNumber AND DR_CompanyName = @Org_CompanyName
END

IF @DriverName <> @Org_DriverName

BEGIN

UPDATE Driver
SET DR_DriverName = @DriverName
WHERE DR_DriverNumber = @DriverNumber AND DR_DRiverName = @Org_DriverName
END
...



Any suggestions?

The only other thing I thought of is to forget the stored procedure and do inline SQL ... but I'd rather have a proc.

Thanks!

View 15 Replies View Related

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

Transact SQL :: Dynamically Alter Schema Of Stage Table When Source Table Structure Changed?

Oct 25, 2015

we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.

View 4 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

Transact SQL :: Update Table From Linked Table If Something Has Changed?

May 20, 2015

Is it possible to check/notify if something (record) has changed in the linked table (from linked server) and automatically update table in local one?

View 4 Replies View Related

Changed Table Structure

Mar 1, 2004

Hi all,

How can i get the information as to when was the last my particular table structure was changed (for ex. adding of a column/dropping a column)

Pls let me know
TIA

View 3 Replies View Related

Can Table Ownership Be Changed?

Jul 20, 2005

When I create tables in SQL I can specify dbo as the owner using thesyntax below but when using the upsize wizard in Access 2000 I owneverything. Is there a way that the system administrator can changeownership after the fact?CREATE TABLE dbo.mytable (c1 int not null)Thank You,Randy KJoin Bytes!

View 1 Replies View Related

How To Check A Table Whether Data Is Changed Or Not..??

Jan 30, 2006

Hi..

I got 10 Tables with data in it for 100 Loans. The data can be Good and Bad .....

I had a Update Proc which Updates the 10 tables with the Good data what ever i pass...for this 100 Loans
Tha Proc will update the existing data in all tables whether it is Good or Bad Data.

when the updating is completed I want to know what are the Loans that were updated where there is a Bad Data in atleast one Field of the 10tables.

I don't want to check field by field in each table.... if there is a bad data and my proc updated with a Good Data i want to know that Loan.

Can any one has an idea on this,,,,

Thanks
Bob

View 2 Replies View Related

How To Find Out If A Spesific Table Has Changed?

Feb 28, 2008

Hi,

I have a small question regard how to find out if a specific table has chanced.
Is there any (sql)function inside MSSQL2005 that I can use to find if a spesific table has changed? I have heard of timestamp, but I don't know if that will solve my problem? Is there any thing in the sys.tables that kan help me, just do a fast query and check if a table has changed?

I'm developing a web-application with AJAX.NET, and I have UpdatePanels with datalist. When the tables in the db gets big the application is henging for a sec or two when it receiving the data, and when the timer (refresh rate) is 2 min it is not good! So my plan is to check if the table has chanced since last transfer of data, and if it has it will download the data, if not do nothing.

Jonny

View 4 Replies View Related

Subscriber Pull- Hows It Know Table Changed.

Jan 7, 2000

When you setup a subscriber to a publication, that is pull the publication, does it copy all the rows or does it keep track of which rows changed.

View 1 Replies View Related

T-SQL (SS2K8) :: Get Recent Changed Value From History Table

Apr 6, 2015

I have a history table with the following values

CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)

INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22')
INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20')
INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')

[Code] ....

I need an output in the below format. I need the most recent changed value for any given UID. Need to get the below result

OUTPUT
UID PreviousDueDate CurrentDueDate
----------------------------------------
PT-01 2015-04-20 2015-06-30
PT-02 2015-04-22 2015-04-22
PT-03 2015-04-18 2015-04-22
PT-042015-04-222015-04-18

View 4 Replies View Related

Table Schema Changed At Runtime In Cursor

Apr 8, 2008

none

View 3 Replies View Related

Column Compare In Same Table And Show Only Changed Value

Aug 18, 2014

How can I compare column in the same table and show only column and value that has been changed.

declare @t table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))

insert into @t
SELECT '11111','Text1','Text2','Text3'

insert into @t
SELECT '11111','Text1','Text2','Text4'

END RESULTS:

Column Name Old New
Column 3 |'Text3' |'Text4'

View 9 Replies View Related

Find The Last Time The Data In A Particular Table Was Changed

Mar 27, 2008



Is there a Tool/Utility/StoredProcedure/Query/View that can tell me the last time the data in a particular table was changed?

That change can be the result of either an ADD , INSERT, or DELETE statement.

I am using SQL Server 2005.

View 3 Replies View Related

Using LIKE To Find Any Of Fields In One Table In Fields Of Another

Jul 31, 2013

I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).

Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.

View 5 Replies View Related

Can A Column Data Type Be Changed On A Replicated Table?

Dec 21, 2006

On sqlserver 2000 transactional replication:

How would I best go about changing a published table's column from smallint to int? I could not find anything about it in BOL or MS.com. I do not think EM/Replication Properties allows the change. I suspect I have to run "Alter Table/Column" on the Publisher and each Subscriber the old-fashioned way. Is that true?



Thanks!

View 3 Replies View Related

Could Not Complete Cursor Operation Because The Table Schema Changed

May 30, 2006

Microsoft SQL Server  2000 - 8.00.2039

Got this error:

Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000

Is this a known issue?  I suspect the application logic may cause this error. Please advise.

Thanks a lot!

 

View 16 Replies View Related

SQLSERVER 2000,How Can I Detect The Last Time Where The Table Has Changed (updated)?

Jan 2, 2008

Hi, I wonder where I can see last update over a table. Maybe sqlserver write some information about every table.

For example there are 300 tables in a database an I want to execute some query to see the last tables updated (list of tables updated > 2008-01-02 )

Hope to be clear,
Best regards
Ariel

View 4 Replies View Related

Altered Table-changed Column From Text To Varchar And Now Having Issues With Alter Switch

Feb 14, 2008

Working on partitioning a few large tables. One of the tables included a text column and the €œTEXTIMAGE_ON [PRIMARY]€? clause which would prevent the partitioning of this table. After some research we found that the data was legacy and no longer used. We updated the column on the affected rows to NULLS and altered the column to a VARCHAR(20)
When I attempted to run the ALTER TABLE SWITCH I encountered the error
Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresultsjoe' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part'.
After a lot of grief and testing I determined that the message was bogus and the real issue is that the 'sys.tables' still has €œlob_data_space_id€? with a value of 1 for this table.
I created a copy of the table with the text column altered to varchar and one with just the varchar to begin with. After copying data from the original table, I tried to run the alter switch. It failed once again for the text column altered to varchar table, but it worked for the varchar from the start.

Since it appears that this value is causing my issues, is there anyway to update the table in place. I know I can BCP the data out, but that would take too long and would defeat the advantage of using the alter switch method.

BOL States:

The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. This means we cannot update the table manually.

Thanks

View 1 Replies View Related

Insert / Update In Master Table And Also Save A History Of Changed Records : Using Data Flow/simple Sql Queries

Feb 9, 2007

Hi,

My scenario:

I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to

1) separate the master (static) data from the flat files,

2) check whether that data is present in the master table, if not then insert that data into the master table

3) If data present then move that existing record to an history table and then update the main master table.

All the 7 fields need to be checked to uniquely identify a single record in the master table.

How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.

Thanks in advance for your help.

Regards,

$wapnil

View 4 Replies View Related

Finding Rows In One Table But Not In Another Table Based On 2 Fields

Apr 23, 2008



I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.

View 7 Replies View Related

Creating Table Fields Dynamically From Another Table Row

Aug 31, 2006

Hello all:

Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.

The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.

create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )

Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..

create table emp (
empid int, empname varchar(100), empage int....)

The table should look like as:

Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==

I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]

A small rough snippet of the code will be appreciated....

Waiting for replies........
saby

View 1 Replies View Related

I Want To Update My Table But It Has 30 Fields So How Can I Modify My Table I Have To

May 29, 2008

I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it


I need that query for alter table

in it


reply me soon

View 14 Replies View Related

HT Insert Table A Record Fields Into Table B

Jun 13, 2008

All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.

How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)

Thanks!

-Kurt

View 1 Replies View Related

SP To Add Fields To Table

Feb 14, 2006

I want to let a user a fields and select the data type and size from a short list. For example: nvarchar(?), Money, Int, or Bit. These are most likely the only choices for now. There will be a web page that lets them enter a field name and select a data type.
Can someone suggest how the sproc might look? Here is what I have so far.
CREATE PROCEDURE [dbo].AddCustomField ASALTER TABLE MyDataSQL.dbo.tblCustomFields ADD @FieldName @FieldDataType NULLGO
Thank you,

View 1 Replies View Related

How Do I Copy A Fields From Another Table Into A New Table?

Oct 23, 2007



I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.


Table Fields

Tbl_Date_Dimension [Date_Dimension_Year], [Date_Dimension_Period], [Date_Dimension_Fiscal_Week]
Tbl_Report_Level Report_Level_Id
Tbl_Customer Customer_Code
[Sales Fact] [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id


The new table is called Tbl_Sales_Growth
Here's the information on the new table:






Code Block
[Date_Dimension_Year] [int] NOT NULL,
[Date_Dimension_Period] [int] NOT NULL,
[Date_Dimension_Fiscal_Week] [int] NULL,
[Report_Level_Id] [int] NOT NULL,
[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,
[Quantity] [decimal](18, 0) NOT NULL,
[Gross turnover] [decimal](18, 0) NOT NULL
) ON [PRIMARY]




View 2 Replies View Related

Access To Fields In A Table

Jun 25, 2006

I am trying to access the fields in a table using Visual Basic in Web Developer .NET

View 1 Replies View Related

Dynamic Table Name And Fields

Jan 13, 2007

I need to get the field values of a table (name will be dynamic).Then assign those values to properties in a class.Let's say I will get the table name dynamically.dim tblName as string = "tablea"The 2 tables can each have 25 fields or so.I need a way to select the amt and email field values from tblName.  Without saying "select job_amt, job_email from ...Is there someway to get the values based on the column name.  So if the column name has amt and email, then give me those values.Maybe loop through the datatable - then for each column -- if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 thenthen drop  that column from the datatable.ex of table structure<u>tablea</u>job_idjob_amtjob_email<u>tableb</u>dance_idamt_dancedance_email

View 3 Replies View Related

Number Of Fields In A Table?

Jan 4, 2004

My users login from the tbl_users table.

I would like to store all information on them in this table but that amounts to quite a lot of fields (about 50) and some of these will store a lot of HTML text in them.

Is it best to split the table up for performance reasons or will it make little difference?

Thanks,
JB

View 4 Replies View Related

Table Fields Name Query

Aug 9, 2004

Hello all,

How can i write a sql statement that can give me the name of the fileds in a specified table.

Eg: if i have a table name "table1" , which has the fields "field1", "field2",........ How can i get these fields names using sql query.




any help.

View 25 Replies View Related

Number Of Fields In A Table

Mar 6, 2000

I'm new to SQL Server 7. Is there a limit to the number of fields that a table can have?

View 1 Replies View Related







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