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


ADVERTISEMENT

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

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

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

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

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

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

Insert Multiple Files

Jan 24, 2008

Is there any control flow task or any task in SSIS that is able to get 50 XML files
from a directory on a server and insert them into a table/column of xml datatype
in a sql server database?

I lnow I can use the foreachloop container and specify the directory path to pick up
the xml files but what do you do after that?

I also know of the OPENROWSET Function but that does 1 file at a time only!

??


View 5 Replies View Related

Insert Multiple Files

Mar 6, 2008

I have 60 XML files in the following directory, how do I loop through each one to insert into a table like the code below only does 1 file.

How do I store each filename in a variable and the concatenate it to the following code?


DECLARE @XML XML

SELECT @XML = CAST(BulkColumn AS XML)

FROM OPENROWSET(BULK '\SQLSRV1ConvergCust_21012008.xml' , SINGLE_BLOB) AS x

INSERT dbo.Test.XML (XMLDoc)

SELECT @XML

View 4 Replies View Related

Multiple Flat Files To Different Tables (SSIS)

Feb 21, 2012

So we are running SQL 2000 and we use a DTS Package to load about 100 txt files to different tables in a SQL DB.

So in DTS packages you define the connection (database) and the different source files will connect to the connection.

You define the destination on the flow arrow. My question is how do i accomplish this in SQL 2008 using SSIS. (upgrading SQL).

View 9 Replies View Related

Exporting Multiple Tables As Flat Files

Sep 20, 2006

Hi.

I've tried to create a SSIS package to simply export a bunch of tables as flat files, and am having troubles because when the for each loop hits the second table the column mappings in the flat file destination are not synchronised with its schema.

I created a for each loop with an enumerator that returns the table names and sets a user variable.

I created a data flow task which dynamically connects to the table name variable.

In the Flat File Destination there is a column mapping property, but I don't know how to reset these mappings on each iteration.

Any ideas?

View 3 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

Insert Into Multiple Tables

Aug 6, 2007

hi guys,

can someone please tell me the best ways to insert data into multiple related tables .

cheers guys

andy

View 2 Replies View Related

SQL Tools :: How To Restore Multiple Databases From Multiple BAK Files At Once

Aug 14, 2012

I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.

View 13 Replies View Related

Integration Services :: Loading Multiple Flat Files Into Different Tables Using SSIS?

Oct 25, 2015

I have been tasked to do the following using SSIS.

We received two csv files each week and we would like to load these files to two different sql server tables using SSIS.

These files should be archived into a folder after each load.  

How can I achieve this?

View 6 Replies View Related

MS SQL Basics: Insert Into Multiple Tables At Once.

Aug 15, 2006

