[T-SQL] Inserting missing records using multiple fields as the key |
| Inserting missing records using multiple fields as the key Posted: 07 Apr 2013 10:00 PM PDT Hi,I have a complex problem that I'm pretty sure should have a very simple solution. If I could just find it.I am trying to import data from a flat file aka Raw_Build_List:Contract Description varchar(30)Section Description varchar(25)Item Description varchar(30)Quantity integerI am trying to write a script that will pull it into a relational database to use with other data. The script will have to run every night. The Raw data may contain records processed before.I have 4 tables:Contracts (ID, Contract_Description, etc), Sections (ID, Section, etc), Items (ID, Description, Category, etc) and Build_list (ContractID, SectionID, ItemID, Qty, StartDate, etc)To import the data I do multiple steps.1. Add 3 extra fields to Raw_Build_List: ContractID, SectionID and ItemID.2. I run a conditional insert into each table (Contracts, Sections and Items) using a group by eg.[code="sql"]INSERT INTO Contracts (Contract_Description) SELECT ContractDesctiption GROUP BY ContractDesctiption WHERE ContractDescription NOT IN (SELECT Contract_Description FROM Contracts)[/code]3. Then using the IDs generated by the Identity Fields I update the new fields in the Raw_Build_List table. eg.[code="sql"]UPDATE Raw_Build_List SET ContractID = ID FROM Contracts LEFT OUTER JOIN Raw_Build_List ON Contracts.Contract_Description = Raw_Build_List.ContractDesctiption[/code]4. Now in the Raw_Build_List I have all the key fields against all the correct descriptions and I want to put all those keys into the Build_list table and not create duplicates.[b]Is there a non-[u]RBAR[/u] way of doing this?[/b]The one way I thought may work is to string all the IDs together and compare them to the string of all the IDs in the other table and it does work but as expected is really slow. [code="sql"]RIGHT('00000000'+LTRIM(RTRIM(ContractID))+RIGHT('00000000'+LTRIM(RTRIM(SectionID))+RIGHT('00000000'+LTRIM(RTRIM(ItemID))[/code]NOTE: I had to pad it as Contract=1, Section=24 and Item=45 (joined = 12445) will match up with Contract=12, Section=44 and Item 5 (joined = 12445).[i]There has to be a better way of doing this...:blink:[/i] |
| T-SQL Variable vs String Equivalent Posted: 08 Apr 2013 12:47 AM PDT Got a question with some coding standards. There are developers declaring a variable to a constant value and using that in multiple places in a stored proc rather that using the string equivalent. For example:[code="sql"]DECLARE @Yes VARCHAR(3) = 'Yes'SELECT [pk_value] ,[field_value] FROM [tempdb].[dbo].[table7] WHERE [field_value] = @Yes; [/code]rather than:[code="sql"]SELECT [pk_value] ,[field_value] FROM [tempdb].[dbo].[table7] WHERE [field_value] = 'Yes';[/code] I setup a quick and dirty example and ended up with the example below, the same execution plan was generated, but query using the variable was slower, using more CPU.[img]http://s20.postimg.org/o4by92ex9/Untitled.png[/img]Is there any scenario out there where a variable would be faster than the string equivalent?Thanks,Kevin |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
