Source Data Base Deleted Records Reflection On Dataware House DB
Jun 27, 2007
Iam using Sql Server Integration Service to transfer the data. I have to methods to transfer the data.
Method -1
Daily cleaning Dataware house Data base and transfering the data from Source database.
Method -2
Only new rows transfering from the Source database to dataware house data base.
If i use first method, Any performence issues's will come in the future?. In future my source data will have upto 6 lacks records.
If i use second method, daily date based i can transfer the data. But if they delete any previous records from the source database how can i reflect the same in Data ware house Data base.
Can you please provide the solutions.
Regards
Hanu
View 3 Replies
ADVERTISEMENT
Jan 26, 2008
Hi pals,
I need some help from u.
This is datawarehousing related stuff.
I am having a source table as "test" and target table as "trg".
I need to extract the data in required format as per below loading instructions and then load the data into "trg" table.
Below sample data is only given one zipcode.There can be several codes.
drop table test
create table test
(
currentyear int,
district varchar(10),
school varchar(10),
rollno int,
zipcode varchar(10),
flag1_handicapped char(1),
flag2_disadvantaged char(1),
status varchar(10),
relation varchar(10)
)
/* inserted 11 rows */
insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','R' )
insert into test values(2005,'D1','S1',101,'530024','N','N','E','R' )
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','NR ')
select * from test
--- Structure of the target table
create table trg
(
cyear int,
district varchar(10),
school varchar(10),
RollNo int,
zipcode varchar(10),
type varchar(20), /* This is an extra column with hard coded values which we need to assume as Total,flag1_handicapped,flag2_Disadvantaged.For Every unique zipcode i need to GROUP BY these 3 values.
These values never come from the source table i.e "test".But we can make use of the 2 source columns "flag1_handicapped" & "flag2_Disadvantaged"*/
actaul_cnt int,
empl_related int,
empl_not_related int,
modified_date datetime
)
-- The below table shows what values should get loaded into trg table
----------------------------------------------------------------------
trg table columnvalue to be loaded Description
-----------------------------------------------------------------------
cyear test.currentyear
districttest.district
schooltest.school
rollnotest.rollno
zipcodetest.zipcode
type /* here we to load 3 rows with 3 values
This table contains some calculated columns. such as "actual_cnt","empl_related","empl_not_related" and so on...
Every calculation should be grouped by this "type" column.For reference the you can see the bottom output rows how they should look like.
The 3 valid values for this type column is "Total","flag1_handicapped","Disadavantaged".
"Total" means = All the records which satisfies the calculation.
"flag1_handicapped" means = All the records which statisfies the calculation and have test.flag1_handicapped = 'Y'
"flag2_Disadvantaged" means = All the records which satisfies the calculation and have test.disadvanatged = 'Y'*/
actaul_cnt This is a calculated column. The calc is as follows:
count of records grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)
empl_relatedThis is again calculated column. The calc is as follows.
count of records where status='E' and relation = 'R' grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)
empl_not_related This is again calculated column. The calc is as follows.
count of records where status='E' and relation = 'NR' grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)
modified_date getdate()
-----------------------------------------------------------------------------------------------
Here is the sample template which i felt like using to load the data. We need to modify this query littlt bit accordingly as per above rules.
select
currentyear as "CYear",
district as "District",
school as "School",
rollno as "RollNo",
zipcode as "zipcode",
count(*) "actaul_count",
sum(case when (status='E' and relation='R') then 1 else 0 end) "Emp_Related",
sum(case when (status='E' and relation='NR') then 1 else 0 end) "Emp_Not_Related",
getdate() "Date"
from test
group by currentyear,
district,
school,
rollno,
zipcode
/* Using the above query we need to load 3 rows into below target table whose structure is defined as follows */
------------------------------------------------------------------------------------------------------------------------
Expected Output Rows using above sample data
----------------------------------------------------
CYEAR|DISTRICT|SCHOOL|ROLLNO|ZIPCDE| TYPE |ACTUALCOUNT| EMPL_RELATED |EMPL_NOT_RELATED |MODIFIED_DT
-------------------------------------------------------------------------------------------------------------------------
2005 | D1 | S1 | 101 | 530024 | Total | 11 | 2 | 9 | 2002-01-26
2005 | D1 | S1 | 101 | 530024 | flag1_handicapped | 2 | 1 | 1 | 2002-01-26
2005 | D1 | S1 | 101 | 530024 | flag2_Disadvantaged | 2 | 1 | 1 | 2002-01-26
------------------------------------------------------------------------------------------------------------------------
But using above SELECT,i am able to get only row as output that to i am not able to show the "type" column in the output
2005 | D1 | S1 | 101 | 530024 | 11 | 2 | 1 | 2002-01-26 12:57:53.420 |
------------------------------------------------------------------------------------------------------------------------
Basically i am not getting how to build the Group by clause and displaying the type code using above rules.
Can anyone help me out in solving the problem.
Do we need to perform any UNION ALL ON test.flag1_handicapped and test.flag2_Disadvantaged columns.?
This is totally seems out of box for me.
Any help would be greatly appreciated.
Thanks in Advance.
View 4 Replies
View Related
Jun 6, 2007
I am into starting a new dataware house project where i will be loading data from several sources to respective tables within databases.Are there any basic things which i need to set in doing this.
Please let me know
View 6 Replies
View Related
Mar 25, 2008
hi all,
i unpacked starter site and dw.pup file and i got 34 reports provided by commerce server 2007. i want to edit these reports.
i followed these steps
1)created a "Report server project" in Visual studio 2005
2)In that project in "Shared data sources" folders i added "Startersite_Datawarehouse" of type "Microsoft SQL Server"
3)i added another shared data source name "Startersite_Datawarehouse" of the type "Microsoft SQL services analysis services"
4)later i added one of the reports to "Reports" folder by selecting "Add existing item".
But in design mode of that report when i clicked "Preview" tab i am getting the error
"an error occured during the local report processing. The item /CSDW_olap cant be found"
when i clicked "data" tab i am getting this below error
"Connection cant be made to database"
please help me in resolving this urgent issue....
Thanks in advance,
Archana Devi Papineni
View 1 Replies
View Related
Jan 29, 2008
Hi,
I have an SSIS package that runs each day from a live data source to create a data mart, which is then used for various things including SSAS and SSRS.
The problem is that certain records that will eventually go on to form fact tables are deleted from the live system (not a very robost database in the first place, hence the SSIS!) but these are not reflected in the SSIS transformation, creating plus figures when compared to the live system.
I currently use type 1 slowly changing dimension processes in each data flow (of which there are about 35) but I realise that this only updates records and does not delete.
The solution I have in place is to truncate the fact tables in the mart before the run starts using an Execute SQL task. This solves the problem though to me seems a little heavy-handed and renders the slowly changing dimension processes redundant (as it is currently only run once a day).
My question is, is there a better method of dealing with the above scenario? If there isn't, it would be a nice feature to add to future versions (*nudge nudge*).
Thanks in advance :-)
View 1 Replies
View Related
Jan 15, 2007
Hi, All
I'm only have permition to query table data and with local sql server installed. Also, capable link server from local machine to live sql server (Blue). Getting error type when ran a query that commons: Column name or number of supplied values does not match table definition. Others, ambiguous column name " ". Here are the information below before i ran the query.
In house data:-Table fields contain (address,city,state,zip,zip4,fips)
-All data type fields are character
-No Primary Key
Local machine table:
-Fipscodes (table) contain data was provided by customer that I inserted into my machine
and column fields (state,zip,fips) made Zip as Primary Key.
So, I wants to able run a query to retrieve data from live server by using some kind of join table with table(FipsCodes)locate in my local machine.
Query Statement:
SELECT h.lname,h.fname,h.street,h.city,h.state,h.zip,h.zip4,h.carroute,h.gender,
h.keycode,h.purprice,h.mortamt,h.phone,h.lender,h.recorddate,h.fips,h.pubmonth,
h.pubday,h.pubyr,h.trantype,h.condocode,h.transdate,h.ratetype,h.loantype,h.birth,
h.heritage,h.estcurrval,h.estcurreq,h.dpbc,n.state,n.fips
FROM blue5.Homeowner.dbo.homeowners AS h INNER JOIN FipsCodes AS n
ON h.FIPS = n.FIPS
WHERE state ='AL' AND fips='001' AND pubDate >= '12/1/2006' AND pubDate <='1/8/2007'
Error occur:
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'State'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FIPS'.
, please help me solve this issue and thank you very much everyone.
View 8 Replies
View Related
Sep 15, 2006
I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.
View 6 Replies
View Related
Dec 3, 2014
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
View 5 Replies
View Related
Jul 20, 2005
Hi,I used sp_addlinkedserver to link to a remote server through ODBC.When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL QueryAnalyzer. It returns 13705 records. But when I execute select * fromLinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify somecolumns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.It returns more records, 1213 records.I guess there is something limit the return storage, but I can notfind it.Any suggestion will be appreciated. Thank you
View 3 Replies
View Related
May 12, 2014
I have a table like this one, with PurchaseID be the primary key.
Code:
PurchaseIDItemIDQtyDatePurchased
1105152010-01-12
210742012-11-30
[Code]...
But I can't figure out how to get the max DatePurchased of the same ItemID.
View 4 Replies
View Related
Sep 17, 2007
I am using this code to delete records from a table in the database that has two fields (both are primary keys) I am not getting any error, but nothing gets deleted. Thanks for the help.
Code:protected void btnRemove_Click(object sender, EventArgs e)
{
Remove();Response.Redirect("RegisteredCompanies.aspx");
}protected void Remove()
{
//update database with filename
//Save Changes
connection.Open();using (SqlTransaction trans = connection.BeginTransaction())
{
try
{ SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "spRemoveCompany", new SqlParameter("@Userid", getGdUserID(Profile.UserName)),new SqlParameter("@BusinessID", getBusinessID());
}catch (Exception err)
{
// throw exception
trans.Rollback();
lblStatus.Text = "An error has occurred in the delete process. Please try again.";
}
}
Here is the stored procedure:ALTER PROCEDURE [dbo].[spRemoveCompany]
@Userid uniqueidentifier OUTPUT,
@BusinessID uniqueidentifier OUTPUT
ASDELETE FROM Associations2 WHERE Userid = @Userid AND BusinessID = @BusinessID
RETURN
View 4 Replies
View Related
Mar 22, 2005
I want to know what to do to recover the deleted records on a specific table. Unfortunately, the dba was unable to activate Trace feature and I am not familiar in using the transaction logs to retrieve data.
please help. :confused:
View 4 Replies
View Related
Sep 21, 2001
Does anybody know if SQL 2000 has a tool to roll back (udelete) records?
Let's say Payroll dept deletes 500 records by mistake...
I know of 2 products that do that (Computer Assoc. - Unicenter Log Analyzer
and Red Matrix Techmologies' SQL Audit 1.7 - but I have never used them. Any comments? Thanks for your help.
View 2 Replies
View Related
Feb 20, 2005
Hi everyone
Is there any way to recover all deleted records.
By mistakes i deleted all records.
Please help me out
Waiting for reply.
Thanx in advance
View 2 Replies
View Related
Dec 12, 2005
Hello All.
Is there a way to check who has deleted records from my SQL tables? I asked because I notice records keep disappearing from my tables recently for no reason. The DB is only accessible by a few IT staff. Business users have no direct access to it so they can't do any harm and there is no application that update these record missing tables.
I don't need to know the exact records that have been removed. I need info on who has made a deletion activity on which table, date and time is good enough.
Thank you.
Best regards
View 5 Replies
View Related
May 14, 2006
Hi,
Plz help me to restore deleted records.Pl zsuggest the ways to do it.
Thanks!!
View 2 Replies
View Related
May 7, 2004
Hi all,
I have a db with 15 tables and I want to keep records that have been deleted.
Now I don't know where to start:
Must I keep the same structure as the main db or can I also dump all the data in one table?
what are the advantages and disatvantages of the named possibilities.
If someone knows anything else please help me out.
Thnx in advance
View 4 Replies
View Related
Jul 20, 2005
I'm running a DB using MSDE (2000) that is interfaced by 2 differentades running on PCs with Access 2000 Runtime. One of the ADEs is apackage accounting system that is very solid and stable, the other isa custom application that I wrote (much less solid and stable). Thecustom app only deals with a select few tables in the database, andthe table in question is not one of those.With alarming regularity(daily), records are getting deleted out of aparticular table. I've set up a couple of dummy records in the tableand put a delete trigger on the table that creates record in a 'log'table that tells me the user and the time that the records aredeleted.The deletion (all records in the table) always occurs during businesshours (never over the weekend or at night) and the user responsiblevaries among 3 or 4 different users. 2 of those users don't even haverights to that table, so I'm really confused how those logins couldcause a delete on the table they don't have access to!??!As far as I can tell, this is only happening to this particular table( I hope!).Is there a way that I can get more information on the process ormachine or anything else that is behind the deletion?
View 2 Replies
View Related
Mar 4, 2006
I am trying to use SSIS to upsize Visual FoxPro data. The data is in free tables. I'm using the VFP OLE DB provider in a connection pointed at a folder. When I click on preview, I see the right data--deleted records are not included. However, when I run the package, the deleted records are sent to SQL Server 2005. I can pack all the tables prior to executing the package as a work around, but I would think I shouldn't have to. I don't see any way to detect which records are marked for deletion in the data flow. I've tried to specify in the connection string Deleted=Yes, but to no good effect. Also, as I mentioned, when I preview the data, the deleted records are not included, so the connection seems to work properly .
Steps to reproduce:
In VFP:
CREATE TABLE Test (Field1 N(1))
INSERT INTO Test (Field1) VALUES (1)
INSERT INTO Test (Field1) VALUES (2)
GO TOP IN Test
DELETE
In SQL Server:
CREATE TABLE Test (Field1 Numeric(1))
In Visual Studio SSIS:
Create connection object using VFPOleDB provider pointed to the folder with the Test.DBF.
Create a connection object to SQL Server 2005 pointed to the database with the test table.
Create a DataFlow task with the OLE DB Source and the SQL Server destination. When you preview the Source object you will see just one record, but when you execute the package, you see 2 records transferred and 2 records are actually in SQL Server. Seems like a bug.
View 6 Replies
View Related
May 14, 2001
We occasionally use TruncateTable to delete the contents of a number of tables on our SQL Server 7 database. We then build them anew from an outside source. I am finding that the size of the database (and corresponding backup file) has grown significantly. As I understand it, the space representing the deleted records is retained by the database. I have investigated using various DBCC commands, to no avail. Does anyone have a clue as to how I can recover this lost space? ... Thanking you in advance.
Mike Rosen - Amalgamated Bank
View 3 Replies
View Related
Feb 28, 2008
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.
Thanks
View 9 Replies
View Related
Feb 19, 2009
It happened accidently that we deleted records from a table in SQL server 2005 DB. We never took a full backup of the DB till then. Is there any way that we can recover the deleted records. Logs files are still present. ( *mdf and *ldf ).
View 3 Replies
View Related
Jan 11, 2006
installation of their softwares? If there has NEVER been a backup ofthe Database (.mdf), Recovery Model Set to Full, and there has neverbeen a backup of the Log (.ldf) files?Using the trial software for both, this was unclear.Thank you for your time,bd
View 2 Replies
View Related
Jul 20, 2005
I have 2 tables say table1 and table2 with the same structure. Each recordis identified by a field 'SerialNo'. Now there should be a total of 500000records in both tables with serialno from 1 to 500000. Either a record is intable1 or table2. I want to find records (or SerialNo's) that are inneither table (if deleted by accident etc). What would be the sql query?I'm using SQL 6.5thx
View 2 Replies
View Related
Jan 16, 2007
Hello,
Can any one tell me ..is there any restriction on the total number of records that can be added to a table ( SQL Server 2005) .Because .. i added 100 records to a table through my program..but i was able to c ..only the last 20 records which is being inserted..can any one tellme any way to increase the total number of records..
pls..help me..solution is required immediately.........
Regards,
Sweety
View 1 Replies
View Related
Jul 16, 2006
Hi all..
How to reflect deleted records in Sql Server table to the Dataset?
-- using a web service --
Thank you..
View 1 Replies
View Related
Jul 2, 2015
I want to delete some records from dbo.ABC table using where clause.
And would like to restore the deleted records back into the same table.
View 5 Replies
View Related
Nov 22, 2007
Hi all,
Can you help me to resolve my problem ? I have to do a simple daily backup system. Source : Flat File; Destination : SQL Server. I want to use the Slowly Changing Dimension component to backup only the new and updated row from my source (Flat File) and put them into SQL Server.
But how can I do to detect deleted rows from my source ?
Any suggestions ?
If it's not clear enough, please ask for more details !
GO
View 3 Replies
View Related
Apr 21, 2008
Hi,
Pardon if this is a dumb question; I'm not much of a database guy. Is there a way to reflect on a database? In particular, I would like a way to dynamically find out if a database has a table with a particular name or not.
Thanks!
View 4 Replies
View Related
Mar 25, 2013
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.
View 14 Replies
View Related
Nov 29, 2007
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
Thanks!
CB
View 1 Replies
View Related
Aug 10, 2007
I'm relatively new to compact framework and SQL Server Compact so bear with me if I've got an obvious thing I've forgotten.
I've written my own database helper layer. My idea is to generate SQL Insert statements dynamically based on what is in the contents of each object. Peformance however is horrible. I try to do 1800 inserts and it takes about 50 seconds on the device (Release Build, outside of the IDE).
I pass in a list of objects to be inserted which derive from a ModelBase class. ModelBase includes some ORM information (what table the object goes into. what fields are mapped to which properties). I generate one SQLCeCommand object, one sql string (new params for each insert), and am using SqlCeResultResultSets.
What can I do to make this run faster? Thank you
Code Snippet
public bool Insert(List<ModelBase> recs)
{
SqlCeConnection con = new SqlCeConnection(connectionString);
try
{
con.Open();
con.BeginTransaction();
}
catch
{
return false;
}
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = con;
String sqlString = "INSERT INTO [" + recs[0].tableName + "] (";
String sqlString2 = ") VALUES (";
PropertyInfo[] props = recs[0].GetType().GetProperties();
for (int x = 0; x < props.Length; x++)
{
PropertyInfo pi = props[x];
if (recs[0].fieldMap.ContainsKey(pi.Name))
{
sqlString += "[" + recs[0].fieldMap[pi.Name] + "]";
sqlString2 += "@" + pi.Name;
sqlString += ", ";
sqlString2 += ", ";
}
}
sqlString = sqlString.Substring(0, sqlString.LastIndexOf(", "));
sqlString2 = sqlString2.Substring(0, sqlString2.LastIndexOf(", "));
sqlString += sqlString2 + ")";
cmd.CommandText = sqlString;
cmd.CommandType = CommandType.Text;
foreach (ModelBase rec in recs)
{
cmd.Parameters.Clear();
for (int x = 0; x < props.Length; x++)
{
PropertyInfo pi = props[x];
if (rec.fieldMap.ContainsKey((pi.Name)))
{
if (pi.GetValue(rec, null) == null)
cmd.Parameters.AddWithValue("@" + pi.Name, DBNull.Value);
else
cmd.Parameters.AddWithValue("@" + pi.Name, pi.GetValue(rec, null));
}
}
try
{
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.None);
}
catch(Exception e)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
Program.log.Error(e.Message,e);
return false;
}
}
con.Close();
return true;
}
View 7 Replies
View Related
Jan 25, 2006
When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you
View 11 Replies
View Related
