SQL Server Performance Forum – Threads Archive
Appending problem …
I have records coming into a temp table. The records get parsed in this table from one field to about 9 fields. I have this command setup as a job already and it runs in < 30 seconds. The next two steps, i’m still doing in access cause i’m not able to accomplish it yet in SQL. Step 1 – Parse data from 1 field to 9 fields in temp table(records = 200k +/-) Step 2 – Append temp table to production – all 9 fields
Problem here is some of the records are duplicates, so i have to weed them out.
With access i can run an append query and it will only append those that don’t violate the primary key.
With SQL – it won’t run because of the violation. how to get past this?? Step 3 – Append temp table to history – all 9 fields
Same problem as Step 2 Thanks for any help!
Regards, John Hobby
SQL Beginner
Tennessee
You dont provide much detail and so I can only give vague answers… Its common when doing a bulk import to first load the data into a staging/temporary table. This allows you to filter out bad rows using efficient set based queries, instead of having to do row-byrow operations using some a cursor or some scripting language. You already do this by the sound of it. You have your data in a #temporary table. If the problem is that dupes exist in the temp table, its easy to filter them out using DISTINCT, or if the dupes are key based, you could use a subquery to only select rows where the key is not duped. If the problem is that dupes exist between the temp table and the destination table, you can write an insert statement that inserts data from the temp table, but also LEFT JOINs onto the destination table (using the primary key obviously), and only insert rows where the key on the right hand side (the destination table) IS NULL. This will suppress any inserts where the key already exists in the dest table. Give us a schema definition and more details and maybe I can help more
Hello John, for help parsing look into BOL for string functions like substring (if your record is of fixed size and fields would also be in the same positions) or combination of substring with patindex/charindex (if you need to identify row separator). Check out BCP options if you use it to bring records to the database, you may be able to parse the data as it is coming in avoiding a need for extra operations. as for "appending" you can use NOT EXISTS or NOT IN logic , i.e. insert into realtable (….)
select distinct ….. from temptable t
where not exists (select 1 from realtable where t.fields=fields)
simas
Sorry about being a little vague. But i think you answered my question, Chappy. You stated: "If the problem is that dupes exist between the temp table and the destination table, you can write an insert statement that inserts data from the temp table, but also LEFT JOINs onto the destination table (using the primary key obviously), and only insert rows where the key on the right hand side (the destination table) IS NULL. This will suppress any inserts where the key already exists in the dest table." This will more than likely work once I can get the code written. Here’s a little more detail: tempTable is the same as tblProduction (all field names, types, etc)
I upload a single field to the tempTable
sample field: c:wwwrootftprootfolder1p01012004,10102345,xyz123,94.jpg
I parse this field into multiple fields, pulling out the data elements i need to work with. Since these are filepaths i’m working with, they don’t always get ‘backed-up’ from the main directory so when i run my import command again, i’ll get the same ones over, plus any new ones. Then after parsing into the different fields – i append to production and history. I think the LEFT OUTER JOIN will work. I’ll have to play around with it some next week. Thanks for the help and replies!
Regards, John Hobby
SQL Beginner
Tennessee
Thanks for the help – now instead of a 40 minute Access append time, i’m < 5 minutes Stored Procedure append time! Final Code —code— CREATE PROCEDURE usp_InsertTempHistory
AS
INSERT INTO tHistory (x, y, z)
SELECT DISTINCT x, y, z
FROM tztest2 t
WHERE NOT EXISTS (SELECT 1 FROM tHistory WHERE t.X=X)
GO
—end code—
Regards, John Hobby
SQL Beginner
Tennessee
]]>