One Row From Multiple Tables With Multiple Items

Mar 11, 2008

I have two tables, Personnel and Emails.
Personnel
*per_personnelID (int)
per_name (varchar)
per_office (varchar)
per_cell (varchar)

Emails
*eml_emailID (int)
eml_personnelID (int)
eml_name (varchar)

I can have a user that has multiple emails but I need to return them all in one row in addition to the name, office and phone. And I need to do this regardless of the number of emails they have. Please, can anyone help the newbie?

View 4 Replies

Insert Items From One To Table To Multiple Smaller Tables

Nov 15, 2004

I have a table that I filled with data imported from another database.

What I need to do is now take this huge table and break apart the information and put it into 5 smaller tables.

So I have a huge insert statement.

I have one main table called Property with two keys. One key is a "Prop_ID" and the other is "owner" where Prop_Id is a automated unique ID. Once the information is inserted into that table, I then get the Unique ID that it was given, and I then used that ID to insert into the other tables.

The problem I am encountering is I keep getting the following error

Violation of PRIMARY KEY constraint 'PK_Prop_Res_Detail'. Cannot insert duplicate key in object 'Prop_Res_Detail'.
The statement has been terminated.

I have an idea what might be going wrong, but I am not sure. What I want to happen is that I want the query to look at the first row of the huge table and then do all 4 of the inserts, and then go to the next row. But I think it is trying to all the inserts into the property table, and then go on to the Prop_Res_Detail table and that is why I am getting that error.

Any help is greatly appreicated.

here is the code..

Code:

CREATE PROCEDURE [dbo].[Insert_Properties]

AS

DECLARE @Prop_ID Int

SET NOCOUNT ON

INSERT INTO Property(Acres,
Assoc_Phone,
Assoc_Cell,
AppraisalForm,
Area,
Assess_Account,
AttachDetach,
Block,
City,
County,
Directions,
DOM,
ER_EA,
FloodZone,
Import_From,
Import_ID,
Insert_Date,
LandSQFT,
LandSQFTDim,
LegalRemarks,
ListAppraiser_ID,
ListAssoc_ID,
ListBroker_ID,
ListDate,
Listing_Office_Remarks,
ListPrice,
Lot,
Map,
Num_Images,
Office_Phone,
Original_ListPrice,
Owner,
Pending_Date,
PhotoName,
PropSubType,
Prop_Type,
Quad,
Remarks,
State,
Status,
StreetDir,
StreetNum,
StreetName,
Township,
UnitNumber,
ZipCode)

SELECT CONVERT(FLOAT(8), Acres),
CONVERT(Varchar(25), Assoc_Phone),
CONVERT(Varchar(25),Assoc_Cell),
CONVERT(Varchar(50), AppraisalForm),
CONVERT(Varchar(10), Area),
CONVERT(Varchar(50), Assess_Account),
CONVERT(Varchar(20), AttachDetach),
CONVERT(Varchar(20), Block),
CONVERT(Varchar(40), City),
CONVERT(Varchar(50), County),
CONVERT(Varchar(1000), Directions),
CONVERT(int, DOM),
CONVERT(Varchar(10), ER_EA),
CONVERT(Varchar(50), FloodZone),
CONVERT(Varchar(20), Import_From),
CONVERT(Varchar(20), Import_ID),
CONVERT(datetime, Insert_Date, 101),
CONVERT(Varchar(20), LandSQFT),
CONVERT(Varchar(50), LandSQFTDim),
CONVERT(Varchar(2000), LegalRemarks),
CONVERT(Varchar(50), ListAppraiser_ID),
CONVERT(Varchar(50), ListAssoc_ID),
CONVERT(Varchar(50), ListBroker_ID),
CONVERT(varchar(11), ListDate),
CONVERT(Varchar(1000), Listing_Office_Remarks),
CONVERT(Varchar(10), ListPrice),
CONVERT(Varchar(20), Lot),
CONVERT(Varchar(10), Map),
CONVERT(Varchar(10), Num_Images),
CONVERT(Varchar(25), Office_Phone),
CONVERT(Varchar(10), Original_ListPrice),
CONVERT(Varchar(50), Owner),
CONVERT(datetime, Pending_Date, 101),
CONVERT(Varchar(50), PhotoName),
CONVERT(Varchar(25), PropSubType),
CONVERT(Varchar(20), Prop_Type),
CONVERT(Varchar(10), Quad),
CONVERT(Varchar(1000), Remarks),
CONVERT(Varchar(25), State),
CONVERT(Varchar(10), Status),
CONVERT(Varchar(4), StreetDir),
CONVERT(Varchar(15), StreetNum),
CONVERT(Varchar(50), StreetName),
CONVERT(Varchar(20), Township),
CONVERT(Varchar(6), UnitNumber),
CONVERT(Varchar(20), ZipCode )

FROM Imported_Closed_Property_From_MLS

SET @Prop_ID = @@Identity

/*Property Res Table */
INSERT INTO Prop_Res_Detail(Prop_ID,
Addition,
Appliances,
Basement_Area,
BasementDesc,
Builder,
Construction,
Cool,
Dining,
District_School,
Energy,
Exterior_Features,
Fence,
Floors,
Foundation,
FP,
FP_Type,
Garage_Attach_Detach,
Garage_Cap,
Handicap,
Heat,
HOA,
HOA_Fee,
HOA_Inc,
HOA_Period,
Inlaw_Plan,
Interior_Features,
Livestock,
Lot_Desc,
Mechanical,
NumLivingArea,
Num_Baths,
Num_Beds,
Num_Levels,
Other_Info,
OvenDesc,
Owner,
Parking,
Patio,
Patio_Dim,
Perc_Basement_Com,
Pool,
Pool_Type,
Prop_Faces,
Range,
RangeDesc,
Remodeled,
Rental,
RentalAmount,
Roof_Type,
Roof_Year,
RoomOther,
Sect,
SQFT,
SQFTSource,
Style,
Tax_Amount,
Tot_Rooms,
UtilityAvailable,
WindowType,
Year_Built)

SELECT @Prop_ID,
CONVERT(Varchar(50), Addition),
CONVERT(Varchar(100), Appliances),
CONVERT(Varchar(25), Basement_Area),
CONVERT(Varchar(100), BasementDesc),
CONVERT(Varchar(50), Builder),
CONVERT(Varchar(50), Construction),
CONVERT(Varchar(20), Cool),
CONVERT(Varchar(10), Dining),
CONVERT(Varchar(60), District_School),
CONVERT(Varchar(100), Energy),
CONVERT(Varchar(100), Exterior_Features),
CONVERT(Varchar(40), Fence),
CONVERT(Varchar(100), Floors),
CONVERT(Varchar(40), Foundation),
CONVERT(Varchar(50), FP),
CONVERT(Varchar(40), FP_Type),
CONVERT(Varchar(50), Garage_Attach_Detach),
CONVERT(Varchar(25), Garage_Cap),
CONVERT(Varchar(20), Handicap),
CONVERT(Varchar(20), Heat),
CONVERT(Varchar(40), HOA),
CONVERT(Varchar(30), HOA_Fee),
CONVERT(Varchar(100), HOA_Inc),
CONVERT(Varchar(20), HOA_Period),
CONVERT(Varchar(20), Inlaw_Plan),
CONVERT(Varchar(100), Interior_Features),
CONVERT(Varchar(40), Livestock),
CONVERT(Varchar(400), Lot_Desc),
CONVERT(Varchar(100), Mechanical),
CONVERT(Varchar(10), NumLivingArea),
CONVERT(Varchar(5), Num_Baths),
CONVERT(Varchar(5), Num_Beds),
CONVERT(Varchar(30), Num_Levels),
CONVERT(Varchar(100), Other_Info),
CONVERT(Varchar(100), OvenDesc),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(100), Parking),
CONVERT(Varchar(25), Patio),
CONVERT(Varchar(50), Patio_Dim),
CONVERT(Varchar(25), Perc_Basement_Com),
CONVERT(Varchar(20), Pool),
CONVERT(Varchar(20), Pool_Type),
CONVERT(Varchar(40), Prop_Faces),
CONVERT(Varchar(20), Range),
CONVERT(Varchar(100), RangeDesc),
CONVERT(Varchar(50), Remodeled),
CONVERT(Varchar(10), Rental),
CONVERT(Varchar(10), RentalAmount),
CONVERT(Varchar(20), Roof_Type),
CONVERT(Varchar(5), Roof_year),
CONVERT(Varchar(100), RoomOther),
CONVERT(Varchar(10), Sect),
CONVERT(Varchar(10), SQFT),
CONVERT(Varchar(50), SQFTSource),
CONVERT(Varchar(100), Style),
CONVERT(Varchar(10), Tax_Amount),
CONVERT(Varchar(5), Tot_Rooms),
CONVERT(Varchar(100), UtilityAvailable),
CONVERT(Varchar(50), WindowType),
CONVERT(Varchar(5), Year_Built)
FROM Imported_Closed_Property_From_MLS

/*Sold Info Table */
INSERT INTO Sold_Info(Prop_ID,
Buy_Pts,
Closed_Date,
Closed_Price,
Closed_Price_SQFT,
COOP_Sales,
Days_On_Market,
InterestRate,
Lender,
LoanAmount,
LoanTerms,
Loan_Years,
Origination_Fee,
Owner,
SellerConcessions,
LoanType,
Sold_Remarks)

SELECT @Prop_ID,
CONVERT(Varchar(10), Buy_Pts),
CONVERT(datetime, Closed_Date, 101),
CONVERT(Varchar(10), Closed_Price),
CONVERT(Varchar(50), Closed_Price_SQFT),
CONVERT(Varchar(50), COOP_Sales),
CONVERT(Varchar(5), DOM),
CONVERT(Varchar(10), InterestRate),
CONVERT(Varchar(50), Lender),
CONVERT(Varchar(10), LoanAmount),
CONVERT(Varchar(50), LoanTerms),
CONVERT(Varchar(10), Loan_Years),
CONVERT(Varchar(10), Origination_Fee),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(100), SellerConcessions),
CONVERT(Varchar(25), LoanType),
CONVERT(Varchar(1000), Sold_Remarks)
FROM Imported_Closed_Property_From_MLS

/*Remarks Table */
INSERT INTO Remarks(Prop_ID,
App_Date,
App_Remark,
Contract_Date,
Inspection_Type,
Owner,
PendingSalesPrice,
PendingSaleComments)

SELECT @Prop_ID,
CONVERT(datetime, App_Date, 101),
CONVERT(Varchar(1000), App_Remark),
CONVERT(datetime, Contract_Date, 101),
CONVERT(Varchar(50), Inspection_Type),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(10), PendingSalesPrice),
CONVERT(Varchar(1000), PendingSaleComments)
FROM Imported_Closed_Property_From_MLS

GO

View 2 Replies View Related

SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables

Mar 21, 2008

Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:

1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.

1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.

1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.

I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.

Thanks in advance for any help/suggestions,
Bob

View 1 Replies View Related

Integration Services :: Import Multiple Files Into Multiple Tables Using SSIS

Jun 16, 2015

I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.

Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.

View 4 Replies View Related

Integration Services :: Import Data From Multiple Excel Sheets To Multiple Tables Using SSIS?

Aug 25, 2015

I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.

E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.

View 6 Replies View Related

One Receipt Number, Query Multiple Tables Gives Multiple Data.

Sep 8, 2006

I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.

Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)

Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.

Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')

Which gives a result of:

RecNo.
30429
Cheque
250
Deposit
250

30429
679.98
250

This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.

View 3 Replies View Related

Merging Data From Multiple Databases With Multiple Tables (all With The Same Structure)

Nov 15, 2006

Hi!

I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.

I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.

I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)

Any pointer most welcome!

best regards and thanks

Thibaut

View 1 Replies View Related

Importing Multiple Flat Files To Multiple Tables In SSIS

Jun 27, 2006

I have a couple of hundred flat files to import into database tables using SSIS.

The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.

However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.

Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.

I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?

View 9 Replies View Related

Multiple Insert Into Multiple Tables With A Stored Procedure

Mar 1, 2007

Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew

View 9 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

Bulk Insert Multiple Files To Multiple Tables - How?

Feb 15, 2008

I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?

Here is XML file:

Code Snippet
<ReferenceFiles>

<File>

<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>

<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>

Thanks.

View 1 Replies View Related

Export Multiple Tables To Multiple Flat Files

Nov 29, 2007

