November 13, 2002 at 2:42 pm
I have two SQL tables that are related by an id field but otherwise have different structures. I need to combine these two tables so that when output to a text file table 1 data precedes table 2. That is, they are grouped by the id. Since the table structures are different, UNION is not an option. How can I combine the data to satisfy the ASCI import? I thought maybe using cursors and the fetch method might be the answer but I am looking for some ideas.
November 13, 2002 at 2:55 pm
How about inserting the records from table 1 and table 2 into a third table "Table 3". Then you could run a select statement from table 3 to output your records by the id. Of course you might have to put a another column in table 3 that indicated which table the original record came from so that table 1 records would come first, then table 2, by id. Also i suppose some data manipulation might also have to occur to get the data into table 3, since table 1 an table 2 have a different structure.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 13, 2002 at 3:00 pm
Why is UNION out of the question.
You can insert the data into a table and then select it out.
How are you plannig to get the ascii output
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 13, 2002 at 3:14 pm
Originally the specs that I had for importing this data into a third-party app called for separate patient(table 1) and response (table 2) text files. This was easy since these tables have different layouts and column sizes. Using DTS I just created these two files. Now the spec calls for patient records to precede their related response records - all in one file. Doesn't UNION require these two tables to be alike?
November 13, 2002 at 3:40 pm
So you want to be able to have 2 different types of record in one file.
i.e
Patientcol1,Patientcol2,Patientcol3
responsecol1,responsecol2,responsecol3,responsecol4,responsecol5
If you have the same number of columns that will be fine. convert all the columns to a common data type (varchar).
If you create a table with the right number of columns and then do the 1 inserts and then return the data ordered by ID you should have a solution. You will have to look out for your conversions to varchar (i.e dates, decimals)
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 13, 2002 at 4:35 pm
One trick you may be able to use if the flat file is fixed width or comma/tab delimited is to create 1 large column in a work table, named "text1" or something like that, then insert the pre-formatted values into that table from each table... like this: Insert worktbl (id,type,text1) select id,1,text1=convert(char(10),[id])+' ' + convert(char(20),SomeOtherField)....
Where the ID is the unique identifier, and the type is Patient OR Response (P/R, 1/2..whatever). Then in the DTS out, use a query: "select text1 from worktbl order by id,type"
You could also format the generic column with TABs or COMMAs if needed.
-Dan
-Dan
November 13, 2002 at 5:01 pm
I think Gregs answer would be the most efficient. Pad the insert queries to build a common structure. This could even accomodate the ASCII structure you need. Use a Key - Subkey Structure (Keys from Patient, SubKeys From Response) and it boils down to one insert from each table and a sort when retrieving the recordset.
And Simon's answer is a good one as well, again pad the query's with bogus values for fields to generate a common structure, and your key, subkey structure may even be the natural relationship from your table structure. This one's more dynamic, but puts more load on SQL server I believe. But then again, perhaps not.
Either way is preferable to a cursor, even though, that would work as well. Keep in mind that when you use a cursor, your generating thousands of transactions in most cases, as compared to the 7 or so needed for a set based operation.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply