I am creating a DTS package to export a text file. My question is: does anyone have any ideas on how for one read of the tables I can produce 2 lines of output. Here is how the file layout needs to be...
HEADER DETAIL HEADER DETAIL HEADER DETAIL
I am a little confused about how I can stagger header and detail using the same data.
I appreciate any help you can give. Hopefully my explanation of the problem is understandable.
Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11).
So far I have two stored procedures: 1) sp_copyDetailsOne /*Create a recordset of the desired items to be copied*/ CREATE PROCEDURE sp_copyDetailsOne @invoiceIdFrom INT, @outCrsr CURSOR VARYING OUTPUT AS SELECT itemId, itemPrice, itemDescription, itemQuantity FROM tblSalesItems WHERE (invoiceId = @invoiceIdFrom) OPEN @outCrsr
2) sp_copyDetailsTwo CREATE PROCEDURE sp_copyDetailsTwo @invoiceIdFrom INT, @invoiceIdTo INT /*Allocate a cursor variable*/ DECLARE @crsrVar CURSOR
/*Loop through the recordset and insert a new record using the new invoiceId*/ FETCH NEXT FROM @crsrVar WHILE @@FETCH_STATUS = 0 BEGIN
/*Run the insert here*/ INSERT INTO tblSalesItems (invoiceId, itemId, itemPrice, itemDescription, itemQuantity) VALUES (@invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30)
/*Fetch next record from cursor*/ FETCH NEXT FROM @crsrVar END
CLOSE @crsrVar DEALLOCATE @crsrVar
My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome.
I have a header and detail table. I want to create a constraint on the detail table, based on a value it's linked to in the header table. If the bit is checked in header then a unique value is required , if it's not checked then a NULL value is acceptable.
Hi All,There is some additional info I forget on this same topic I just posted.I have a database design question, pls give me some help..I want to define tables for salesman's sales target commission . Thecommission could be given per EITHER sales amount of : Group of Products ORGroup of Brand. e.g : the data example :For one salesman_A :product_1, product_2, product_3 etc.. => sales = $100 - $200 =>commission = 5%product_1, product_2, product_3 etc.. => sales = $201 - $400 =>commission = 10%Brand_A, Brand_B, Brand_C .. etc => sales = $100 - $200 =>commission = 2.5%Brand_A, Brand_B, Brand_C .. etc => sales = $201 - $400 =>commission = 5%Below is my table design, is this a good design or something is wrong here ?Thank you for your help.CREATE TABLE Sales_Commission_Header (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Note Varchar(30),Constraint Sales_Commission_Header_PK Primary Key(Sales_ID,Sales_Commission_Group))Alter Table Sales_Commission_Header Add ConstraintFK_Sales_Commission_Header Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Sales_Commission_Detail (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Sales_From Decimal(12,2) ,Sales_To Decimal(12,2) ,Commission Decimal(5,2),Constraint Sales_Commission_Detail_PK Primary Key(Sales_ID,Sales_Commission_Group, Sales_From, Sales_To))Alter Table Sales_Commission_Detail Add Constraint FK_Sales_CommissionForeign Key (Sales_ID, Sales_Commission_Group) ReferencesSales_Commission_Header(Sales_ID, Sales_Commission_Group)--------------------------------------------** ALTERNATIVE _1 :CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4) Primary Key,Note Varchar(30))CREATE TABLE Commission_Group_Detail_Product (Sales_Commission_Group Char(4),Product_ID VarChar(10), -- This product_ID will be FKreference to master productConstraint Commission_Group_Detail_Product_PK PrimaryKey(Sales_Commission_Group, Product_ID))Alter Table Commission_Group_Detail_Product Add ConstraintFK_Commission_Group_Detail_Product Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Commission_Group_Detail_Brand (Sales_Commission_Group Char(4),Brand_ID VarChar(10), -- This brand_ID will be FKreference to master brandConstraint Commission_Group_Detail_Brand_PK PrimaryKey(Sales_Commission_Group, Brand_ID))Alter Table Commission_Group_Detail_Brand Add ConstraintFK_Commission_Group_Detail_Brans Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)** ALTERNATIVE _2 :CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4),Group_Type Char(1), -- 'B': Brand Group 'P': Product GroupNote Varchar(30),Constraint Commission_Group_Header_PK Primary Key(Sales_Commission_Group,Group_Type))CREATE TABLE Commission_Group_Detail (Sales_Commission_Group Char(4),Group_Type Char(1), -- 'B': Brand Group 'P': Product GroupProduct_Brand_ID VarChar(10),Constraint Commission_Group_Detail_PK Primary Key(Sales_Commission_Group,Group_Type, Product_Brand_ID))Alter Table Commission_Group_Detail Add ConstraintFK_Commission_Group_Detail Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group, Group_Type)The PROBLEM here is : with Product_Brand_ID , I CAN NOT make foreign keyinto both Master Product and Master Brand.So which one is better design ?split the Commission_Group_Detail into Two tables, product and brand , andmake the FOREIGN KEYto master product and master brand (previous mail)ORcombile Commission_Group_Detail for Product and Brand into one table likeaboveand NOT make any FK to master Product or Brand ?Thank you for your help,Tristant
I can't believe it's been a few days and I can't figure this out. We have a flat file (purchaseOrder.txt) that has header and detail lines. It gets dropped in a folder. I need to pick it up and insert it into normalized tables and/or transform it into another file structure or .NET class.
10001,2005/01/01,some more data SOME PRODUCT 1, 10 SOME PRODUCT 2, 5
Can somebody place give me some guidance on how to do this in SSIS?
I have a flat file with header and detail information, it is actually employee punch card data. I need to parse the header line which contains the Employee ID and don't save it to a table just save the value. Then with the detail line, parse the different data elements and save them along with the employee ID to one table. Then continue until the next header line is read.
So I think I need a data flow transformation object that let's me save the Employee ID into a variable available when the next record is read. What type of transformation would be best?
I have a report with two groups and a detail row (subtotals & totals to follow). When I add the child (detail row) it pushes out to the right of the parent column. Is there any way to start the detail row all the way back to the left hand side of the page? I lose a lot or real estate with the group descriptions.
I€™ve created with the help of some great people an SSIS 2005 package which does the follow so far:
1) Takes an incoming txt file. Example txt file: http://www.webfound.net/split.txt
The txt file going from top to bottom is sort of grouped like this Header Row (designated by €˜HD€™) Corresponding Detail Rows for the Header Row €¦.. Next Header Row Corresponding Detail Rows
€¦and so on
http://www.webfound.net/rows.jpg
2) Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table. A uniqueID has been created for each header and it€™s related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started. The reason I split this out is so I can massage it later with stored proc filters, whatever€¦
Now I€™m trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file. So, if you look at the original txt file, each new header and it€™s related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file.
This is where I€™m stuck. How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files.
The filenames of the txt files will vary and be based on one of the column values already in the header table.
I have a problem with a report, and I am running out of ideas on how to approach it. I have a report with groupings of data displayed, and once per group I need to display an image. When I place the image in a record, it shrinks it down to the height of the record and repeats the image for each record despite what formulas I put in the hidden property.
I have a province table in a my database. I would like to link this province table to a resource table's Province_ID. This Province_ID is an int. Vic Valentic CEO/President Open Door 2 Elite Dr. #33 Hamilton, Ontario L8W 2N3 905-389-7492 http://www.wlu.ca/next/opendoor
In second Table store those records who are selected and stored in 2nd table. NameAppidFunctionCodeFunNameSubFunCode Data810Summary0 Data820View0 Data830&View0 Ad630Mbl20
Our requirements we use one query, In query fetch total 5 rows. and output show like this
The plant number field is a location based field that the application uses to filter/select data on for the end users. What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location.
I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
While backing up our database, I am getting the following message:
Could not insert a backup or restore history/detail record in msdb.dbo.sysbackuphistory or sysrestorehistory. This may indicate a problem with the MSDB database. DUMP/LOAD was still successful. (Message 3009)
In RS 2005 i am using a table to show the multiple records.Now i want to repeat the header of the table for each record of table but dont know how to do this.the layout should like..
Well im new at this SSIS stuff and i have something that i am trying to do, but cannot get it to work.. Ill try to explain, and if anyone can help me or point me in the right direction it would be much appriciated..
I have 2 tables, one header table. and one lines table. This is a one - to -many relationship.. ie 1 header, many lines.. This is a Order Header, and Order Lines table setup.. Order header has Order numbers and and email address field that link to the lines table by order number. I also have a view which links all this info together.
I would like to export a excel file (preferable named from the order number column - but that can come later) for each order number in the header table. The excel file will contain the details from the View that was created. I want this to loop through all the header records in the header table and create a excel file for each one..
Down the track i want to add a send mail task to this and pass the email address to a variable so i can use it in the send mail task.. But ill get the main part working first..
Anyhelp would be more than helpful.. I tried to set this up, but i am stuck on the enumerator part..
I am listing detail transaction lines in a table sorted by account and order number. the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order.
I was trying Group by and Having but this doesn't seem to do what I need.
Being relatively new to Reporting services, any nudge in the right direction would be useful.
Hi-I have a sql database (2005) that I need to extract a report from that looks somehintg like SELECT * From Empl_Hours WHERE some_flag <> 'true' .The thing works fine, but the problem is this: I need to insert a record in the 1st row that looks like "Static_text"+row_count() +"more_static_text"where row_count is the actual # of rows that were retrieved. Thanks in advance for any help.DAn
When calling my SELECT statement, I would like to have Column name and Data type as the first record. Is this possible to do? I would like to make a SP that would select all data from any table(with a header row) with just:
_sp_getData ' table_name'
I looked at sysobjects, and syscolumns tables, but was unable to put it all together. I have over 200+ tables, so creating variables for every column is very cumbersome.
Something like this:
select 0 AS SortCol , cast(min(case ordinal_position when 1 then column_name end) as varchar) as col1, cast(min(case ordinal_position when 2 then column_name end) as varchar) as col2, cast(min(case ordinal_position when 3 then column_name end) as varchar) as col3, cast(min(case ordinal_position when 4 then column_name end) as varchar) as col4, cast(min(case ordinal_position when 5 then column_name end) as varchar) as col5, cast(min(case ordinal_position when 6 then column_name end) as varchar) as col6, cast(min(case ordinal_position when 7 then column_name end) as varchar) as col7, cast(min(case ordinal_position when 8 then column_name end) as varchar) as col8, cast(min(case ordinal_position when 9 then column_name end) as varchar) as col9 from information_schema.columns where table_name = 'authors' union all select 1 , au_id, au_lname, au_fname, phone, address, city, state, zip, cast(contract as varchar) from authors
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.* FROM MASTER WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
I have an application providing me with multiple headers which I havemergerd into one big header (below), this header my not always be thesame but I need to be able to extract a periodstart and periodend fromit. The periodstart will always be the third substring from the end(or 3rd from right) and the periodend will always be the firstsubstring from the end (or 1st from the right).How can I extract the periodstart and periodend?E.g:- Header'Jensen Alpha TR UKN GBP BM: Caut Mgd BM (50% FTAllSh 50% ML £ BroadMkt) RF DEF:RFI 3Y 31/08/2004 To 31/08/2007'I currently have the sql: convert(Datetime,(dbo.FDHGetWord(@FullHeader, 20)) ,103) but this only works in thisinstance, I need to use someting like the RIGHT function or REVERSEfunction but I can't get the sql right.Can someone please help!????
Is is possible to insert a record through a view. If so, how?
USE Northwind
GO
CREATE TABLE tbForms ( FormID INT IDENTITY (1,1) NOT NULL, Form varchar (100) NOT NULL )
GO
ALTER TABLE tbForms ADD CONSTRAINT tbForms_pk PRIMARY KEY (FormID) GO
CREATE TABLE tbDoubleTeeForms ( fkFormID INT NOT NULL, Form varchar(100) NOT NULL, Width FLOAT, Height FLOAT, Flange FLOAT, Leg FLOAT, LegCount INT )
GO
ALTER TABLE tbDoubleTeeForms ADD CONSTRAINT tbDoubleTeeForms_pk PRIMARY KEY (fkFormID) GO
ALTER TABLE tbDoubleTeeForms ADD CONSTRAINT tbDoubleTeeForms_fk FOREIGN KEY (fkFormID) REFERENCES tbForms (FormID) GO
CREATE TABLE tbFlatPanelForms ( fkFormID INT NOT NULL, Form varchar(100) NOT NULL, Width FLOAT, HEIGHT FLOAT )
GO
ALTER TABLE tbFlatPanelForms ADD CONSTRAINT tbFlatPanelForms_pk PRIMARY KEY (fkFormID) GO
ALTER TABLE tbFlatPanelForms ADD CONSTRAINT tbFlatPanelForms_fk FOREIGN KEY (fkFormID) REFERENCES tbForms (FormID) GO
CREATE VIEW MyProducts AS SELECT fkFormID, Form FROM tbDoubleTeeForms UNION ALL SELECT fkFormID, FOrm FROM tbFlatPanelForms
GO
-- How can I insert a new record, the pk of the forms table is identity. -- Can this be done? INSERT INTO MyProducts (Form) VALUES ('My First Entry') GO
Hi All,I am using Microsoft SQL Enterprise Manager version 8.0 and havecreated a view from a combination of 4 different tables. I would liketo be able to go into sql and open the view and select a row anddelete that row however this seem impossible right now. I am not sureif it's possible to delete a row from a view?? Or could it be thatthese tables are all interconnected and in order to delete a recordthat is joined to one or more of the tables it has to be deleted atthe top level of the join heirarchy etc etc. (do you understand what imean?) Can this be done??Thanks in advance,Erin
Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?
For example let’s say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?
recno persons 1person1 2person2 3person3
Please help me
I will be very grateful if you also reply your answers also and to my email
is this possible to retireve data from view where i need only userid with rolekey1.? tried with a function but its taking more time? any options in doing it in the view itself?
