.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

How to output data from master detail tables into text file but on different lines.

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server


I have a requirement to output data from two tables, a master table and a detail table, to a text file such that the output looks like the following:

master record 1

detail record  1 1

detail record   1 2

master record 2

detail record  2 1

master record 3

detail record 3 1

detail record 3 2

detail record 3 3



Is this possible in one query?  I am sufficiently familiar with the bcp utility to do the output from any of the tables separately but have some difficulty with doing it for the two tables and have the result structured as outlined above.

View Complete Post

More Related Resource Links

Data loading from text file

Hi, I am loading the data from a flat file into a sql table. The values in one of the column have spaces at the end of the value. I want to remove those spaces and load into the table. I am thinking of using derived column and use rtrim on that column, how can I use that as an expression. Thanks.sqldev

Insert Master/Detail data using ADO.net Transactions

I am working on Sales application. In database i have SalesOrderHeader & SalesOrderDetail tables. They are almost typical Adventureworks db tables.How to insert data in both tables using TransactionScope so if any of the record from the two tables generate error complete transaction roll-back.One thing more I am using TableAdapters, My initial concept was inserting record in SalesOrderHeader, get SalesOrderId using Scope_Identity & bulkInsert Records to SalesOrderDetail using TransactionScope.Please suggest.

Accessing master formview data from a nested detail formview?

Hi all, I would like to ask if it is possible to access data on a master FormView from a nested detail FormView. I have a main form (master) with several tabs (detail) and would like to display a label with text from main form that is hidden by the tab at the moment of editting. My asp page looks like this.<asp:FormView ID="fvwCustomer" runat="server" DataSourceID="dsCustomer" DefaultMode="Edit" DataKeyNames="CustomerID"> <EditItemTemplate> <asp:TextBox ID="CompanyTextBox" runat="server" Text='<%# Bind("Company") %>' /> <asp:FormView ID="fvwContactPerson" runat="server" DataSourceID="dsContactPerson" DefaultMode="Edit" DataKeyNames="ContactPersonID"> <EditItemTemplate> <%-- I need to get the value Company from dsCustomer --%> <asp:Label ID="CompanyTextLabel" runat="server" Text='<%# Eval("Company") %>' /> </EditItemTemplate> </asp:FormView> </EditItemTemplate> </asp:FormView>   Is it possible to get the value of the label CompanyTextLabel from CompanyTextBox using just ASP.NET expressions or something similar without writing c# code in

Output Blank Text File

Hello all,I have come across an interesting edge case when using Response.Write. I couldn't find another forum to post this in, so I'm putting it here, even though its not really "getting started" in my mind.The scenario goes like this:I have a ASPX page that stores data in a string using a comma delimited text format, which is then written to the response stream using Response.Write. Whenever there is data, this works fine. But, when there is no data, the string generated is empty and a basic HTML page structure magically appears in the output even though it isn't even in the .ASPX file. What I'd like to do is simply return a blank text file (not even a space).I've tried using Response.Clear to ensure that there is no content in the response stream. I've tried setting the content type and even specifying a filename in the response header. I am, of course, also using Response.End after the call to Response.Write, but all to no avail. The response is always a basic HTML structure.To eliminate other possibilities, I've set up a test. Just a default .ASPX Web form with the following PageLoad method: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Response.ContentType = "text/plain" Response.Clear() Response.Write("") Response.End() End Sub

Import a flat file with combined data into separate SQL tables using SSIS

