Stored Proc To Copy Unnormalized To Normalized Table
Jul 20, 2005
I have a "source" table that is being populated by a DTS bulk import
of a text file. I need to scrub the source table after the import
step by running appropriate stored proc(s) to copy the source data to
2 normalized tables. The problem is that table "Companies" needs to
be populated first in order to generate the Identity ID and then use
that as the foreign key in the other table.
Hi I am new to SSIS. Probaly What I am asking is the simple one. But I dont know. I have a requirement. My Data source is Excel file and Destination is SQL Server . Excel file consist around 10k unnormalized rows which should be loaded to the multiple normalized master and child tables into the destination with the Primary key (identity column) and foreign key constrains. Even sometimes package should check for destination database table for matching Source column (Excel) code with the lookup table code (destination lookup table) and get the Primary key of Lookup table and put it into the destination table. Please help me the way to find the solution for this
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.
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)
I'm working on a couple projects and I've recently been trying to make everything fully normalized so updates are easier and I'm just wondering if there's a standard way to query and update normalized tables. For example: If I have table People with columns ID, FirstName, LastName, Height, Weight, ShoeSize, I can normalize that into two tables. Table People has ID, FirstName and LastName. Table PeopleDetails has PeopleID (FK), Property and Value. That way i can add more properties later right at the presentation layer if I like. Essentially I moved the data from being horizontal to being vertical. But doing a simple search for people means I have to search the details table and return a LOT more records (one each for Height, Weight and ShoeSize) not to mention any more details I might add later. With a lot of details, it seems like your performance would take a big hit and your code would get really complicated as your looping through a vertical dataset to find the properties you want. Or is there some other standard way of doing that? I'm just hoping that someone else has solved these problems and there's a standard set of functions out there for selecting and updating this kind of DB structure. Anyone?
I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.
Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.
Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?
I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
Need some suggestions on what to check or look for.My stored proc is not finding the table in the sql db. This is the error...Invalid object name 'tblServRec'I use the same function to pass in an arraylist and the sp name for another sp and that one works ok.The sp is the same as another one except for the table name and that one works ok.The sp works fine on my local machine. It finds the table ok. But when I try it on the server, it doesn't work.I have checked permissions, and they are the same for all tables, even the one that the other sp works ok on the server.Here is the sp if that will help.....CREATE PROCEDURE dbo.deleteServRec @fldNum INT ASBEGIN DECLARE @errCode INT DELETE FROM tblServRec WHERE fldNum = @fldNum SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeENDGOThanks all,Zath
I take the information below in query analyzer and everything runs fine-returns 48 rows. I try to run it as a stored proc and I get no records. If I put a simple select statement into the stored proc, it runs, so it's not permissions. Can anyone help me with why this won't execute as a stored procedure? Articles seem to indicate you can do this with temp tables in a stored procedure. Thanks
declare @style as int, @disc as int, @key as varChar(500), @sdate as varChar(15), @edate as varChar(15), @ld as varChar(15)
set @style=0 set @disc=0 set @sdate='3/1/2006' set @ld='2'
insert into #ListAll SELECT top 100 percent wid, parentID, 0 as myFlag FROM myTable WHERE (@style=0 or styleID=@style) and (@edate is null or start_date < @edate) and ((start_date is null) or (datediff(day,start_date,@sdate) <1)) and (@ld='9' or charIndex(convert(varchar(1),datepart(dw,start_dat e)),@ld)>0) and wid in (select wid from myTable2 where (@disc=0 or discID=@disc)) and wid in (select wid from myTable where @key is null or ([title] like '%' + @key + '%' or [keywords] like '%' + @key + '%'))
update #ListAll set myFlag=1 where parentID<>0 insert into #ListAll select w.wid, w.parentID, 0 as myFlag from myTable w right join #ListAll on #ListAll.parentID=w.wid where #ListAll.parentID<>0 delete #ListAll where myFlag=1
SELECT top 100 percent srt, w.WID, w.parentID, w.[title], w.start_date, w.end_date, w.cancelled, w.url, styleID, w.[keywords], w.onlineID, w.httplocation, datepart(dw,w.start_date) as lddate FROM myTable w right join #ListAll on #ListAll.wid=w.wid ORDER BY srt, start_date, [title]
Recently someone told me that I could use a Parameter in a Stored Proc as a text placeholder in the SQL Statement. I needed to update a table by looping thru a set of source tables. I thought NOW IS MY TIME to try using a parameter as a table name. Check the following Stored Proc
CREATE PROCEDURE [dbo].[sp_Update] @DistributorID int, @TableName varchar(50) AS UPDATE C SET C.UnitCost = T.[Price] FROM (tbl_Catalog C INNER JOIN @TableName T ON C.Code = T.Code) GO
NEEDLESS TO SAY this didn't work. In reviewing my references this seems to be a no no.
Is it possible to use a parameter as a table name? OR is there another way to do this?
We have modified a column name for a table. Now we need to find out all the database objects (stored procedures, functions, views) which access this table so that we can modify them. This is a very big database and its not easy to open the code for each object and check if its access that table. To add to the complexity, its not just one table and column but a number of tables have been modified.
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off, for a fewweeks in advance. Anysuggestions?
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off. Anysuggestions?
Hello,Does anyone know of a way to loop thru a SQL table using code in a storedprocedure?I need to go thru each record in a small table and build a string usingvalues from the fields associated with a part number, and I can't find anyway to process each record individually. The string needs to be initializedwith the data associated with the 1st record's part number, and I need tobuild the string until a new part number is incurred. Once a new part numberis found in the table, the string is written to a different table and resetfor this next part number in the table. Need to repeat until all records inthe table have been processed.I use ADO in access 2000 to work thru local recordsets, I just can't findanyway to do this in a stored SQL procedure.Thanks for any suggestions, Eric.
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')
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 trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:
Create Procedure VerifySubjectNum (@forum_ID VARCHAR(10), @subject_num INT) As If Exists (SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num) Return 1 Else Return 0
when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?
Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?
I know there maybe something similar of what im asking for but i just cant find it.
I have 3 Stored procedure.
SPA - create a temporary table "sp_getListOfChildren" SPB - insert the data into the temp table "sp_InsertCategoriesFound" SPC - display the list of categories i found "sp_ListingAvailableCategories"
process: SPA call SPB and SPC call SPA
my problem is in the SPC. it seems that the table doesnt exist anymore when i do a select but in the message tab of my sql analyser i can see that the table have some data before executing that store proc..
Invalid object name '#TblTempCat'. for my SPC !! ??? why.. how do i detect a temp table in diffirent stored procedure per user and as to be temp table.. for multiple access.. "WEB"
============MY "SPC" CODE============= alter PROCEDURE sp_ListingAvailableCategories @CurrentCategoryID AS uniqueidentifier AS
exec sp_getListOfChildren @CurrentCategoryID
select * from #TblTempCat
select * from TblCategories where CatID not in (select CatID from #TblTempCat) and CatId <> @CurrentCategoryID
Is there a way to delete records from table passing parameter as tablename? I won't to delete all records from a table dependent on table selected. i'm trying to do this with stored procedure...Table to delete depends on the checkbox selected.
Current code(works) Public Function DelAll() MZKDB = MZKHRFin If sloption = "L" Then sqlConn.ConnectionString = "Server=" & MZKSrv & ";Initial Catalog=" & MZKDB & ";Integrated Security=SSPI;" ElseIf sloption = "S" Then sqlConn.ConnectionString = "Server=" & MZKSrv & ";User id=sa;Password=" & MZKPswd & "; Initial Catalog=" & MZKDB & ";" End If sqlConn.Open() sqlTrans = sqlConn.BeginTransaction() sqlCmd.Connection = sqlConn sqlCmd.Transaction = sqlTrans Try sqlCmd.CommandText = sqlStr sqlCmd.ExecuteNonQuery() sqlTrans.Commit() frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Prior records have been deleted from the database." & vbCrLf SetCursor() Catch e As Exception Try sqlTrans.Rollback() Catch ex As SqlException If Not sqlTrans.Connection Is Nothing Then frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " An exception of type " & ex.GetType().ToString() & " was encountered while attempting to roll back the transaction." & vbCrLf SetCursor() End If End Try frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Records were NOT deleted from the database." & vbCrLf SetCursor() Finally sqlConn.Close() End Try ResetID() End Function If cbGenFY.Checked Then sqlStr = "DELETE FROM FIN_FiscalYear" TableName = "dbo.FIN_FiscalYear" DelAll() ClearCounts() timeStepStart = Date.Now GenFY() timeStepStop = Date.Now
DispOneCounts() End If If cbGenFund.Checked Then sqlStr = "DELETE FROM FIN_Fund" TableName = "dbo.FIN_Fund" DelAll() ClearCounts() timeStepStart = Date.Now GenFund() timeStepStop = Date.Now DispOneCounts() End If If cbGenFunc.Checked Then sqlStr = "DELETE FROM FIN_Function" TableName = "dbo.FIN_Function" DelAll() ClearCounts() timeStepStart = Date.Now GenFunc() timeStepStop = Date.Now DispOneCounts()
End If If cbGenObject.Checked Then sqlStr = "DELETE FROM FIN_Object" TableName = "dbo.FIN_Object" DelAll() ClearCounts() timeStepStart = Date.Now GenObject() timeStepStop = Date.Now DispOneCounts() End If If cbGenCenter.Checked Then sqlStr = "DELETE FROM FIN_Center" TableName = "dbo.FIN_Center" DelAll() ClearCounts() timeStepStart = Date.Now GenCenter() timeStepStop = Date.Now DispOneCounts() End If If cbGenProject.Checked Then sqlStr = "DELETE FROM FIN_CodeBook" TableName = "dbo.FIN_CodeBook" DelAll() sqlStr = "DELETE FROM FIN_BudgetAccnt" TableName = "dbo.FIN_BudgetAccnt" DelAll() sqlStr = "DELETE FROM FIN_Budget" TableName = "dbo.FIN_Budget" DelAll() sqlStr = "DELETE FROM FIN_Project" TableName = "dbo.FIN_Project" DelAll() ClearCounts() timeStepStart = Date.Now GenProject() timeStepStop = Date.Now TableName = "dbo.FIN_Project" DispOneCounts() End If If cbGenProgram.Checked Then sqlStr = "DELETE FROM FIN_Program" TableName = "dbo.FIN_Program" DelAll() ClearCounts() timeStepStart = Date.Now GenProgram() timeStepStop = Date.Now DispOneCounts() End If If cbGenGL.Checked Then sqlStr = "DELETE FROM FIN_gl" TableName = "FIN_gl" DelAll() ClearCounts() timeStepStart = Date.Now GenGL() timeStepStop = Date.Now DispOneCounts() End If If cbGenRevenue.Checked Then sqlStr = "DELETE FROM FIN_Revenue" TableName = "FIN_Revenue" DelAll() ClearCounts() timeStepStart = Date.Now GenRevenue() timeStepStop = Date.Now DispOneCounts() End If If cbGenBank.Checked Then sqlStr = "DELETE FROM FIN_VendorBankAccnt" TableName = "dbo.FIN_VendorBankAccnt" DelAll() sqlStr = "DELETE FROM FIN_VendorBank" TableName = "dbo.FIN_VendorBank" DelAll() sqlStr = "DELETE FROM FIN_bankAdd" TableName = "dbo.FIN_bankAdd" DelAll() sqlStr = "DELETE FROM FIN_bankTERMS" TableName = "dbo.FIN_bankTerms" DelAll() sqlStr = "DELETE FROM FIN_bank" TableName = "dbo.FIN_bank" DelAll() ClearCounts() timeStepStart = Date.Now GenBank() timeStepStop = Date.Now TableName2 = "dbo.FIN_bankTERMS" TableName3 = "dbo.FIN_BankAdd" TableName4 = "dbo.FIN_VendorBank" TableName5 = "dbo.FIN_VendorBankAccnt" DispTwoCounts() End If If cbFinAP.Checked Then sqlStr = "DELETE FROM FIN_Period" TableName = "FIN_Period" DelAll() ClearCounts() timeStepStart = Date.Now GenPeriod() timeStepStop = Date.Now DispOneCounts() End If
Hi I wanted to use the table variable in Stored proc , for that i have create the table variable in the main SP which will be used by again called sp(child SPs) now when i am trying to use the same table variable in the child SP, at the time of compliation it is showing error
Msg 1087, Level 15, State 2, Procedure fwd_price_cons, Line 149 Must declare the table variable "@tmp_get_imu_retn".
Can any body give me the idea how to complile the child SP with the same table variable used in the main SP.
Can anyone helpCREATE PROCEDURE PagedResults_New (@startRowIndex int, @maximumRows int ) AS
--Create a table variable DECLARE @TempItems TABLE (ID int IDENTITY, ShortListId int ) -- Insert the rows from tblItems into the temp. table INSERT INTO @TempItems (ShortListId) SELECT Id FROM shortlist SWHERE Publish = 'True' order by date DESC
-- Now, return the set of paged records SELECT S.*, C.CategoryTitleFROM @TempItems t INNER JOIN shortList S ON t.ShortListId = S.Id WHERE ID BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1 GO
I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."
It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?
Hi,I am using data from multiple databases and/or queries. It would greatlysimplify and speed things up if I could use CONTAINS in processing theresults. However, "CONTAINS" requires the data to be indexed. Due to theamount of processing, I think it would be faster even if I had to re-indexevery time.For example, I would like to do something like this (simplified toillustrate the desired functionality... This should show all of the wordsfrom one table that are not contained in their current or inflectional formswithin another table):SELECT W1.ContentFROM(SELECT Word AS ContentFROM MyTable) W1LEFT OUTER JOIN(SELECT Phrase AS ContentFROM MyOtherTable) W2ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)WHERE W2.Content IS NULLCan the results of a procedure be indexed? If not, can I drop the resultsinto a new table and trigger an automatic index of it, pausing the procedureuntil the indexing is done?Or, it there another way?Thanks!
I need to write a storedproc that receives the name of a table (as a string) and inside the stored proc uses select count(*) from <tablename>. The problem is the passed in tablename is a string so it can't be used in the select statement. Any ideas how I can do what I want?
Hy , How is the stored procedure, to copy from a table to others tables? I have a stored procedure which is doing that, but for 1 registration : " insert into....". So i want for each registration in the source table to execute this procedure and put the data in my format table.