I'm trying to do something very basic here but I'm totally new to MS SQL Server Manager and MS SQL in general.
I'm using the Database Designer in MS SQL Server Manager. I created two tables with the following properties:
Schedule (ScheduleID as UniqueIdentifier PK, Time as dateTime)
Course (CourseID as UniqueIdentifier PK, Name as VarChar(50))
I create a relationship by dragging the PK from the first table over to the second and I link on ScheduleID to CourseID columns (I'm not certain what type of relationship is created here N:N?). It appears to work, I can do a Select * and join the two tables to get a joined query.
The problem starts when I try to populate the tables: a course will have a schedule. I can't seem to get the rows to populate across both tables. I try to select the pk from the first table and insert it into the second but it complians about it not being a uniqueidentifier. I know this is very basic but I can't seem to find this very basic tutorial anywhere.
I come from the Oracle world of doing DB's so if you have some examples that relate across that would be great or better yet if you can point me to a good reference for doing M$ DB stuff that would be great.
Thanks.

View 9 Replies View Related

Insert Multiple Records Into 2 Tables

Aug 29, 2006

I have searched in length and cant seem to find a specific answer.I have a tmptable to hold user "shoppingcart" ( internal supplies)What i want is to take when the user clicks order to take that table pull the records with that user and populate the order and order details tablesThe order table has a PK of orderID and the orderdetails has a FK of orderIDI know how to insert to the main table, but dont know how to populate the details at the same timeI have this.Insert into supplyordersselect requestor from tmpordercart where requestor = &name so how do i also take from the tmpordercart the itemno and quanity and put them into the orderdetails so that it links back to order table?

View 1 Replies View Related

Insert Data In Multiple Tables

Oct 10, 2007

hi. can anyone help me, please.
i am using vwd2005 express edition and sql server 2005 express. i want to insert data in multiple tables at once. the tales are linked to each other through primary key and foreign keys. for example i have one table with a primary key. when i add data to it, i have to retrieve the id of the newly inserted record and then introduce this id in another  table as a foreign key. i have 10 tables linked in this way, and only one form to add data to the database.
can you help me, please? i'd be very greatful. thanks.

View 3 Replies View Related

INSERT INTO Multiple Tables Rows

Jul 5, 2005

Can i insert values into multiple tables? Usually we using this

INSERT INTO Customers (CustomerID) VALUES ('ABC')

But i want to combine both into one statement
INSERT INTO Customers (CustomerID) VALUES ('ABC')
INSERT INTO Orders (OrderID) VALUES ('DEF')

View 12 Replies View Related

Insert/Updated SP From Multiple Tables

Nov 23, 2004

How do I insert unrelated statistical data from three tables into another
table that already exist with data using an insert or update stored procedure?
OR...
How do I write an insert/Update stored procedure that has multiple select
and a where something = something statements?

This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this....

CREATE PROCEDURE AddDrawStats
AS
INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)

SELECT
WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),

TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),

RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )

FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID

WHERE D.DrawingID = DS.CurrentDrawing
GO

View 6 Replies View Related

Help With INSERT Multiple Tables In Many-to-many Relationship?

Feb 2, 2006

I am relatively new to MS SQL (not a novice, but hardly a master).

I am working on a content management application for a magazine publisher. It’s written in ASP (VB Script) and being created on Dreamweaver 8 with an MS SQL 2000 database. Now, I’m trying to decide the best and fastest approach to coding a complex INSERT and UPDATE function. My question isn’t as much about the SQL (although that will probably come up after I decide how to do this), but about the procedural steps and approach I shuold be taking to do this.

Reporters will use an online form to enter their story into the system. It collects the usual data: Headline, byline, story content, and the story category (feature, opinion, entertainment, business, sports, etc.). Each story may belong to MULTIPLE categories (feature & business, for example).

So, I’ve created three tables to support this many-to-many realtionship:

Story
Category
StoryCat (a junction table with the IDs from both the other tables).

The online form has a dropdown menu which pulls the available categories from the Categories table. When the reporter has entered the data I use ASP to performs the insert just as you would expect it to.

The next step needs to be to update the StoryCat table so that it creates a new record with the StoryID of the record it just inserted, along with the CategoryID that was in that record.

------------------

As I said, I’m not sure of the best way to do this.

Should I just pull back the last record inserted and then create a procedure that would insert into the StoryCat table (which is what I’m thinking of doing on the confirmation page), or is there another approach I should take (perhaps some sort of temporary table or stored procedure?).

Any and all help will be greatly appreciated.

View 1 Replies View Related

INSERT Records In Multiple Tables

Apr 2, 2004

I need to update two tables. I have created a view and am using the code in the attached file to insert into the two tables.

The page loads without errors, but I get this message that the view is not updatable because the modification affects multiple base tables.

I thought this was the purpose of views?

Does anyone have any suggestions? I am using Dreamweaver MX and SQL Server.

Thanks!
N

View 11 Replies View Related

INSERT ... SELECT Into Multiple Tables

Apr 7, 2008

Hi,

I have a 'charges' table that records charges for an invoice. There are several different types of charges, each with its own unique set of additional data fields that need to be recorded.

