How To Save New / Updated Records Only In To Destination Tables
Jun 14, 2006
Hi
I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records.
So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records.
How can i do that in SSIS 2005 using different data flow tasks ?
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
If I update a recordset a group of records using dynamic SQL where I update the TOP n records, is it possible to get the set of records that was updated?
CREATE PROCEDURE usp_Structural_ScheduleComponent @cProject char(7), @cComponentID char(10), @iPour int, @iQuantity int, @iAvailable int OUTPUT, @dtCast datetime OUTPUT AS
SET @dtCast = convert(char(10), getdate(), 120)
DECLARE @cSql varchar(500) SET @cSql = 'UPDATE tbStructuralComponentSchedule SET PourNumber = ' + CAST (@iPour AS VARCHAR) + ', ScheduledDate = ' + '''' + CAST(@dtCast AS VARCHAR) + '''' + ' WHERE EntryID IN ( SELECT TOP ' + CAST(@iQuantity AS VARCHAR) + ' FROM tbStructuralComponentSchedule ' + ' WHERE fkProjectNumber = ' + '''' + @cProject + '''' + ' AND fkComponentID = ' + '''' + @cComponentID + '''' + ' AND IssueDate IS NOT NULL' + ' AND ScheduledDate IS NULL' + ' ORDER BY EntryID DESC)'
EXEC(@cSql) IF(@@ERROR <> 0 OR @@ROWCOUNT < = 0) RAISERROR('Failed to add components to pour!',16,1)
SELECT @iAvailable = SUM(CASE WHEN IssueDate IS NOT NULL AND ScheduledDate IS NULL THEN 1 ELSE 0 END) FROM tbStructuralComponentSchedule WHERE fkProjectNumber = @cProject AND fkComponentID = @cComponentID
GO
-- Is there a way to return the recordset that were modified in the update?
Hi all, I have a data of applicants. Everyday we dump this data into SQL server. Now I need to generate reports everyday so that we can track how many records get updated everyday. Now the thing is that the applicants are in various stages. So my reports need to track the how many applicants changed from stage "abc" to "pqr" and how many changed from "pqr" to "xyz". Now it is not necessary that all the records change stages everyday.
My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table. Maybe a tool someone knows of?
I need to create a trigger to save every record which has been updated in a table (e.g. "Contacts") into another (e.g. "Shadow_contacts). So basically something like a versioning on database level. I have no experience with it and any precise hints/solutions would be very appreciated
Hi I am using sql server 2005 express and would like to keep all my fields from being both updated and deleted.
In other words, once I create a new record, I would like to have it protected from being deleted and I dont want the field values to be updated/changed from the values initially entered. Is there a way to this without running triggers or changing database permissions and user roles?
I tried making the database read-only, but then of course i cant add new records.
I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD..
IF anyone could shead some light on what I'm doing wrong that would be great.
I am having a hard time understanding triggers. My goal is to put a trigger on table x where records are UPDATED or DELETED. When this trigger fires I need to take the record ID and put the ID modified record into table y with the date modified. so basically logging the recordid changed with the getDate()
I don't quite understand how to get the rowid of the modified record.
i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
We have a third party process that runs and updated several SQL tables. Is there any way to find out what tables are being updated and store it in another table?
I have a front end inteface that adds data to tables in a SQL database.My question is:Is there an easy way of finding out what tables are affected by the update? Is there a sp_ or DBCC command that would give me the update or altered tables?
In my data archiving process , I would end up deleting hunders records from the production databases but would that help me save some DISK space immediately??? Should I run some DBCC command to get some disk space ?
if SO ..!! What should I do after deleting the records..????
I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2000 as a back End.
Actually I am fetching data from other database(named as retailexcel but in same server). I have wriiten a view (in retailexcel database) to get required data from four table of other database. My problem is when I fetch those data(about 40000 to 50000 record) It taking to much time. I tried catch feature of asp.net2.0. Time reduced but not significantly. So I am trying to import those tables to my database (named Inventory) from that retailexcel.
Now problem is every day or two, record of those tables changes. How can I keep update my tables (which are importing from retailexcel)
Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain. Any ideas on how to keep the data updated on the website?
How do I insert unrelated statistical data from three tables into another table that already exist with data using an insert or update stored procedure? OR... How do I write an insert/Update stored procedure that has multiple select and a where something = something statements?
This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this....
CREATE PROCEDURE AddDrawStats AS INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)
SELECT WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),
TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),
Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),
RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )
FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID
I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ??
I would like to propagate an event signal to an external applicationwhen a table in my MSSql2000 server is updated.Prog A; I have an external application adding records to a table.Prog B; I have another external application using this table as well.When Prog A creates a new record in the Table, how can I have Prog B benotified of the event without polling the table or creating a linkbetween Prog A and Prog B.Thanks.
Greetings from a SSIS newbie still on the learning curve...
I have a SQL query OLE DB Source that yields a result set that I'd like to put into a SQL Server Destination, but only those records that don't already exist in the destination.
Is there a recommended (read: easy) way to accomplish this? Perhaps a handy transformation?
I have tried to incorporate a subquery in my source query along the lines of:
SELECT fields FROM table1 WHERE keyfield NOT IN (SELECT keyfield from table2)
which works in design time but fails at the server with a cryptic:
"Error: Unable to prepare the SSIS bulk insert for data insertion."
and
"Error: component "<component>" (16) failed the pre-execute phase and returned error code 0xC0202071."
Don't mean for that to cloud the issue, though. I would appreciate any help given.
We have a SQL 2000 DB1 ( publisher) which is replicated using transactional replcation onto the secondary server DB2 ( subscriber). we have identity columns on the DB1 and we created those tables with 'not for replication' clause. we skipped the following errors in the replication profile '2601:2627:8102:20598'. on DB2 ( the subscriber ) some records are missing in some tables . ( I verified this with record count differences for some tables in both the servers DB1 & DB2.
How to find out what are all the records missing & the cause.
I have listed the msrepl_errors table on DB1 , distribution databases all I'm seeing there are error code 8102 , unable to update identity column.
Short Question: How do I delete all records from a destination table prior to appending new data to that table?
I am working with a SQL database that was migrated from MS Access. All relationships, primary keys, and identity columns have been set identically to the MS Access database values. The MS Access database is still being used as the database of record until the SQL database is fully functional with front-end, etc.
I want to delete the information stored in all the SQL tables, and then append the MS Access values to the SQL tables. I was able to write delete and append queries in MS Access to correctly transfer data to the SQL tables. However, I would prefer doing this through SSIS because I have several other sources of data to move to a SQL Server database and most of those other sources are not a MS Access database.
Due to relational entegrity settings, I need to delete the records from 8 tables in a specific order. I have tried independent control objects for each of the 8 tables with data flow objects of either "OLE DB Command" or "OLE DB Source" with the SQL command as "Delete From TableName". Results of the debug indicate everything is "green" but no records were deleted fromt the tables.
I am having a brain-fart or something. I need help!!!
I'm developing a transform that selects data from a SQL script, then transforms that data, and then needs to update the data back into the same source table. The transform is working great, but I can't figure out the update table part. I've got it adding the transformed records to the table, but that's not what I need to do. I need to update the table with the changed data.
Any sample code or blog links would be greatly appreciated!
I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.
The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?
Am I correct in the way I am doing or there is something better to handle this?
I have an application written in Visual Basic 2008, using a SQL Express 2008 database. This application runs in several locations on stand-alone PC's and there is no network connecting them. I have to maintain a SQL database in a central location an would like to be able to distribute updated tables from that central location by using an USB stick and replace the tables of the same name in the different locations with the updated ones on my USB stick. This I would like to do using code within my VB2008 application.
Can someone please help me on the way to achieve this. Is it possible to give some concrete code examples, as I am a newbie as a SQL Server user.
Writing a Control flow that inserts records worked fine, using lookups and all, but i can't find somewhere an example on how to update records of even delete records.
I'm working on databases where statistics of some indexes (tables) are changing too frequently. Once I update them manually, one minute after they get 10-20% change, and five minutes after they get over 100% change. Tables get updated very frequently (multiple times in a second).
When I run a query to read from sys.stats, sys.dm_db_stats_properties and other dynamic views, I see that they were last updated when I did it manually, but the change rate overpassed the 500+20% (tables have multiples of 10K rows). Auto create and update statistics are set to true on all databases, and I don't know why sql server does not do that automatically.
I'm developing a credit card application, and would like to store some sensitive data in a table temporarily, but be absolutely positive it either never goes into the log file in the first place, or is securely deleted from the log file when I delete the data. There's millions of other bits of data in the log, so truncating or otherwise disposing of the whole log is not an option.
In particular, I'm capturing the credit card mag stripe at the beginning of a transaction, and saving it while the work is done to complete the transaction. (ie, a pilot walks in, says "Top off the G4 with JetA, here's my card, have it ready when I get back.") We want to save the swipe info for maybe a couple of hours (it is encrypted during this time), use it for the authorization, then throw it away as per credit card industry rules.
Is there any way I can save the encrypted text string in a normal MSSQL field (Varchar(200)), have it stay around for a few hours as the user logs in and out of the database, then delete the record and be sure it's nowhere in the log?
TIA, George Lehmann Horizon Business Concepts, Inc. Broken Arrow, OK
hi all i have a database in sql server 2000 and vb.net forms when i send arabic data through forms, they save to database as ?????? i had create my database with arabic collation and save my forms with unicode but that problem exists any help ? imergency help is needed ! thank u