I used the data export wizard to export a single table to a single flat file (multiple wasn't allowed). I saved the package as a *.dtsx file which I'm attempting to edit to add the additional tables.

Creating additional sources is fairly easy copy of the first source and change to the table name.

I've tried copying the destination connection and changing to a new text file, but can't get past having to add each column manually to the new destination.

How can I duplicate the mapping that must be taking place in the wizard in the *.dtsx editing environment?

This seems like a simple / common task, but I've been unable to find a solution.

Thanks, Richard

View 1 Replies View Related

Querying Multiple Tables Multiple Times

May 31, 2007

I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?

View 12 Replies View Related

Search Multiple Parameters In Multiple Tables

Dec 21, 2007

Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
@name var(30),

@nationality (30),

@phone int,

etc

as

Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c

WHERE

name LIKE '%' @name '%'

OR nationality LIKE '%' @nationality '%'

OR phone LIKE '%' @phone '%'

etc

But I got an error when I am trying to execute this code because the nulls values so I wrote
1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25

Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?

Thanks in advanced

View 4 Replies View Related

JOIN Multiple Tables From Multiple Databases

May 23, 2008

Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks

View 3 Replies View Related

Selecting Multiple Records From Multiple Tables

Nov 4, 2004

i want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.

ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.

View 1 Replies View Related

Delete Multiple Records From Multiple Tables

Jan 20, 2006

What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)

delete dbo.tblcase

where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseclient

where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseinformation

where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaselawyer

where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseprosecutor

where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

View 1 Replies View Related

Multiple Flat Files To Multiple Tables

Jun 1, 2007

Hi,

I have searched but not found quite the best way to look at this so far..

I have an application that outputs data to several text files (up to 30). These have commonality by an object name, but then contain completely different column data.

In DTS I had each of the source text file connections going to one OLE DB connection and then individual transform data tasks pointing to the one OLE DB connection.

Looking at SSIS, it would appear that I would need to have one source and one destination for each of these and therefore 30 parallel data flows?

Just wondering if there is a neater way of doing this??

It is a regular data import that happens a few times a day - the text files are named the same as the SQL tables - ie app_userdata.txt goes to app_userdata table.

Hope that explains ok and thanks in advance.

Mike

View 3 Replies View Related

Insert Multiple Items From An ArraryList Into Db

Jan 4, 2008

HiI am working on a site right now. Basicly how it works is they choose from a list of checkboxes what they want to be quized on. This all gets stored in an arrayList. Once they done that they have an option to save there settings so they don't have to go through the list and choose everything again if they want to come back and do that same quiz. So I am trying to take all those options in the arrayList and put them into the database. So I have 4 questions. Question 1.This is how I was thinking my database design should be.This table that all this stuff is going to be inserted into is called QuickLinks. Originally I was thinking of having a PK to FK relationship with the Asp membership table called Membership(or User Table was another posibility). I am now thinking that is a waste of time and pointless. My first reasoning was well this way I can get the UserID from that table but now I realize no I actually need the UserID to be in the QuickLink table before I try to join the two tables together. With joining the 2 tables I don't get anything out of it since if I just do a check if the User is logged in before I insert the data into the database I can just slip the userID in there as well. Then the QuickLink table should have everything it needs. So My question is I am thinking of having the quick Link talbe to operate like this. Say you have 50 items in the array(this is all from the same item). Each item will gets its own row that will have the UserID,CharacterName,CharacterImagePath, QuickLinkName plus the LinkID.So when I need to call that information I just have to look at 2 pieces of information. Find all the rows that have the same QuickLink name and UserID. So is this a good way to store the data? Question 2.Currently right now I have UserID as unquieIdentifer but I am guessing if I have 50 rows with the same UserID it won't like that very much since its not Unquie. Should I just change that to a varChar?LinkID UserID CharacterName CharacterImagePath----------- ---------------------------------------------------------------------------------------------------- ------------- --------------------------------------------------1 15bde69a-c6fe-4159-b60f-405a013ae4c3 hi image2 15bde69a-c6fe-4159-b60f-405a013ae4e2 hi image3 15bde69a-c6fe-4159-b60f-405a013ae4c3 hi imageFind it actually kinda weird that it allowed 2 of the same UserIDs in. Question 3.Right now my primary key is LinkID. I just made that up but right now I have no use for it since I can't think of anything that would be useful to be a primary key but I figured I need a primary key. So does anyone have any ideas if I should leave that as the primary key or not have one? Or what I should do?Question 4I been playing around on a new webpage I made so I don't have so much cludder code to get in the way I don't screw anything up(I just find it sometimes easier to make a watered down version of what I want and get that work before I tackle the big version and have no clue what is going on. I am not sure if this is the best approach). So right now I have text box that I type in the UserID into(I have gotten the part to work where it checks the login of the user and grabs it but I don't want use it for now since it might complicate things a bit).I have then a textbox where you type in the image path and then 2 other textboxes that you type in the characterName. Those 2 textBoxes for the characterName gets stored in array(so it is almost like the real thing I want to do). I then tired to put my code in a for loop and tired to keep inserting it in until everything from the array was out. However it does not work. I get this errorThe variable name '@UserID' has already been declared. Variable names must be unique within a query batch or stored procedure. I am not sure what it means or wants. My code. ArrayList InsertCharcter = new ArrayList();
InsertCharcter.Add(TextBox1.Text);
InsertCharcter.Add(TextBox2.Text);

SqlCommand comm;
string holdInsert = "INSERT INTO QuickLinks (UserID, CharacterName, CharacterImagePath) VALUES (@UserID,@CharacterName,@CharacterImagePath)";
comm = new SqlCommand(holdInsert, getConnection());
Guid userID = new Guid(txtuserID.Text);

comm.Connection.Open();
for (int i = 0; i < InsertCharcter.Count; i++)
{
comm.Parameters.Add("@UserID", SqlDbType.VarChar);
comm.Parameters["@UserID"].Value = txtuserID.Text;
comm.Parameters.Add("@CharacterName", SqlDbType.VarChar);
comm.Parameters["@CharacterName"].Value = InsertCharcter[i].ToString();
comm.Parameters.Add("@CharacterImagePath", SqlDbType.VarChar);
comm.Parameters["@CharacterImagePath"].Value = txtImage.Text;

comm.ExecuteNonQuery(); } comm.Connection.Close(); Thanks

View 2 Replies View Related

Search For Items Across Multiple Table...

Oct 24, 2007

I have been working on a database and I have imported a number of lists into multiple tables.

I am having a lot of trouble writing the SQL code to be able to query across multiple tables. For instance, I want to know if john doe is on all five lists or if he is only on 3 lists (search by student name).

Can someone help me with this?

Luin.

View 2 Replies View Related

Multiple Instances Of Menu Items

Dec 10, 2006

When using SQL Server Management Studio Express I get multiples of all the standard Windows menu items as well as in the dropdowns. I have also experienced this in the Visual Basic 2005 Exprerss edition IDE. What is causing this?

View 10 Replies View Related

How To Show Multiple Items In A Textbox?

Nov 27, 2007

In a multi-parameters report I want to show the parameter(s) user selected in a text box. How to set this up. For example user selected A, B, C in the parameter dropdown etc in the text box I want to show A, B, C.
Thanks

View 1 Replies View Related

Multiple Items On A Report Problem?

Nov 30, 2007

We are trying to do something basic with SRS but we can't seem to get iit to work. Here's the scenario: We want to send out a monthly report that consists of numerous subreports. Each subreport stands on its own withh its own data source.

On our main report we drag the subreports into position as we want them to display. But when we run the report sometimes we get 2 subreports on 1 page, other times we get 1.... Bottom line is that we want to get 7-8 subreports on one page. Any ideas on what we can do to get everything on one page?

View 4 Replies View Related

How Do I Store Multiple Selected Listbox Items

Jan 12, 2008

Hi There
This is probably realy simple but since im still a newbie some help would be appreciated. I have a listbox bound to an sqldatasource which has names of people from my sql database employeeDetails table. I simply want to allow someone to select to select multiple names and insert them into another field in my database.
I have this bit workng, ie they can select multiple people on the list, it does insert, however only the first selected name, not the others.Can you please help me out
Kind Regads
Dan

View 3 Replies View Related

Simple Explanation To Concatenating Multiple Items Into One Column

Jan 17, 2008

Hi, I am a extreme beginer to sql server and i am i'm having big trouble trying to display my sql query properly. Bascially i want to put the results of a one to many query into one row per record. I have read articles and forums discussing 'concatenating the values' or creating a function??? but i dont follow what they mean and i am completely lost. Can anyone provide a really simple explanation on what i need to do to resolve my duplicate row issue? i urgently need to find a solution to this.
Regards

View 2 Replies View Related

Display Multiple Items On One Line Per Order (was Query)

Jun 27, 2005

I have table1 with orderID and demographic info.
Table2 with orderID and items.
I would like to have a results display like this:
OrderIDDemographicInfo Item1Item2Item3....ect
One line per order. When I do a join I displaying all items in different rows.

View 1 Replies View Related

Select Query To Fetch Multiple Checkbox Items

Nov 15, 2013

I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.

Checkbox tag:
<asp:CheckBoxList ID="cblGroup" Style="vertical-align: baseline" runat="server" CssClass="chkbox">
</asp:CheckBoxList>

Select Query:SqlCommand cmd = new SqlCommand("select * from AppInvent_Test where Designation= '" + cblGroup.SelectedValue + "'", con);

View 2 Replies View Related

Creating Query To Update A Field For Multiple Items

May 5, 2015

Looking to write an query that will update a field for multiple items, like 1,500.

something like:

UPDATE INMAST
SET FPRICE = 111.11

WHERE
INMAST.FPARTNO = 'xxx'

only issue I'm having is a need to do a JOIN because there's one more condition that must be met from another table, i've tried this:

SET FPRICE = 111.11
JOIN INVCUR
ON
(inmast.fpartno + inmast.frev)= (invcur.fcpartno + invcur.fcpartrev)

WHERE
INMAST.FPARTNO = 'NRE'
AND
invcur.flanycur = 'TRUE'

but that is giving me an error around the JOIN

View 11 Replies View Related

Multiple Rows To Multiple Tables

May 4, 2001

Hi, Gurus,

I am trying to populate a table with repeating groups in multiple columns by using information from two other tables. The sample tables and records are like:

Table A

CSID

1
2
3
4

Table B

CP_IDCO_CODE

12C1
12C2
12C3
12C4
13C5
13C6
13C7
13C8
14C9
14C10
14C11
14C12

Table C (The empty table I want to populate like the following)

CSID CP_ID_1 CO_CODE_1 CP_ID_2 CO_CODE_2 CP_ID_3 CO_CODE_3

112C1 13 C514 C9
212C2 13 C614 C10
312C3 13 C714 C11
412C4 13 C814 C12

What is the best way to do it? Thanks in advance.

Sam

View 2 Replies View Related

DTS - Multiple Files To Multiple Tables

Aug 4, 2004

I am trying to write (my first, unfortunatly) DTS, and am having some problems.

I need to be able to import multiple flatfiles (all in the same format, just with different schema), each one going into a different table. I have written an application to call my DTS, sending it variables for the tablename and the filename. This works fine when I test it on a single flatfile.

My problem is, the Tranformation object does not reset after each DTS call, so I get "Column does not exist" errors after the first successful import. I can go into the DTS Manager and reset the Transformation options, but that would defeat the purpose of automation. Is there anyway to reset, or another technique, the Transformation object so that it will continuosly work on files that use different schema?

I am very new at DTS, so please consider me "ignorant" when replying.

Thanks in advance.

- Jordan

View 4 Replies View Related

Import Multiple Csv Into Multiple Tables

Aug 9, 2006

Is there a way to import multiple csv files from a directory into sql2005? The situation I have right now is that I have a folder withmultiple csv files that i need to import into sql 2005. I can do itwith the import wizard but it takes to long. The files will be updatedmonthly. The first row in the files contains all the header informationwhich may change monthy. What I am looking to do is import all of thesecsv into tables. One csv file into for one table. Ideally I would liketo use the name of the csv file as the name of the table. Any bump inthe right direction would be apprecieted

View 1 Replies View Related

Delete Statement For A List Of Items With Multiple Columns Identifying Primary Key

Jul 7, 2006

I frequently have the problem where I have a list of items to delete ina temp table, such asProjectId Description------------- ----------------1 test12 test43 test34 test2And I want to delete all those items from another table.. What is thebest way to do that? If I use two IN clauses it will do it where itmatches anything in both, not the exact combination of the two. I can'tdo joins in a delete clause like an update, so how is this typicallyhandled?The only way I can see so far to get around it is to concatenate thecolumns like CAST(ProjectId as varchar) + '-' + Description and do anIN clause on that which is pretty nasty.Any better way?

View 2 Replies View Related

How To Create Multiple Tables On The Fly So That Every User Each Has His/her Own Set Of Tables?

Aug 12, 2006

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

View 6 Replies View Related