I have a flat text file (comma delimited) that is essentially multiple files, each with its own format, combined into one file. The file is coming from an external software vendor so unfortunately we don't have much choice but to work with what we are receiving. Here is an example of what the file could look like: Customer Data CustID,FName,LName,PhNum,Email 12345,John,Smith,,jsmith@gmail.com 12346,Jane,Doe,8001111111,jdoe@hotmail.com Customer Plan CustID,PlanType,PlanName,PlanStart 12345,0,Plan1,01/01/2010 12345,2,PlanVis,01/01/2010 12346,3,PlanLf,04/01/2010 12346,0,Plan1,01/01/2010 Customer Payment CustID,LastPayment,Amount 12345,09/01/2010,100.00 12346,05/01/2010,50.00 There is an empty line between each 'section' of data. I adapted a VB script I found online that can take the incoming file and save off each section as its own file so that each one can be separately imported, but this seems inefficient. I'm really new to SSIS in general, but it seems like it shouldn't be that difficult to take the data, split it where there is an empty line, and then import each section into the appropriate SQL table. Any ideas would be most welcome. Thanks!  

saving a text file in ms access data base

i have to read a text file in a text box then have to save it into ms access data base in column format how could i do this....

Unable to load data from txt file with text delimiter specified, if the data has the same character


Have come across a strange scenario and seeking your ideas/thoughts.

I'm using a ssis package to load a txt file. The txt file is text delimited by double quote (") and column delimited by tilde (~). It works fine in most scenarios and strips the enclosing "" from the columns -no problems there. However, in a specific scenario where the last column has double quote in the data as well being the text delimiter, the package throws the following exception indicating that it's missing a column delimiter

 The column delimiter for column "COL_NAME" was not found

This behaviour was not observed when double quotes were present as data in any other column in the file. Was able to workaround by disabled text delimiter only for the last column, but that isn't a perm solution. Also observed that if the " was replaced by escape sequence /" the file loaded successfully. Wondering if this a known issue ?


SSIS - XML Source to multiple SQL tables(Master-Detail)



I've a scenario in which i need to map my xml file to multiple sql server tables, maintaining Referential Integrity. 

Below is my sample xml structure







 <Hdr_ID>ID of the above record</Hdr_ID>




 <Hdr_ID>ID of the above record</Hdr_ID>


as u can see above, 1 parent record has 2 child records. So i need to save the parent record first, based on the ID which is generated automatically, i need to save my child records into the Detail table. I've searched everywhere in google, but couldn't get a solution yet.

