Combining Data for ASCI layout

Question

Combining Data for ASCI layout

porcrim

SSC Eights!

Points: 948
More actions
November 13, 2002 at 2:42 pm

#80269

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

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

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 2

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

porcrim SSC Eights! Points: 948 More actions · Answer 3

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?

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 4

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

dj_meier SSCrazy Points: 2732 More actions · Answer 5

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

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 6

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.