I maintain separate tables for each charge type and these tables participate in an "ISA" relationship with the main charges table.

Here is a simplified version of my schema. Hourly charges are one type of charge:

charges table
=============
id int (autoincremented primary key)
date datetime
amount money

hourly_charges table
====================
charge_id int (primary key, also a foreign key to charges table)
start_time datetime
end_time datetime

I need to write a query that will duplicate all charges meeting a certain criteria by inserting new records into both the charges table and the hourly_charges table.

Here is some non-working pseudo-code that hopefully will get across what I would like to accomplish:

INSERT INTO charges JOIN hourly_charges
(
charges.date,
charges.amount,
hourly_charges.charge_id,
hourly_charges.start_time,
hourly_charges.end_time
)
SELECT
date,
amount,
SCOPE_IDENTITY(),
start_time,
end_time
FROM charges
JOIN hourly_charges
ON charges.id = hourly_charges.charge_id
WHERE some condition is true

Now I realize this code is invalid and I'll have to go about this an entirely different way but I'm wondering if someone can tell me what the proper way is.

Thanks,

Adam Soltys
http://adamsoltys.com/

View 3 Replies View Related

Insert Data In Multiple Tables

Apr 27, 2007

Hi there!!! Trying to figure something out, I have searched this forum but no answer to my dilemma.

I have three tables on a database that I have to insert new data and update the old one. The structure of the tables is like this:

Table1
custid int primary key,identity
fname varchar(30)
lname varchar(30)

Table2
fileid int primary key, identity
ssn varchar(11)
custid int foreign key

Table3
statusid int primary key,identity
title varchar(18)
fileid int foreign key


This is very general but that's the idea. Now I receive a text file with the necessary info, I've already parse and break down the file into the correct fields.

Now for my question, how I insert the relevant data onto the tables from this one parsed file? and also how I go about inserting the primary keys from the different tables onto the foreign keys of each tables?

I tried relationships and key indexes, but it just spew a bunch of errors, that I'm investigating.

Any ideas, pointers, tuts, something I'm missing?

BTW I'm using sql 2005 express and VB.net


Thanks in advance for any help.


_José

View 1 Replies View Related

Insert With Max Values From Multiple Tables

Dec 20, 2007

I am trying to add records to 2 separate tables and each table has a unique ID from the other table. I need to find the highest number ID from both tables (and add 1) and add a record with both new values to both tables.

I have tried the following, but it is not working. Any help would be appreciated, Thanks (my tables are MEMOS and PDSMSGC)


INSERT INTO MEMOS (MemoID, ParentID, FieldName, MemoText)
SELECT MAX(MemoID) + 1 FROM "MEMOS", MAX(MessageID) +1 FROM "PDSMSGC", 'pmc:MemoID', 'Hi my name is bob'
INSERT INTO PDSMSGC (MessageID, MemoID) SELECT MAX(MessageID) + 1 FROM "PDSMSGC", MAX(MemoID) FROM "MEMOS"

View 5 Replies View Related

How Do I Insert Into Multiple Tables Bases On Conditions?

Jan 6, 2004

I have a record that I want to insert into (2) tables. The first thing I want to do is see if a record already exists in the table for the user, if it does - I just want to skip over the insert.

next I want to do the same thing in the SW_REQUEST table. If there is a record in there for the member, I want to just skip the insert.

My code works as long as there isn't an existing record in the tables. Can someone give me a hand?


Here's what I have (and it doesn't work)


CREATE PROCEDURE b4b_sw_request

@FName as varchar(50)= NULL,
@LName as varchar(50)=NULL,
@Address1 as varchar(100) = NULL,
@Address2 as varchar(100) = NULL,
@City as varchar(50) = NULL,
@State as char(2) = NULL,
@Zip as char(5) = NULL,
@Email as varchar(100) = NULL,
@Send_Updates as smallint = '0'

AS