(I had done this sort of thing in vb.net but now my job is to do it using SSIS)   :(

Can you gurus help me in achieving my task.

Thanks in advance.


Some problem in Data Flow while upload a "||" seperated text file


I am facing one problem with sql 2005 SSIS while uploading a "||" seperated text file with Data Flow in SSIS.Below I am giving the sample data of the text file



When I uploaded it using sql 2000 DTS it was not giving and it was being uploaded into the table but when I tried with SQL 2005 it is giving the below problem.

"[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". "



How to connect text file data to native SharePoint lists with auto update?


I have to pull external data from a text file to a SharePoint list on a daily base. Additional column content (e.g. attachments) in SharePoint should be kept during update. There should be RSS feeds, email notifications or workflows on external data change (so delete all items and import new will not work).

I've found this codeless 3rd party solution with automatic background update (and write-back???):

Any other idea? BDC-based? Thanks.

Thanks, SharePointFrank http://www.layer2.de/en/products/

Upload Text File data to SQL table Using SSIS

I have a text File with following format  (  HED - Header Row, CL-Data (Sample 3 Rows) , TRL-Trailer Row)


CL 012352908607309000014090 2010-03-04 16:12:53.077000000 Inserted / Log ICode Action on Order Number / 33949591 / with / I-Code / IDEC/ / Memo: / cci questions about alert/

CL 012352958107309000020378 2010-04-14 10:20:03.340000000 Inserted / Log ICode Action on Order Number / 36992594 / with / I-Code / ISRB,INEW,ILKD/ / Memo: / cci to cxl, saved with benefits and gave her instructions to move account online/

CL 012353013807309000021681 2009-11-24 17:09:05.973000000 Inserted / Log Customer Info Change Action CL 012353013807309000021681 2009-11-24 17:09:05.973000000 Updated / Log Customer Info Change Action

TRL201008240001PCMCL 0000007349

I have to Import The above Mentioned data in to Following Table

CREATE TABLE [dbo].[Log]

[Type] [varchar

How do I create an XML file from a text file containing simple ascii data? Put another way, how do I


Im trying to take a text file I downloaded from the US Census Bureau website and transform it into an XML file. It is what the Census Bureau calls a ZCTA file. The schema of the file I have reposted as follows:

ZCTA File Record Layout

The ZCTA file contains data for all 5 digit ZCTAs in the 50 states, District of Columbia and Puerto Rico as of Census 2000. The file is plain ASCII text, one line per record.

  • Columns 1-2: United States Postal Service State Abbreviation
  • Columns 3-66: Name (e.g. 35004 5-Digit ZCTA - there are no post office names)
  • Columns 67-75: Total Population (2000)
  • Columns 76-84: Total Housing Units (2000)
  • Columns 85-98: Land Area (square meters) - Created for statistical purposes only.
  • Columns 99-112: Water Area (square meters) - Created for statistical purposes only.
  • Columns 113-124: Land Area (square miles) - Created for statistical purposes only.
  • Columns 125-136: Water Area (square miles) - Created for statistical purposes only.
  • Columns 137-146: Latitude (decimal degrees) First character is blank or "-" denoting North or South latitude respectively

Columns 147-157: Longitude (decimal degrees) First character is blank or "-" denoting East or West longitude respectively

All I want to do is produce an XML file with t

3 tables, different schemas, going to 1 text file


We have three tables, each with different schemas, and we want to base a new output text file on them. We want to use the top record in table 1 as a header in the new text file, the entire body of Table2 as the "middle" section of the file and finally, the record in Table3 as the footer record in the file. Here's an example showing the tables.


      MyHeader 12345                                         (the content of Table 1)

      I don't think my shrink understands me         (from Table 2)

      I don't think my dog understands me either   (from Table 2)

      It isn't that great to be a footer either!           (from Table 3)


Wanted (Dead or Alive) output file that  looks like this:


MyHeader 12345

I don't think my shrink understand me

I don't think my dog understands me either 

It isn't that g

SqlBulkCopy How to Use to Insert Data From text CSV file to Sql Table



I am New to using the  SqlBulkCopy Can any one tell me how to use it and how many rows maximum we can insert using the SqlBulkCopy

how to habdle the  exceptions when bulkcopy inserting

Create a zedgraph by using the data from a text file

I'm new to zedGraph and based on i need to retrive the data from EC.txt file to form a zedgraph.
        private void graphButton_Click(object sender, EventArgs e)
            Graph energyGraph = new Graph();
            GraphPane myPane = energyGraph.zedGraphControl1.GraphPane;
            myPane.Title.Text = "Graph ";
            myPane.XAxis.Title.Text = "Number of S";
            myPane.YAxis.Title.Text = "EC";
            myPane.XAxis.Scale.Min = 0;
            myPane.XAxis.Scale.Max = 15;
            myPane.YAxis.Scale.Min = 0;
            myPane.YAxis.Scale.Max = 100;
            PointPairList list1 = new PointPairList();
            objStream = new StreamReader("

Exporting encrypted data to a text file


Hi All,


I have a table with one coumn encrypted using SQL encryption. What i need to do is extract this data, encrypted but in a readable format (in the table, the encrypted data looks like asian characters), and send it off to a vendor who will then import and decrypt the data. I've worked through the requirements of db master key/certificate and symmetric key being the same on both our end and the vendor's end.

But i cannot generate a text file properly. I use the import/export wizard and select 'unicode' and when i do a preview i can see the encrypted data string. After i generate the txt file, and load it into my new test database, apply the same db master key/certificate and symmetric key, and then try to decrypt the data, the clumn shows NULL values.

Can someone help out with what im doing wrong in the file generation? Should i be using import/export wizard or something else like bcp?

Thanks in advance

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend