There will only be a max of 3 Country entries for each Employee. So I want to select the EmployeeId and get the three CountryIds so it would look like this:
I am pretty new to SSIS, so please excuse me if this is a trivial question.
I have a denormalized database table in an Access database that I need to import into several different tables in a SQL 2005 database. You can think of the Access table as a CustomerOrders table. For example customer related information (i.e. CustomerName, CustomerID, etc...) is repeated with each record in the Access table. When this data gets moved to the SQL 2005 database, I need to insert one record for each distinct CustomerName/Customer ID record into a Customers table. I then need to insert and link every "Order" record into an "Orders" table.
I am sure that this is probably a pretty common task, but I have not found any examples or articles explaining this particular situation. What ways can this be done?
I was thinking I need to loop through each DISTINCT Customer record in the Access (source) table and insert a Customer record into the destination database's Customer table. I would then need to iterate through each row of the Access (source) table and "Lookup" the appropriate CustomerID/Key Field and insert an "Order" record.
The Access table contains over 75,000 rows of data. I am looking for the most appropriate way of doing this with SSIS (so that I don't have to write a custom application to do this!). Any help, input, links, articles, etc. is appreciated!!
I'm building a system that imports data from several source, Excel files, text files, Access databases, etc. using DTS. The entire process revolved around MS SQL Server, by the way.
I figured I would create denormalized tables that mirror the Excel and flat files, for example, in structure, import data to those, clean up and remove duplicates there, then break those out into my normalized table structure later.
Now I've finished the importing part (though this is going to happen once a week) and I'm onto breaking up the denormalized tables.
I'm hesitating because I'm not sure I've made the best decisions in terms of process, etc.
I've decided to use cursors to loop over the denormalized tables and use batch insert statements to push data out to the appropriate tables.
Any comments? Suggestions? All is welcome.
I'm specifically interested in hearing back on the way I've set up the intermediate, denormalized tables and how I'm breaking them up using cursors (step 2 of the process below). Still, all comments are welcome. As are suggestions for further reading.
Thanks again...
simplified example (my denormalized tables are 20 - 30 colums wide)
denormalized table: =================== name, address, city, state, cellphone, homephone
I'm breaking up the denormalized tables like this (*UNTESTED*): =================================================
DECLARE @vars.... (one for each column in my normalized table structure, matching size and type)
DECLARE myCursor CURSOR FAST_FORWARD FOR SELECT name, address, city, state, cellphone, homephone FROM _DNT_myWideTable INTO
WHILE @@Fetch_Status = 0 BEGIN -- grab the next row from the wide table FETCH NEXT FROM myCursor INTO @name, @address, @city, @state, @cellphone, @homephone
-- create the person first and get the ID with @@IDENTITY INSERT INTO tblPerson (name) VALUES (@name)
SET @personID = @@IDENTITY
-- use that ID to coordinate inserts across other tables INSERT INTO tblAddress (FK_person, address, city, state, addressType) VALUES(@person, @address, @city, @state, 'HOME')
INSERT INTO tblContact (FK_person, data, contactType) VALUES(@person, @cellphone, 'CELLPHONE')
INSERT INTO tblContact (FK_person, data, contactType) VALUES(@person, @homephone, 'HOMEPHONE')
Ok, I'm fairly new to .NET and even newer to the whole database concept. But, don't run away yet, I'm no idiot and I shouldn't have too hard of a time understanding your responses if you're kind of enough to give them. That being said, here's my dilemma:I'm trying to make a database of all the movies I own, the actors in them and the genre (s) they belong to. I have a set of tables that are in the 2NF (I think). I have a movies table, an actors table, a genres table, and two tables called movies_actors and movies_genres with primary-foreign key relationships to pull it all together (e.g. movie_id 1 has two entries in movies_genres, one for Action and one for Drama). My problem arises that when execute my monster query to pull ALL the data on one movie, I get a row returned for every combination of Genres and Actors in a movie. Example:movie_id movie_title comments actor_first actor_last genre_name1 Casino blah blah Robert DeNiro Action1 Casino blah blah Robert DeNiro Drama1 Casino blah blah Joe Pesci Action1 Casino blah blah Joe Pesci Drama And here's the query that produced that:1 SELECT movies.movie_title, movies.comments, actors.actor_first, 2 actors.actor_last, genres.genre_name 3 FROM movies INNER JOIN movies_actors ON movies.movie_id = movies_actors.movie_id 4 INNER JOIN actors ON movies_actors.actor_id = actors.actor_id 5 INNER JOIN movies_genres ON movies_genres.movie_id = movies.movie_id 6 INNER JOIN genres ON movies_genres.genre_id = genres.genre_id So, I want to put all the actors for one movie into the same cell in the datagrid (same with the genres) and still keep it sortable by actor or genre. Is this possible with the .NET 2.0 datagrid? Do I have some fundamental misunderstanding of how my tables should be structured? Am I just really far off and acting like a n00b?
I normalized the below tables but I am finding it difficult to copy data to the new tables. Â How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:
SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it. CREATE TABLE SKU_DATA ( SKU     Integer NOT NULL,
[code].....
The table structure above have two three determinants( SKU,SKU_Description and Buyer).  SKU and SKU_Description are candidate keys. Primary key is SKU.
Normalization : SKU_DATA(SKU,SKU_Description, Buyer) Â BUYER(Buyer,Department)
I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.
I have an allergy table which has a patientid and an allergy id. i would like to create a view(or SQL statement) that will give me a crosstab of a patient and there allergies(like below)
Dear Experts,When I use a single table I can easily use constraints to enforce mybusiness logic, but what do I do when I normalize a single table intomultiple tables.For example, imagine that my initial table has the columns ID, Name,Salary with the constraint that Salary is not NULL. Now imagine thatI break this into two tables, one with ID and Name and another with IDand Salary. I would like to have a constraint that prevents thecreation of a row with (ID,Name) in the first table unless acorresponding row in the second table is also created.I can enforce this logic with triggers, but it looks ugly and isfairly brittle. Is there a better way or is this the dark side ofnormalization?Thanks.
My ERP system (SAP Business One) does not have a "payment type" field, instead it has a field for the amount of each type of payment. It makes for annoying reporting.
If I have the following data in the Incoming Payments table (ORCT)
SELECTT0.DocEntry, CASE WHEN T0.CashSum <> 0 THEN 'Cash' WHEN T0.CreditSum <> 0 THEN 'Credit' WHEN T0.CheckSum <> 0 THEN 'Check' WHEN T0.TrsfrSum <> 0 THEN 'Transfer' END AS [Type], CASE WHEN T0.CashSum <> 0 THEN T0.CashSum WHEN T0.CreditSum <> 0 THEN T0.CreditSum WHEN T0.CheckSum <> 0 THEN T0.CheckSum WHEN T0.TrsfrSum <> 0 THEN T0.TrsfrSum END AS [Total] FROMORCT T0
I think I've just been staring at this too long and wanted some fresh eyes to think outside of the box on this one.
I am using this to report on our customer's monthly statements so that I can display "12/13/06 Wire Transfer $100". There are many more complications to this query such as currencies and which document number or reference to display, but I have figured out most of those on my own.
I am looking to store the different forms and data in our database. We have several different forms and contains different information. I am looking for different approaches to model this table structure.
Also, I need to make sure the table structure will allow for new forms.
Hello, My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type. The dropdown options on the form will be as follows: ComplimentsComplaintsGeneral CommentsSuggestions Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item. For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter. However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated. If you need more information, I will provide whatever is needed. As always, thanks for everyone's assistance. Chris
I have just inherited a new project consisting of data imported into sql 2005 from a multi-dimensional database. After finding the correct ODBC and importing the data I believed that I was done, but after reviewing the resulting structure I discovered why this was called a €œmulti-dimensional€? database. The resulting imported data is completely de-normalized and resembles an excel spreadsheet more than a relational database. An example of this structure is the persons table. The table has multiple columns, some of which contain the multi-dimensional fields. These fields contain multiple values which are separated with a tilde, €œ~€?. I need to parse out the data and normalize it. In the specific sample of data below I attempting to take the personid, associates, and assocattrib and insert them into a sql table, associates. This table references the persons table where the personid and the associates references the personid in the persons table.
CREATE TABLE [dbo].[associates]( [associd] [int] NOT NULL REFERENCES persons(personid), [namepkey] [int] NOT NULL REFERENCES persons(personid), [assocattribute] [varchar](20) NULL )
The purpose of normalizing this data will be to show the realationship(s) between people as it has been documented in the previous data structure, i.e. person 1 is an associate of person 336 and the attribute is WIT.
My problem lies in attempting to parse out the associates and assocattrib columns and relate them to the appropriate personid. The personid relates to each associate and assocattrib and the tilde, ~, separates the values ordinal position which, in sql, would be separate rows. For example the single row: personid associates assocattrib 58201 252427~252427~252427 VICT/SUSP~WIT~RP Should be: 58201 252427 VICT/SUSP 58201 252427 WIT 58201 252427 RP
The imported data can have no associates: personid associates assocattrib 152683 NULL NULL
or up to 69 associates, I am not even going to try to paste all of that here.
There are over 400,000 rows that I am looking at parsing, so any way of doing this in t-sql or the clr would be GREAT. This data is stored in SQL 2005 standard SP2. The specific machine is our test/reporting server, so I am not necessarily concerned with the best performing solution, I am leaning more towards providing some free time for me.
Any help or suggestions, including better ideas on the table structure, would be greatly appreciated.
First of all I think of myself as a software developer and by no means a database expert. So I am posting a question here hoping that someone will lead me in the right direction.
I am somewhat following the database diagram from AdventureWorks with my own "normalized" database and have a question on how to update the table(s).
Here is my situation: Table: Client ClientID (PK) Name ...
Table: Address AddressID (PK) AddressLine1 AddressLine2 City ...
The tables should be referenced properly, etc... So my question is, when I add a new Client record, how and when do I update the Client table, Address table and ClientAddress table? If my PK on the Client and Address tables is an autoincriment, do I need it before I can update the ClientAddress table? If so, what do I have to look for, or how do I get the newly created ID to update the ClientAddress?
Is this making any sense? - I'm a little lost/confused. So if anyone can provide me links to where I can learn more about stuff like this and/or post some ideas/solutions, I would greatly appreciate it. Also, if I am not clear on my issue, please ask and I will see if I can clarify it more.
We've had a new server set up with SQL 2012 and I'm in the process of moving data to it from a 2008 (SP2) server.
Details are as follows:- 2012 instance:- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
2008 instance:- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)
I don't want to do a backup/restore routine as there are collation conflicts on the 2008 server.I've created the database and tables on the 2012 instance and now I want to transfer the data from the 2008 instance to the 2012 one.
The 2012 instance has a linked server to the 2008 instance.I was trying to use sp_MSForEachTable (I know, it's old and will probably disappear shortly) but that doesn't seem to work properly because some of the columns have an Identity field set up.
Some of the tables have upwards of 10 million records in them and are quite sizeable.how I can achieve the transfer without a back-up/restore?
1. Take a subset of data from about 100 tables that have multiple references to other tables in this group of 100 from a first DB. 2. Insert the above data into a second DB, a database that already has data in the 100 tables, while maintaining the correct references.
As a general approach, the best way I can think of doing this is as follows:
1. Create mapping tables for every ID that is referenced in a different table (OldID NewID) 2. Insert the old data into the new table and output the OldID and NewID into the mapping table. 3. Use that mapping data to make sure all tables that use those IDs have the new IDs in DB2.
This approach is extremely labor intensive both on initial implementation and would require a fairly substantial amount of work to maintain going forward.
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT -- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run) (SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
[Code] ...
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :
I need to make a query that counts installed developer software for all our developers (from the sccm database), for licensing purposes. The trick here is that a license should only be counted once per. developer and that should be the highest version. But in the database, the developers can have different versions of the software installed (upgrades) on the same computer and they often use several computers with different software versions.
So for example: A source table with two developers
------------------------------------------------------------------- | dev1 | comp1 | Microsoft Visual Studio Ultimate 2013 | dev1 | comp1 | Microsoft Visual Studio Professional 2010 | dev1 | comp2 | Microsoft Visual Studio Premium 2010 | dev2 | comp3 | Microsoft Visual Studio Professional 2010 | dev2 | comp4 | Microsoft Visual Studio Premium 2012 --------------------------------------------------------------------
I want the result to be: ----------------------------------------------------- | dev1 | Microsoft Visual Studio Ultimate 2013 | dev2 | Microsoft Visual Studio Premium 2012 ------------------------------------------------------
I have created a query using cursors that give me the correct result, but it's way to slow to be acceptable (over 20 min..). I also toyed with the idea of creating some sort of CRL proc or function in C# that does the logic, but a SCCM consultant from MS said that if I create any kind of custom objects on the SCCM SQL Server instance, we loose all support from them. So I'm basically stuck with using good old fashioned T-SQL queries.
My idea now, is to use a CTE table and combine it with a Temp table with the software and a rank. I feel that I'm on the right track, but I just can't nail it properly.
This is how far I have come now:
IF OBJECT_ID('tempdb..#swRank') IS NULL CREATE TABLE #swRank(rankID int NOT NULL UNIQUE, vsVersion nvarchar(255)) INSERT INTO #swRank(rankID, vsVersion) VALUES (1, 'Microsoft Visual Studio Ultimate 2013'), (2, 'Microsoft Visual Studio Ultimate 2012'),
I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -
SELECT * FROM ( select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type ,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo ,n.note FROM ih_cml_log_entry cml
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
Hi, My problem is I only can put the database table to another database.I use example to ********. For example. My database name is Career. The user has a database name called Online. The Online has already some tables in the database they created before. I just need load my database career tables to the Online database. My database career has seven tables. These tables has a reltionship for each other. I just need to load these table to the Online database, But don't build relation to the tables that already exist in the Online database. Besides, I want to know how load the stored procedure from my database to another database.
In a Library Management database we have these tables
1) Document ( DocNo , Doc_type , permalink,inDate) 2)Title(id, DocNo,Main_Title, Other_Title) 3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....) 4)Publisher(id,DocNo , Name,Publisedate,address) 5)Subject(id,DocNo,Subject) 6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc
In document table I have 500,000 records.
I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?
I have 6 tables which are very huge in row count and records needs to deleted which are older than 8 days.
Little info: Every day, 300 Million records are inserted in below 7 tables. we should maintain only 8 days worth of data in below tables. How to implement Purge script which can delete records in all tables in the same time and with optimized parallelism.
Master table which has [ID],[Timestamp] Table Name: Sample - 2,578,106
Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table. So the records needs to deleted based on Min(ID) from Sample
I need to consume a live data feed from a golf tournament. And by consume, I really mean insert (merge) into our own SQL Server database on a regular intervals as a tournament progresses.This site didn't let me upload an XML file, but you can see a sample of the data feed here: URL....
I need to insert this data into 2 tables, Player_Holes and Player_Shots. But while doing the insert, I need to lookup several things such as our player ID match to theirs on an external_id against the players table. The shot types translation, and some other logic about the process overall.
The columns in my player_holes tables are: id, player_id, hole_id, round, shots (this is a total # of strokes) and date_created/date_modified.Shots table is similar: id, player_id, hole_id, round, shot_number, shot_type_id, club, distance, date_created/date_modified.
The only way I know how to do it, is inefficient. I would parse the XML in ColdFusion (please no comments on ColdFusion, that's what we use for webdev), and then loop over it and do inserts for each player, each hole for each round, and the shots would probably be separate for each hole.
It would be so much better and more efficient if I could do it in SQL directly. I've done some research and SQL Server Data Tools looks promising. I've never used it, so would have to learn, but also I'm not sure if that'd work in this application when we want to run is as a scheduled task every few minutes.
If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101) then output should come as 'Mobile/c' (this is for f.parent_id=101)
If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id
Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache
I have a requirement of creating nested tables in SQL server. how to create them. Just to give a background I am trying to move the RDBMS from oracle to SQL server.
Structure of tables is as follows. I have table 'Employees' with address as one of the column. I have one more table with columns Street, Town, Dist, State. When I query the table 'Employees' I should see the attribute name and values of all the columns in address table in address column.
Employees: with columns: ID, FirstName, LastName, dept, gender, dob, address
Address (Nested table): with columns : Street, Town, Dist, State
This was done in oracle using Nested tables and user defined data types. what is alternative for this in SQL server. How can I achive this requirement in SQL server.
I'm pulling data from XML into tables, but I'm unsure how to link the data after it's imported. This example has names and tasks, and I can pull the data into two tables, but I can't find any way to link the task to the appropriate person. My person and task tables populate without issue, but there's nothing I can find to link the rows together. So in this example Test 1 would go to the first two Tasks and Test 2 would go to the second two work items.
