SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.
Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code FROM dbo.ADSCHL AD INNER JOIN TESTCEP FD ON AD.SCHOOL_NAME = FD.School_Name
I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.
I have a transaction table to store all the transaction happened on a single day. as per my requirement I wrote the query like this select Currency Code,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where TransactionCode in ('BNT' ,'BCN','BTC','STC','SCN','SNT') group by TransactionCode,CurrencyCode,TransactionAmount order by CurrencyCode..I got the result like this
Hi all, I am working with SQL Server2000 and I have done a horrible thing here. We here have an script that delete all data from all user tables of a database, and I run it in the master DATABASE. As we don't made backups of this database, now somethings of the database aren't working.
-- definindo o cursor... declare table_name_cursor cursor local fast_forward for select name from sysobjects where xtype = 'U' and name <> 'dtproperties'
-- desligando os vínculos... open table_name_cursor fetch next from table_name_cursor into @table_name select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' nocheck constraint all' exec(@alter_table_statement) while @@Fetch_Status = 0 begin fetch next from table_name_cursor into @table_name select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' nocheck constraint all' exec(@alter_table_statement) end close table_name_cursor
open table_name_cursor fetch next from table_name_cursor into @table_name select @delete_statement = 'delete from ' + ltrim(rtrim(@table_name)) exec(@delete_statement) while @@Fetch_Status = 0 begin fetch next from table_name_cursor into @table_name select @delete_statement = 'delete from ' + ltrim(rtrim(@table_name)) exec(@delete_statement) end close table_name_cursor
open table_name_cursor fetch next from table_name_cursor into @table_name select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' check constraint all' exec(@alter_table_statement) while @@Fetch_Status = 0 begin fetch next from table_name_cursor into @table_name select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' check constraint all' exec(@alter_table_statement) end close table_name_cursor
I have tried to restore master table with the restore function, but it doesn't work. When I try to do this I received a message informing that it can't copy the data because one file was in use. The server was in a single user mode.
Is there anyway to recover the data that I have lost?
I am new to MDM profisee tool and currently working for Addres verification project for my organization.I wanted to clear my doubts here about Unique Identifer in Stage table and how it works.. Here what i understand till now:
Step 1) I created an Entity using MDM profisee UI and it generated a stage table in MDS database called stg.Address_leaf Step 2) I have loaded data from external source to MDS stage table using ETL and passed Import type as 2 and Import status id as 0 Step 3) I have run store procudure system generated something stg.udp_Address_leaf to load the model and passed the version name as Version_1, Log flag as '1' and Batch tag as 'Address'
Now my below are my questions: 1) What is the field i can use in MDS stage table to populate my unique intifier value coming from source? (lets say Address_Id is my Unique value for all the records coming from source) 2) Where/how Unique Identifier is useful in this process? Will this be useful in next time load from stage to Model? 3) If i truncate and load my MDS stage table in next run and few earlier records has been updated how it will update those records in Model? will this process (code present in SP) recognize by Unique Identier column present in MDS stage table?
Now i have master Table for a device Utility. There is a attribute called "Device Type " in the table. Every Device Type has specific Device Attributes associate with it . Now attribute of Diffrent Device type are stored in Different Tables. Now when i select a particular value of Device Type ( lets say Type 1 or TYPE 2 ... ) then the table with has the attribute associated with particuter device type only has to be selected . So how can I do this ??? How to form a realtion between the tables,... ????
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.
I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.
SELECT * from RemoteDB.TESTDB.dbo.Objects
WHERE Last_Updated > '2008-05-21 10:51:00'
AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)
Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database. This would work just perfectly, no problems.
Now, the local database is actually on a PDA running SQL Server Compact Edition. There is currently no support for creating a linked environment. I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.
Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A: (name phone house cost of house,zipcodecountystatecountry) -a view will later split this large varchar string based column b: is the source filename of the data load (varchar 256) ....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
INSERT INTO @GroupRelation_Test ( CustomerNumber, AmountBilled,MinAmounttBilled) SELECT'12','15243','' UNION ALL SELECT'1231234','15243','' UNION ALL SELECT'463','15243','' UNION ALL SELECT'442','15243','' UNION ALL
We have staging table in which data is dumped from files . The staging table is truncated for every load . In order to retain data from staging table we are creating staging_purge table which hold the staging data. what is the fastest way to copy data from staging to purge table without impacting the load process.
I have 2 tables Staff and Class which are as follows
Staff ID (Primary Key) FirstName MiddleName Surname Etc. . .
Class ID (Primary key) LessonTemplateID TeacherID TermID AssistantID
LessonTemplateID and TermID link to other tables which i don't need right now.
The Class.TeacherID and Class.AssistantID both link to the Staff.ID
I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.
If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.
Im really stuck on this and just can't figure it out (is it even possible?).
I imported an Excel file in the form of a table(11,000 rows) in SQL Server 2012 database called Sanbox, I am trying to link this table to MS-CRM leads table so that I can link all the street addresses from table in Sandbox DB to the respective tables in MS-CRM leads table (200,000 rows), my end goal is to link the owner names from MS-CRM table to Sandbox DB's table with the matching criteria of 'street address'
My simple query looks like this, but it returns 200,000 rows, but I only want 11,000 rows -
SELECT * FROM Sandbox.dbo.Sheet1$ A LEFT JOIN [IPlus_MSCRM].[dbo].[FilteredLead] B ON A.[Street 1] = B.address1_line1
I have 2 tables: Source Table - IncidentDimNew and Destination Table - IncidentDimNew with identical columns: Id and CreatedDate but the data is different.
I would like to insert distinct data from those 2 columns of 1st table into the same 2 columns into the 2nd table but I onlt want to replace the values in the 2nd table where the Created Date is > 2015-04
I compiled the code in order to get the information I want from 2 tables:
Source Table SELECT COUNT(Id), LEFT(CONVERT(VARCHAR(10)), CreatedDate, 126), 7) FROM IncidentDimNew
This is the code I wrote in order to do it
INSERT INTO IncidentDim [Id] ,[CreatedDate] SELECT [Id] ,[CreatedDate]
where left(CONVERT(VARCHAR(10), CreatedDate, 126),7) > '2015-04'
But what it does it's gives the SUM of the values in the corresponding rows where the values are not null which I don't want.
So how do I leave the values as is from table IncidentDim and insert new values as it is from IncidentDimNew where the Created Date is > 2015-04?