IF EXISTS
(SELECT FName, LName, Address1, Zip from MEMBERS WHERE FName = @FName AND LName = @LName AND Zip = @Zip)
BEGIN
RETURN
END

ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
Values
(@FName, @LName, @Address1, @Address2, @City, @State, @Zip, @Email)
END

IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @@Identity)
BEGIN
RETURN
END

ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@@Identity, @Send_Updates)
END
GO

View 2 Replies View Related

Stored Procedure Insert - Multiple Tables

Jan 26, 2012

How to insert data into 2 tables in a stored proc.

Scenario is:

Table 1 insert is generated and the primary key is created. This key is an identity column and is the only thing that makes the row unique.

Table 2 needs an insert but one of the columns that is needed is the newly created column 1 primary key.

How do I know what the new rows primary key value is from Table 1

View 3 Replies View Related

Need To Insert Records From Multiple Access Tables Into 1

Mar 17, 2014

Using SSE 2012 64-bit.I need to insert records from multiple Access Tables into 1 Table in SSE and ensure no duplicates are inserted.This is executing, but is very slow, is there a faster way?

Code:
INSERT INTO dbTarget.dbo.tblTarget
(All fields)
SELECT
(All Fields)
FROM dbSource.dbo.tblSource
WHERE RecordID NOT IN (SELECT RecordID FROM dbTarget.dbo.tblTarget)

View 6 Replies View Related

T-SQL (SS2K8) :: INSERT Data From Multiple Tables

Dec 1, 2014

I have 3 tables: CUSTOMER, SALES_HEADER, SALES_DETAIL and there are no relationships / keys between these tables.

I want to INSERT into SALES_HEADER from CUSTOMER & SALES_DETAIL. Here is the query I used.

insert into sales_header (SALES_ID, CUST_ID, SALES_AMOUNT)
select SALES_DETAIL.sales_id, SUM(SALES_DETAIL.prod_price) as sales_amount, CUSTOMER.CUST_ID
from SALES_DETAIL, CUSTOMER
where SALES_HEADER.sales_id = CUSTOMER.cust_id
group by sales_detail.SALES_ID, CUSTOMER.cust_id;

It shows parsed correctly, but giving error: The multi-part identifier "SALES_HEADER.CUST_ID" could not be bound. How to insert from multiple tables when there are no primary / foreign keys & relationships.

View 3 Replies View Related

Transact SQL :: Insert Records From Multiple Tables

Aug 30, 2015

This is a bit lengthy, but lets say we have three tables

a) tblSaleStatementCustomer
b) tblCreditors
c) tblReceiptDue

which shows records like below

Table 1 - tblSaleStatementCustomer

ID   CustomerName     VoucherType    Outbound     Inbound     CustomerType
----------------------------------------------------------------------------------------------
1     ABC                              Sales                10000              0                   Dealer
2     MNC                             Sales                  9000              0                   Dealer
3     MNC                             Sales                  4000              0                   Dealer

Table 2 -  tblCreditors

ID   Name     OpeningBalance
----------------------------------------------------------------------------------------------
1     ABC          20000  
2     MNC         15000 
3     XBM         18000
4     XYZ          12000

View 2 Replies View Related

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID  key1  key2  key3  key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

Help With Query - Insert Multiple Rows And Link Between Tables.

Feb 27, 2007

I am trying to do the following:
Insert n rows into A Table called EAItems. For each row that is inserted into EAItems I need to take that ItemID(PK) and insert a row into EAPackageItems.
I'm inserting rows from a Table called EATemplateItems.  
So far I have something like this: (I have the PackageID already at the start of the query).
INSERT INTO EAItems(Description, Recommendation, HeadingID)SELECT Description, Recommendation, HeadingIDFROM EATemplateItems WHERE EATemplateItems.TemplateID = @TemplateID INSERT INTO EAPackageItems(ItemID, PackageID) ....
 
I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.

Any Advice / help would rock! Thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved