build sql script: insertion

Bulk Insertion of XML or Relational Data into SQL 2005

I have looked everywhere for a definitive answer to this question but I have
yet to see one. If you have XML data or Relational Data in Pure Text
Delimited Files, what is the best way to automate the process of inserting
that data into MS SQL 2005? Is the best setup to create an SPROC that
performs a BULK INSERT fired off by a trigger to look for the presence of a
file containing the XML/Relational data or is there a better way to perform
this action that DB developers embrace?
Thanks,
--
David Bowles"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:A60F8A0A-08BE-4646-B4C3-C5C8EAB42C60@.microsoft.com...
>I have looked everywhere for a definitive answer to this question but I
>have
> yet to see one. If you have XML data or Relational Data in Pure Text
> Delimited Files, what is the best way to automate the process of inserting
> that data into MS SQL 2005? Is the best setup to create an SPROC that
> performs a BULK INSERT fired off by a trigger to look for the presence of
> a
> file containing the XML/Relational data or is there a better way to
> perform
> this action that DB developers embrace?
> Thanks,
> --
> David Bowles
>
Consider using Integration Services (assuming Standard or Enterprise Ed).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||From what I've read on SSIS, it seems that it's more for utilization with
Business Intelligence and Data Mining. I have also read that when using SSI
S
the tables that data is applied to must be optimized for OLAP rather than
OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
and found from several sources that it's not good for Transactional
Processing of Bulk Data, we moved on to looking for other solutions. From
your experience with SSIS, is this not the case?
--
David Bowles
"David Portas" wrote:

> "David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
> news:A60F8A0A-08BE-4646-B4C3-C5C8EAB42C60@.microsoft.com...
> Consider using Integration Services (assuming Standard or Enterprise Ed).
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:4F29AB9B-B487-4228-A178-AF4358918DE8@.microsoft.com...
> From what I've read on SSIS, it seems that it's more for utilization with
> Business Intelligence and Data Mining.
Not really. Data Warehousing requires moving lots of data around, and
that's what SSIS does.

>I have also read that when using SSIS
> the tables that data is applied to must be optimized for OLAP rather than
> OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
> and found from several sources that it's not good for Transactional
> Processing of Bulk Data, we moved on to looking for other solutions. From
> your experience with SSIS, is this not the case?
> --
You might be confusing SSIS with SSAS. Anyway SSIS is a platform for
building, debugging, running and managing data movement processes (aka ETL).
It is a good fit for what you're trying to accomplish.
David|||David Bowles wrote:
> From what I've read on SSIS, it seems that it's more for utilization with
> Business Intelligence and Data Mining. I have also read that when using S
SIS
> the tables that data is applied to must be optimized for OLAP rather than
> OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
> and found from several sources that it's not good for Transactional
> Processing of Bulk Data, we moved on to looking for other solutions. From
> your experience with SSIS, is this not the case?
> --
> David Bowles
>
SSIS is part of the BI toolset but it's definitely not just for OLAP.
If you are familiar with DTS you'll find SSIS a very pleasant surprise
in terms of the performance it can achieve. Some good examples can be
found at:
http://blogs.conchango.com/jamiethomson/
http://www.sqlis.com/
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Bulk insertion

Hi,

I am working on an application that is to read a large number of XML files, take out specific values from each file, and store these in a SQL server so that reports can be generated from these values. There are some 15-20,000 files for each month of the year. I am OK with parsing the files and getting the fields that I need but I don't want to insert one record at a time as I parse the files. I was told that I can create a .exe file that parses the xml files and stores the required values in a csv file and use these csv files to initiate a bulk insert, using Business Intelligence Studio. I have not been able to find any info or article on how to do this. Any help on how I can accomplish this, or alternate solutions is greatly appreciated.

You can make use of SQL BulkCopy feature of ADO.NET. Load your XML into a DataSet/DataTable and run SQL Bulk copy into your table. Very few lines of code.

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
foreach (string tableName in tableNames)
{
SqlBulkCopy(dataSet.Tables[tableName], tableName, bulkCopy);
}
}

private static void SqlBulkCopy(DataTable dataTable, string tableName, SqlBulkCopy bulkCopy)
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.ColumnMappings.Clear();
foreach (DataColumn myCol in dataTable.Columns)
bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);
bulkCopy.WriteToServer(dataTable);
}

|||

Thanks for the reply Raghu. I don't need all the data elements in the XML files, only a selected few. This is an example XML file:

I only need CIF/Input and RTS/INPUT from this particular file. There will be one of this file for each day of the month and I will have to process a month's worth of xml files. There are tens of different xml files but they all follow the same schema.

Thanks.

bulk insertion

hi i have common problem.

which is the best way to insert 1000 rows at a time in sql server 2000.

From where you want to insert?

You can use the following approaches

apporach 1:

Use bcp to backup the data on text file (delimited file) - from soruce server

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Use BULK INSERT to reload the data on the target server

http://msdn2.microsoft.com/en-us/library/ms188365.aspx

approach 2:

Use DTS to load the data from one server to another server

http://msdn2.microsoft.com/en-us/library/aa902640(SQL.80).aspx

approach 3:

Use linked server to get the source data on the target server

http://msdn2.microsoft.com/en-us/library/aa213778(SQL.80).aspx

|||

If the data is in a delimited text file, use bcp; it's quick and flexible.

Read up on [bcp] in Books Online.

Tuesday, March 27, 2012

Bulk Insertion of XML or Relational Data into SQL 2005

Bulk insertion

bulk insertion

build sql script

Blog Archive

About Me