Inserting a dataset into an sql table

I've cleaned up and parsed a delimited file into a dataset.
Now I want to add that dataset to an sql table.
Simple, right?
My code is below.
It runs, but no records from the parsed file are added to the table.
Can anyone look at this code and make it work?
Thanks
Paul
PS Bulk Insert is a nice idea, and if anyone knows how to use it to insert
my dataset, I'm all ears.
========================================
=========
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
namespace Bot_01
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
private System.Data.SqlClient.SqlCommand sqlSelectCommand1;
private System.Data.SqlClient.SqlCommand sqlInsertCommand1;
private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
private System.Data.SqlClient.SqlConnection sqlConnection1;
private Bot_01.DataSet1 dataSet11;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.dataSet11 = new Bot_01.DataSet1();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(0, 0);
this.button1.Name = "button1";
this.button1.TabIndex = 0;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(88, 8);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(200, 256);
this.dataGrid1.TabIndex = 1;
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table",
"SEC_Index_01", new System.Data.Common.DataColumnMapping[] {
new
System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
new
System.Data.Common.DataColumnMapping("FormType", "FormType"),
new
System.Data.Common.DataColumnMapping("CIK", "CIK"),
new
System.Data.Common.DataColumnMapping("DateFiled", "DateFiled"),
new
System.Data.Common.DataColumnMapping("SECWebAddress", "SECWebAddress")})});
this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT ID, CompanyName, FormType,
CIK, DateFiled, SECWebAddress FROM SEC_Index_01" +
"";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
//
// sqlInsertCommand1
//
this.sqlInsertCommand1.CommandText = "INSERT INTO
SEC_Index_01(CompanyName, FormType, CIK, DateFiled, SECWebAddress) VA" +
"LUES (@.CompanyName, @.FormType, @.CIK, @.DateFiled, @.SECWebAddress);
SELECT ID, Com" +
"panyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
WHERE (ID = " +
"@.@.IDENTITY)";
this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CompanyName",
System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.FormType",
System.Data.SqlDbType.VarChar, 8000, "FormType"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
8000, "CIK"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.DateFiled",
System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
//
// sqlUpdateCommand1
//
this.sqlUpdateCommand1.CommandText = @."UPDATE SEC_Index_01 SET
CompanyName = @.CompanyName, FormType = @.FormType, CIK = @.CIK, DateFiled =
@.DateFiled, SECWebAddress = @.SECWebAddress WHERE (ID = @.Original_ID) AND (CI
K
= @.Original_CIK OR @.Original_CIK IS NULL AND CIK IS NULL) AND (CompanyName =
@.Original_CompanyName OR @.Original_CompanyName IS NULL AND CompanyName IS
NULL) AND (DateFiled = @.Original_DateFiled OR @.Original_DateFiled IS NULL AN
D
DateFiled IS NULL) AND (FormType = @.Original_FormType OR @.Original_FormType
IS NULL AND FormType IS NULL) AND (SECWebAddress = @.Original_SECWebAddress O
R
@.Original_SECWebAddress IS NULL AND SECWebAddress IS NULL); SELECT ID,
CompanyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01 WHERE
(ID = @.ID)";
this.sqlUpdateCommand1.Connection = this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CompanyName",
System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.FormType",
System.Data.SqlDbType.VarChar, 8000, "FormType"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
8000, "CIK"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.DateFiled",
System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_ID", System.Data.SqlDbType.Int
,
4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CIK",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_FormType",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.ID", System.Data.SqlDbType.Int, 4,
"ID"));
//
// sqlDeleteCommand1
//
this.sqlDeleteCommand1.CommandText = @."DELETE FROM SEC_Index_01 WHERE (ID
= @.Original_ID) AND (CIK = @.Original_CIK OR @.Original_CIK IS NULL AND CIK IS
NULL) AND (CompanyName = @.Original_CompanyName OR @.Original_CompanyName IS
NULL AND CompanyName IS NULL) AND (DateFiled = @.Original_DateFiled OR
@.Original_DateFiled IS NULL AND DateFiled IS NULL) AND (FormType =
@.Original_FormType OR @.Original_FormType IS NULL AND FormType IS NULL) AND
(SECWebAddress = @.Original_SECWebAddress OR @.Original_SECWebAddress IS NULL
AND SECWebAddress IS NULL)";
this.sqlDeleteCommand1.Connection = this.sqlConnection1;
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_ID", System.Data.SqlDbType.Int
,
4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CIK",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_FormType",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
System.Data.DataRowVersion.Original, null));
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=AMD;packet
size=4096;integrated security=SSPI;data source=AMD;pers" +
"ist security info=False;initial catalog=SEC_XBRL_10";
//
// dataSet11
//
this.dataSet11.DataSetName = "DataSet1";
this.dataSet11.Locale = new System.Globalization.CultureInfo("en-US");
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(292, 273);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.button1);
this.Name = "Form1";
this.Text = "Form1";
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void button1_Click(object sender, System.EventArgs e)
{
dataGrid1.DataSource=null; //Clear the
datagrid
sqlDataAdapter1.Fill(dataSet11,"SEC_Index_01"); //Fill result dataset
with SEC_Index_01 table data via the sqlDataAdapter1
//WebRequest req =
WebRequest.Create("http://www.sec.gov/Archives/edgar/daily-index/" +
"company." + "20041222" + ".idx"); // Create the Request object
//WebResponse response = req.GetResponse(); //Create the
Response object
//Stream stream = response.GetResponseStream(); //Create a
Stream
//StreamReader sr = new StreamReader(stream); //Open the file
in a stream reader
StreamReader sr = new StreamReader("C:\\test.txt"); //Read From
A File instead of a webrequest
dataSet11.Tables.Add("MyNewTable"); //Add DataTable to
hold the DataSet
dataSet11.Tables["MyNewTable"].Columns.Add("CompanyName"); //Add a
single column to the DataTable
dataSet11.Tables["MyNewTable"].Columns.Add("FormType"); //Add a
single column
dataSet11.Tables["MyNewTable"].Columns.Add("CIK"); //Add a
single column
dataSet11.Tables["MyNewTable"].Columns.Add("DateFiled"); //Add a
single column
dataSet11.Tables["MyNewTable"].Columns.Add("SECWebAddress"); //Add a
single column
string AllData1 = sr.ReadToEnd(); //Read the rest
of the data in the file.
string[] rows = AllData1.Split("\n".ToCharArray()); //Split off
each row at the Line Feed
foreach(string r in rows) //Now add each
row to the DataSet
{
string delimStr1 = "\t";
string[] items = r.Split(delimStr1.ToCharArray()); //Split the row
at the delimiter
dataSet11.Tables["MyNewTable"].Rows.Add(items); //Add the item
}
for (int i = 1; i <= 11; i++) //Remove first 11
rows from the DataTable/DataSet
{
dataSet11.Tables["MyNewTable"].Rows.RemoveAt(0);
}
sqlDataAdapter1.InsertCommand = sqlInsertCommand1; //Insert the new
rows from the "result" dataset
//sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
update
dataGrid1.SetDataBinding(dataSet11, "MyNewTable"); //Binds DataGrid to
DataSet,displaying datatable.
MessageBox.Show("Data has been added to the SQL Table SEC_XBRL_Index");
}
}
}Here's the reply to your earlier post...
A quick browse through your code, you have this line:
sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
update
which sends the updates (i.e. insert, update, delete operation) to sqlserver
for SEC_Index_01 table.
You have to addrow to dataSet11.Tables["SEC_Index_01"] if you want the
inserts!
The gist of it is you would want to add this to your code before calling
da.update.
e.g.
foreach(DataRow dr in dataSet11.Tables["MyNewTable"].Rows)
{
DataRow r = dataSet11.Tables["SEC_Index_01"].NewRow();
r["CompanyName"] = dr["CompanyName"];
// ... do the same for the rest of your columns
dataSet11.Tables["SEC_Index_01"].Rows.Add(r);
}
-oj
"a" <a@.discussions.microsoft.com> wrote in message
news:35A6293F-CA8B-415E-9DEE-36ECD43B5F3D@.microsoft.com...
> I've cleaned up and parsed a delimited file into a dataset.
> Now I want to add that dataset to an sql table.
> Simple, right?
> My code is below.
> It runs, but no records from the parsed file are added to the table.
> Can anyone look at this code and make it work?
> Thanks
> Paul
> PS Bulk Insert is a nice idea, and if anyone knows how to use it to insert
> my dataset, I'm all ears.
> ========================================
=========
> using System;
> using System.Drawing;
> using System.Collections;
> using System.ComponentModel;
> using System.Windows.Forms;
> using System.Data;
> using System.Data.SqlClient;
> using System.Net;
> using System.IO;
> using System.Text;
> using System.Text.RegularExpressions;
> using System.Collections.Specialized;
> namespace Bot_01
> {
> /// <summary>
> /// Summary description for Form1.
> /// </summary>
> public class Form1 : System.Windows.Forms.Form
> {
> private System.Windows.Forms.Button button1;
> private System.Windows.Forms.DataGrid dataGrid1;
> private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
> private System.Data.SqlClient.SqlCommand sqlSelectCommand1;
> private System.Data.SqlClient.SqlCommand sqlInsertCommand1;
> private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
> private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
> private System.Data.SqlClient.SqlConnection sqlConnection1;
> private Bot_01.DataSet1 dataSet11;
> /// <summary>
> /// Required designer variable.
> /// </summary>
> private System.ComponentModel.Container components = null;
> public Form1()
> {
> //
> // Required for Windows Form Designer support
> //
> InitializeComponent();
> //
> // TODO: Add any constructor code after InitializeComponent call
> //
> }
> /// <summary>
> /// Clean up any resources being used.
> /// </summary>
> protected override void Dispose( bool disposing )
> {
> if( disposing )
> {
> if (components != null)
> {
> components.Dispose();
> }
> }
> base.Dispose( disposing );
> }
> #region Windows Form Designer generated code
> /// <summary>
> /// Required method for Designer support - do not modify
> /// the contents of this method with the code editor.
> /// </summary>
> private void InitializeComponent()
> {
> this.button1 = new System.Windows.Forms.Button();
> this.dataGrid1 = new System.Windows.Forms.DataGrid();
> this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
> this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
> this.dataSet11 = new Bot_01.DataSet1();
> ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
> ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
> this.SuspendLayout();
> //
> // button1
> //
> this.button1.Location = new System.Drawing.Point(0, 0);
> this.button1.Name = "button1";
> this.button1.TabIndex = 0;
> this.button1.Text = "button1";
> this.button1.Click += new System.EventHandler(this.button1_Click);
> //
> // dataGrid1
> //
> this.dataGrid1.DataMember = "";
> this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
> this.dataGrid1.Location = new System.Drawing.Point(88, 8);
> this.dataGrid1.Name = "dataGrid1";
> this.dataGrid1.Size = new System.Drawing.Size(200, 256);
> this.dataGrid1.TabIndex = 1;
> //
> // sqlDataAdapter1
> //
> this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
> this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
> this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
> this.sqlDataAdapter1.TableMappings.AddRange(new
> System.Data.Common.DataTableMapping[] {
> new System.Data.Common.DataTableMapping("Table",
> "SEC_Index_01", new System.Data.Common.DataColumnMapping[] {
> new
> System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
> new
> System.Data.Common.DataColumnMapping("FormType", "FormType"),
> new
> System.Data.Common.DataColumnMapping("CIK", "CIK"),
> new
> System.Data.Common.DataColumnMapping("DateFiled", "DateFiled"),
> new
> System.Data.Common.DataColumnMapping("SECWebAddress",
> "SECWebAddress")})});
> this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
> //
> // sqlSelectCommand1
> //
> this.sqlSelectCommand1.CommandText = "SELECT ID, CompanyName, FormType,
> CIK, DateFiled, SECWebAddress FROM SEC_Index_01" +
> "";
> this.sqlSelectCommand1.Connection = this.sqlConnection1;
> //
> // sqlInsertCommand1
> //
> this.sqlInsertCommand1.CommandText = "INSERT INTO
> SEC_Index_01(CompanyName, FormType, CIK, DateFiled, SECWebAddress) VA" +
> "LUES (@.CompanyName, @.FormType, @.CIK, @.DateFiled, @.SECWebAddress);
> SELECT ID, Com" +
> "panyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
> WHERE (ID = " +
> "@.@.IDENTITY)";
> this.sqlInsertCommand1.Connection = this.sqlConnection1;
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CompanyName",
> System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.FormType",
> System.Data.SqlDbType.VarChar, 8000, "FormType"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
> 8000, "CIK"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.DateFiled",
> System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
> //
> // sqlUpdateCommand1
> //
> this.sqlUpdateCommand1.CommandText = @."UPDATE SEC_Index_01 SET
> CompanyName = @.CompanyName, FormType = @.FormType, CIK = @.CIK, DateFiled =
> @.DateFiled, SECWebAddress = @.SECWebAddress WHERE (ID = @.Original_ID) AND
> (CIK
> = @.Original_CIK OR @.Original_CIK IS NULL AND CIK IS NULL) AND (CompanyName
> =
> @.Original_CompanyName OR @.Original_CompanyName IS NULL AND CompanyName IS
> NULL) AND (DateFiled = @.Original_DateFiled OR @.Original_DateFiled IS NULL
> AND
> DateFiled IS NULL) AND (FormType = @.Original_FormType OR
> @.Original_FormType
> IS NULL AND FormType IS NULL) AND (SECWebAddress = @.Original_SECWebAddress
> OR
> @.Original_SECWebAddress IS NULL AND SECWebAddress IS NULL); SELECT ID,
> CompanyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
> WHERE
> (ID = @.ID)";
> this.sqlUpdateCommand1.Connection = this.sqlConnection1;
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CompanyName",
> System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.FormType",
> System.Data.SqlDbType.VarChar, 8000, "FormType"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
> 8000, "CIK"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.DateFiled",
> System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_ID",
> System.Data.SqlDbType.Int,
> 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
> ((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CIK",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_FormType",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.ID", System.Data.SqlDbType.Int, 4,
> "ID"));
> //
> // sqlDeleteCommand1
> //
> this.sqlDeleteCommand1.CommandText = @."DELETE FROM SEC_Index_01 WHERE (ID
> = @.Original_ID) AND (CIK = @.Original_CIK OR @.Original_CIK IS NULL AND CIK
> IS
> NULL) AND (CompanyName = @.Original_CompanyName OR @.Original_CompanyName IS
> NULL AND CompanyName IS NULL) AND (DateFiled = @.Original_DateFiled OR
> @.Original_DateFiled IS NULL AND DateFiled IS NULL) AND (FormType =
> @.Original_FormType OR @.Original_FormType IS NULL AND FormType IS NULL) AND
> (SECWebAddress = @.Original_SECWebAddress OR @.Original_SECWebAddress IS
> NULL
> AND SECWebAddress IS NULL)";
> this.sqlDeleteCommand1.Connection = this.sqlConnection1;
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_ID",
> System.Data.SqlDbType.Int,
> 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
> ((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CIK",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_FormType",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
> System.Data.DataRowVersion.Original, null));
> //
> // sqlConnection1
> //
> this.sqlConnection1.ConnectionString = "workstation id=AMD;packet
> size=4096;integrated security=SSPI;data source=AMD;pers" +
> "ist security info=False;initial catalog=SEC_XBRL_10";
> //
> // dataSet11
> //
> this.dataSet11.DataSetName = "DataSet1";
> this.dataSet11.Locale = new System.Globalization.CultureInfo("en-US");
> //
> // Form1
> //
> this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
> this.ClientSize = new System.Drawing.Size(292, 273);
> this.Controls.Add(this.dataGrid1);
> this.Controls.Add(this.button1);
> this.Name = "Form1";
> this.Text = "Form1";
> ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
> ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
> this.ResumeLayout(false);
> }
> #endregion
> /// <summary>
> /// The main entry point for the application.
> /// </summary>
> [STAThread]
> static void Main()
> {
> Application.Run(new Form1());
> }
> private void button1_Click(object sender, System.EventArgs e)
> {
> dataGrid1.DataSource=null; //Clear the
> datagrid
> sqlDataAdapter1.Fill(dataSet11,"SEC_Index_01"); //Fill result dataset
> with SEC_Index_01 table data via the sqlDataAdapter1
> //WebRequest req =
> WebRequest.Create("http://www.sec.gov/Archives/edgar/daily-index/" +
> "company." + "20041222" + ".idx"); // Create the Request object
> //WebResponse response = req.GetResponse(); //Create the
> Response object
> //Stream stream = response.GetResponseStream(); //Create a
> Stream
> //StreamReader sr = new StreamReader(stream); //Open the file
> in a stream reader
> StreamReader sr = new StreamReader("C:\\test.txt"); //Read From
> A File instead of a webrequest
> dataSet11.Tables.Add("MyNewTable"); //Add DataTable to
> hold the DataSet
> dataSet11.Tables["MyNewTable"].Columns.Add("CompanyName"); //Add a
> single column to the DataTable
> dataSet11.Tables["MyNewTable"].Columns.Add("FormType"); //Add a
> single column
> dataSet11.Tables["MyNewTable"].Columns.Add("CIK"); //Add a
> single column
> dataSet11.Tables["MyNewTable"].Columns.Add("DateFiled"); //Add a
> single column
> dataSet11.Tables["MyNewTable"].Columns.Add("SECWebAddress"); //Add a
> single column
> string AllData1 = sr.ReadToEnd(); //Read the rest
> of the data in the file.
> string[] rows = AllData1.Split("\n".ToCharArray()); //Split off
> each row at the Line Feed
> foreach(string r in rows) //Now add each
> row to the DataSet
> {
> string delimStr1 = "\t";
> string[] items = r.Split(delimStr1.ToCharArray()); //Split the row
> at the delimiter
> dataSet11.Tables["MyNewTable"].Rows.Add(items); //Add the item
> }
> for (int i = 1; i <= 11; i++) //Remove first 11
> rows from the DataTable/DataSet
> {
> dataSet11.Tables["MyNewTable"].Rows.RemoveAt(0);
> }
> sqlDataAdapter1.InsertCommand = sqlInsertCommand1; //Insert the new
> rows from the "result" dataset
> //sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
> update
> dataGrid1.SetDataBinding(dataSet11, "MyNewTable"); //Binds DataGrid to
> DataSet,displaying datatable.
> MessageBox.Show("Data has been added to the SQL Table SEC_XBRL_Index");
> }
> }
> }|||oj:
Thank You...your answer solved my problem. I'd buy you a drink if I could.
I suppose in retrospect that the answer is somewhat obvious, but not being
all that familiar with what code "I" needed to add to make the data adapters
work, it wasn't obvious to me at all.
Thanks,
Paul
========================================
=======
"oj" wrote:

> Here's the reply to your earlier post...
> A quick browse through your code, you have this line:
> sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
> update
> which sends the updates (i.e. insert, update, delete operation) to sqlserv
er
> for SEC_Index_01 table.
> You have to addrow to dataSet11.Tables["SEC_Index_01"] if you want the
> inserts!
> The gist of it is you would want to add this to your code before calling
> da.update.
> e.g.
> foreach(DataRow dr in dataSet11.Tables["MyNewTable"].Rows)
> {
> DataRow r = dataSet11.Tables["SEC_Index_01"].NewRow();
> r["CompanyName"] = dr["CompanyName"];
> // ... do the same for the rest of your columns
> dataSet11.Tables["SEC_Index_01"].Rows.Add(r);
> }
> --
> -oj|||You're welcome.
A virtual drink is welcome also. ;-)
-oj
"a" <a@.discussions.microsoft.com> wrote in message
news:82ACC91A-6558-4FBC-A06A-486477197C42@.microsoft.com...
> oj:
> Thank You...your answer solved my problem. I'd buy you a drink if I could.
> I suppose in retrospect that the answer is somewhat obvious, but not being
> all that familiar with what code "I" needed to add to make the data
> adapters
> work, it wasn't obvious to me at all.
> Thanks,
> Paul
> ========================================
=======
> "oj" wrote:
>
>

Inserting a Control Record into a Flat Text File through SSIS

I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.

What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?

Thanks.

Yes, I would guess that you can do exactly this using the header section.

You can set it dynamically by putting an expression on the [<Flat File Destination Name>].[Header] property of the parent data-flow task.

-Jamie

|||

Ok, I see that this can work, but looking at the available variables, functions for expressions, I do not see how I would get the data inserted from another text file (table) already created into this second one.

Truly not trying to be dense here, just "can't seem to see the forest for the trees."

Thanks.

|||

That's a bit of a different requirement. You may be hampered by the fact that the maximum length of the result of an expression can only be 4000 chars

The way to do it would probably be to build the text up programatically in a script task.

-Jamie

sql

Insert/ Update/ Delete slowness.

SQL2K sp4

Howdy all. I opened a 200 mb. file in Query Analyzer that is full of Inserts/ Updates/ and Deletes. I tried just to parse it, and killed it after 18 hours. There is no blocking. All of the appropriate indexes exist. I even removed them and retried JIC. The box is plenty powerful for this task. Does anyone have any ideas?
I've tried several times with no luck. At the top of the file is SET IMPLICIT_TRANSACTIONS ON and then every 10,000 statements is COMMIT WORK. I've tried adjusting the number of commits to a lower number with no luck. This works fine on smaller files (3 - 20 mb).Can you give a sample of the statements this script is running?

Insert xml into mssql 2000

Hi,

I have app with file selection field. Users can shoose xml file from their local hard disk and click Save. When they click save, content of xml file should be inserted into mssql 2000.

What is the most efficient way to do this using C#2005 and mssql2000?

Any good article or tutorial?

http://www.databasejournal.com/features/mssql/article.php/2204421|||http://aspalliance.com/477

Insert word file in SQL Server

Hi,
I try to save word files in SQL Server.
Can I do this?, How?
Thanks,
Try:
http://www.windowsitpro.com/SQLServe...903/37903.html
HTH
Jerry
"Office ortografa xp" <Officeortografaxp@.discussions.microsoft.com> wrote
in message news:697106EA-805F-49E4-924C-B195E462DD9C@.microsoft.com...
> Hi,
> I try to save word files in SQL Server.
> Can I do this?, How?
> Thanks,
>

Insert word file in SQL Server

Hi,
I try to save word files in SQL Server.
Can I do this?, How?
Thanks,Try:
http://www.windowsitpro.com/SQLServer/Article/ArticleID/37903/37903.html
HTH
Jerry
"Office ortografía xp" <Officeortografaxp@.discussions.microsoft.com> wrote
in message news:697106EA-805F-49E4-924C-B195E462DD9C@.microsoft.com...
> Hi,
> I try to save word files in SQL Server.
> Can I do this?, How?
> Thanks,
>

Insert word file in SQL Server

Hi,
I try to save word files in SQL Server.
Can I do this?, How?
Thanks,Try:
http://www.windowsitpro.com/SQLServ...7903/37903.html
HTH
Jerry
"Office ortografa xp" <Officeortografaxp@.discussions.microsoft.com> wrote
in message news:697106EA-805F-49E4-924C-B195E462DD9C@.microsoft.com...
> Hi,
> I try to save word files in SQL Server.
> Can I do this?, How?
> Thanks,
>sql

insert with exec sql server 2005

Hi,
This is a query that inserts the xmlcontents of a file into the table.

insert into
tbTrades
(
xmlContents
)
Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.FilePath + ''', SINGLE_CLOB) as D')

Now I would like to add an extra field in the insert. something like:

declare @.FileName varchar(200)

set @.FileName = 'c:\1234.xml'

insert into
tbTrades
(
FileName,
xmlContents
)
@.FileName,
Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.FilePath + ''', SINGLE_CLOB) as D')

This gives an error:
Incorrect syntax near '@.FileName'.

p.s. I am happy with the first query, just would like to get the second one to work too.
Thanks

how about:

Code Snippet

insert into

tbTrades

(

FileName,

xmlContents

)

Exec ('SELECT ''' + @.FileName + ''', Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.FilePath + ''', SINGLE_CLOB) as D')

Insert values from file

Just in general terms (I will look up the specifics), how would I go
about doing this?
There is value.csv list containing values (000, 001, 002, 003, etc . .
.). There is a table named Attrib. Each value in value.csv needs an
Insert Into Attrib.
Basically I want to perform the following:
Insert into Attrib
values ('A', 'B', 'C', '000')
Insert into Attrib
values ('A', 'B', 'C', '001')
Insert into Attrib
values ('A', 'B', 'C', '002')
With something like 2000 values in value.csv, I don't know the smartest
way to go about this.
Thanks
-tomTom,
Using DTS (Data Transformation Services) would be the easiest way to do
this. DTS is covered in the SQL Books Online and additional information can
be found at:
http://www.sqldts.com/
HTH
Jerry
"tom" <tomfeldsten@.hotmail.com> wrote in message
news:1129133965.223074.121090@.z14g2000cwz.googlegroups.com...
> Just in general terms (I will look up the specifics), how would I go
> about doing this?
> There is value.csv list containing values (000, 001, 002, 003, etc . .
> .). There is a table named Attrib. Each value in value.csv needs an
> Insert Into Attrib.
> Basically I want to perform the following:
> Insert into Attrib
> values ('A', 'B', 'C', '000')
> Insert into Attrib
> values ('A', 'B', 'C', '001')
> Insert into Attrib
> values ('A', 'B', 'C', '002')
> With something like 2000 values in value.csv, I don't know the smartest
> way to go about this.
> Thanks
> -tom
>|||Look at:
http://www.databasejournal.com/feat...cle.php/3331881
There are samples for opening this in a query.
Then you should go with:
INSERT INTO SomeTable
SELECT <columnlist> from <Openrowsetquery>
HTH, Jens Suessmeyer.|||Thanks to the both of you. Good hints for the direction I need to go.
-tom|||And a third one: "sp_addlinkedserver" with a little help from "schema.ini".
Look up both in Books Online.
ML

Insert trigger

Hi

I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
Turnover, VAT, Netturnover). I get a file which I have to import every
know and then, with new data. In this file I only get values for (ID,
Date, Turnover and VAT). The import is working fine with the import
wizard.

The problem is, that I want to have the Netturnover computed at the
time of insert to equal [Turnover-VAT], but I don't really know how to
as I'm new to these triggers.

Could anyone help me I would appriciate this.
BR / Janjazpar (jannoergaard@.hotmail.com) writes:
> I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
> Turnover, VAT, Netturnover). I get a file which I have to import every
> know and then, with new data. In this file I only get values for (ID,
> Date, Turnover and VAT). The import is working fine with the import
> wizard.
> The problem is, that I want to have the Netturnover computed at the
> time of insert to equal [Turnover-VAT], but I don't really know how to
> as I'm new to these triggers.

The simplest is to make NetTurnover a computed column:

CREATE TABLE DebtorTurnover
(ID int NOT NULL,
Date datetime NOT NULL,
Turnover decimal(10,2) NOT NULL,
VAT decimal(10, 2) NOT NULL,
Netturnover AS Turnover - VAT)

A trigger would look like this:

CREATE TRIGGER DebtorTurnover_tri ON DebtorTurnover
FOR INSERT, UPDATE AS
UPDATE DebtorTurnover
SET Netturnover = dt.Turnover - dt.VAT
FROM DebtorTurnover dt
WHERE EXISTS (SELECT *
FROM inserted dt
WHERE dt.ID = i.ID

The "inserted" table is a virtual table that holds the inserted rows,
or in case of an UPDATE, the update rows after the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog skrev:

> jazpar (jannoergaard@.hotmail.com) writes:
> > I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
> > Turnover, VAT, Netturnover). I get a file which I have to import every
> > know and then, with new data. In this file I only get values for (ID,
> > Date, Turnover and VAT). The import is working fine with the import
> > wizard.
> > The problem is, that I want to have the Netturnover computed at the
> > time of insert to equal [Turnover-VAT], but I don't really know how to
> > as I'm new to these triggers.
> The simplest is to make NetTurnover a computed column:
> CREATE TABLE DebtorTurnover
> (ID int NOT NULL,
> Date datetime NOT NULL,
> Turnover decimal(10,2) NOT NULL,
> VAT decimal(10, 2) NOT NULL,
> Netturnover AS Turnover - VAT)
> A trigger would look like this:
> CREATE TRIGGER DebtorTurnover_tri ON DebtorTurnover
> FOR INSERT, UPDATE AS
> UPDATE DebtorTurnover
> SET Netturnover = dt.Turnover - dt.VAT
> FROM DebtorTurnover dt
> WHERE EXISTS (SELECT *
> FROM inserted dt
> WHERE dt.ID = i.ID
> The "inserted" table is a virtual table that holds the inserted rows,
> or in case of an UPDATE, the update rows after the table.
Hi Thanks for you reply

I made the following

Table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DepTurnOver]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[DepTurnOver]
GO

CREATE TABLE [dbo].[DepTurnOver] (
[Year] [int] NULL ,
[Week] [int] NULL ,
[CalleId] [int] NULL ,
[ShopId] [int] NULL ,
[ItemGroupId] [int] NULL ,
[TurnOver] [real] NULL ,
[Discount] [real] NULL ,
[Qty] [real] NULL ,
[Customer] [int] NULL ,
[VAT] [real] NULL ,
[Consumption] [real] NULL,
[Netturnover] AS [Turnover]-[VAT]
) ON [PRIMARY]
GO

Trigger:
CREATE TRIGGER DepTurnover_tri ON DepTurnover
FOR INSERT, UPDATE AS
UPDATE DepTurnover
SET Netturnover = idt.Turnover - idt.VAT
FROM DepTurnover idt
WHERE EXISTS (SELECT *
FROM inserted dt
WHERE dt.Year = idt.Year
AND dt.Week = idt.week
AND dt.CalleId = idt.CalleId
AND dt.ShopId = idt.ShopId
AND dt.ItemGroupId = idt.ItemGroupId)

But when I try to save the trigger I get the following error:
Server: Msg 271, Level 16, State 1, Procedure DepTurnover_tri, Line 3
Column 'Netturnover' cannot be modified because it is a computed
column.

Have I done anything wrong here.

Thanks in advance
BR/ Jan

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||jazpar (jannoergaard@.hotmail.com) writes:
> I made the following
> Table:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DepTurnOver]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[DepTurnOver]
> GO
> CREATE TABLE [dbo].[DepTurnOver] (
> [Year] [int] NULL ,
> [Week] [int] NULL ,
> [CalleId] [int] NULL ,
> [ShopId] [int] NULL ,
> [ItemGroupId] [int] NULL ,
> [TurnOver] [real] NULL ,
> [Discount] [real] NULL ,
> [Qty] [real] NULL ,
> [Customer] [int] NULL ,
> [VAT] [real] NULL ,
> [Consumption] [real] NULL,
> [Netturnover] AS [Turnover]-[VAT]
> ) ON [PRIMARY]
> GO
> Trigger:

Sorry, I was a bit brief. If you have a computed column, you don't
need the trigger at all. I included the trigger code, in case you
were not in position to change the table definition.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Insert text file content into database

Hello everyone!

I'm having a problem with inserting the content of a text file into a Sql Server 2005 database.

I'm reading the text file into a dataset, and works fine. What I can't do is what I suspect is the simple part: Insert all the data into a table that has exactly the same configuration that the file. I've never worked with dataset's before, and I can't seem to find the answer to this!

This is what I have done so far:

Dim i2As Integer
 Dim jAs Integer
 Dim FileAs String = Server.MapPath("..\Docs\Facts\FORM_MAN_V3_1.txt")
Dim TableNameAs String ="Facts"Dim delimiterAs String ="9"Dim resultAs DataSet =New DataSet()
Dim sAs StreamReader =New StreamReader(File)
Dim columnsAs String() = s.ReadLine().Split(Chr(9))
 result.Tables.Add(TableName)
Dim strs1As String() = columns
For i2 = 0To CInt(strs1.Length) - 1
Dim colAs String = strs1(i2)
Dim addedAs Boolean =False
 Dim [next]As String =""Dim iAs Integer = 0
While Not added
Dim columnnameAs String =String.Concat(col, [next])
 columnname = columnname.Replace(Chr(9),"")
If Not result.Tables(TableName).Columns.Contains(columnname)Then result.Tables(TableName).Columns.Add(columnname) added =True
 Else i += 1 [next] =String.Concat("_", i.ToString())
End If
 End While
 Next i2
Dim strs2As String() = s.ReadToEnd().Split(Chr(13) & Chr(10).ToString())
For j = 0To CInt(strs2.Length) - 1
Dim itemsAs String() = strs2(j).Split(Chr(9))
 result.Tables(TableName).Rows.Add(items)
Next j

So now I have my dataset populated with all the information, but how can I insert it into the database?

If anyone can help I would appreciate very, very much!

Thank you

Paula

Hi There.

I think you should learn a bit about ado.net's table adapters. They can be created automatically with the dataset designer, and they can be used to select/insert/update/delete data between your dataset and its corresponding database.

Take a look at the howto's at the bottom of this article:

http://msdn2.microsoft.com/en-us/library/tkctt675(VS.80).aspx

Insert rows into a Flat File

Hi!

I have a problem that I can′t understand.

I have rows in a table in a SQL Server 2005 that I want to send to a Flat file. When I am executing the task for the first time it is OK and works perfectly. But the problem is when I am trying to do it again. I seems like the path to the file and the filename dissappears after the first time and the execution failes with an error message that tells me that the file is invalid and don′t exists.

I don′t now why this is happening and it′s such an easy thing to do and I can′t make it work.

So please help me!

Regards,

Johanna

I have no idea from what you descripted

I think you should double check your setting or programming

|||

Johanna Maria wrote:

Hi!

I have a problem that I can′t understand.

I have rows in a table in a SQL Server 2005 that I want to send to a Flat file. When I am executing the task for the first time it is OK and works perfectly. But the problem is when I am trying to do it again. I seems like the path to the file and the filename dissappears after the first time and the execution failes with an error message that tells me that the file is invalid and don′t exists.

I don′t now why this is happening and it′s such an easy thing to do and I can′t make it work.

So please help me!

Regards,

Johanna

Can you post the full error message?

Does this happen at design-time or execution-time?

-Jamie

INSERT Records in multiple tables

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!
NNo, that is not the purpose of views. Views are frequently not updateable, and I don't think it is ever possible to update different columns from different tables in the same view. Even a direct SQL Update statement will only update one table at a time, so you will need to issues separate update statements or handle the problem through triggers or cascading updates.

Truth is, views don't serve much purpose any more.

Good database application design principles dictate making all your updates through stored procedures. Your application should rarely if ever have direct access to the database tables, even for retrieving data.|||No, you can update the columns of each of the base table independantly (one or more UPDATE statements per base table), but you can't update multiple base tables in a single pass.

Thinking outside of the SQL box, a table represents a relational algebra entity. An entity has no inherant order for either columns or rows, they behave something like a hash in that respect.

Views represent a relational algebra result. A result can have order, there can be a first, middle, and last for both rows and columns in a view.

-PatP|||Views (with multiple base tables) can be updated at one shot by using INSTEAD OF trigger

Here is some supporting article from MSDN

Cheers

Benny
-----------------------

Modifying Data Through a View
You can modify data through a view in these ways:

Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.

Use updatable partitioned views that modify one or more member tables.
If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:

The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.

No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. However, aggregate functions can be used within a subquery defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified.

Note Partitioned views using the UNION ALL operator can be updatable.

No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference.
Guidelines for Modifying Data Through a View
Before you modify data through a view without using an INSTEAD OF trigger or an updatable partitioned view, consider these guidelines:

All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.

SQL Server must be able to resolve unambiguously the modification operation to specific rows in one of the base tables referenced by the view. You cannot use data modification statements on more than one underlying table in a single statement. Therefore, the columns listed in the UPDATE or INSERT statement must belong to a single base table within the view definition.

All the columns in the underlying table that are being updated and do not allow null values have values specified in either the INSERT statement or DEFAULT definitions. This ensures that all the columns in the underlying table that require values have them.

The data modified in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions and so on. For example, if a row is deleted, all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.

A distributed partition view (remote view) cannot be updated using a keyset-driven cursor. This restriction can be resolved by declaring the cursor on the underlying tables and not on the view itself.
Additionally, to delete data in a view:

Only one table can be listed in the FROM clause of the view definition.|||Originally posted by blindman
Truth is, views don't serve much purpose any more.

what??!!

maybe not for use by the DBA, but for use by end users in a reporting environment, views are invaluable

"much purpose any more"?

what do you suppose the purpose of a view used to be then, before it got to where this purpose was diluted?

okay, here's an example

say a table is called Accounts and say it contains a column called LedgerCode and say the column values range from A to E, and now you have to change the table so that instead of values A to E, the LedgerCode becomes a numeric tinyint foreign key to a Ledger table with values A through Z

the mere fact that you can have a view with a join in it eliminates the need for the end user to figure out how to write a join

rename the table, change the table, declare a view called Accounts, build the join into the view, and voila, all existing code that used to select from the Accounts table still works

a long time ago i used to work in a shop where end users never got to use base tables, they were always given just views, and i can definitely see the logic behind that

it's called program-data independence|||Let me rephrase that...

Truth is, views don't serve much purpose any more, "IMHO".

I used to use views a lot too, specifically for program-data independence. Now, at least for application development, I always use Sprocs or UDFs.|||cool :cool:|||I believe you mean...

"Cool, IMHO." ;)|||indeed

burying application code inside sporcs and fuds is not cool to everybody, i admit -- especially those folks who would like to see a clear separation of application logic from proprietary database languages

usually i insist on declarative relational integrity but otherwise force application logic outside the database

you know, like so that your app is not dependent on any particular dbms

but sporcs and fuds are reasonably coolish, in my opinion, yeah|||Okey-dokey. I usually take the exact opposite approach, putting as much application logic into the RDBMS as possible, so that the application is not dependent on any particular interface. The reasoning is that these days people frequently want to access their data through different channels, such as a VB application, a Crystal Report, Access ADP project, Dot-Net, etc. By putting the application logic in the database you ensure consistent input and output and you avoid duplicating code. Let the the interface do what it does best: display the data and guide the user through it.

I guess the decision depends upon whether it is more likely that your application will need to be ported to a different RDBMS, or that users will come up with new requirements for accessing it. Perhaps I prefer the latter because the former results in boring "file cabinet" databases which frequently lack any sort of referential integrity. They just aren't as much fun or rewarding to work with as a database which is practically an application in itself.

INSERT RECORD with PDF WOED files

Hi guys!

I've made a simple INSERT form write some records in a database...

then I need to associate to every record a PDF FILE or a WORD FILE..

so who (a user) insert a record should upload a file ...

How could associate the record to the file that an user upload?

classical article pubblication problem...do you know some tutorial?

3rdEyed

Hi 3rdEyed,

There are 2 ways that come to my mind in doing this.

1. Add a VarChar field in your table which stores the path and file name of the PDF or DOC file. You can later get this file from the file system and do whatever you like.

2. Use a FileStream to get file in a byte array. You can store the byte array in a binary field in database table.

I will recommend the first way, since it will not give much overhead to database.

|||

Kevin Yu - MSFT:

Hi 3rdEyed,

There are 2 ways that come to my mind in doing this.

1. Add a VarChar field in your table which stores the path and file name of the PDF or DOC file. You can later get this file from the file system and do whatever you like.

2. Use a FileStream to get file in a byte array. You can store the byte array in a binary field in database table.

I will recommend the first way, since it will not give much overhead to database.

Hi thanks for the answer..did you some example SCRIPT or TUTORIAL?

Insert or Update using DTS SERVER 2005

I am new to using DTS so forgive the newbie post.
I have CSV file that has records that lookup a value from a table in a
database and then write the record to the table with the lookup value.
This works for insert.
What I need is a way to check and see if the record already exists and
if so update.
It looks like a EXECUTE SQL transaction will do this but then how do
intercede the write to the actual table? I would like to use DTS.Hi
If you are using SQL 2005 you should be writing new packages in SSIS
(Integration Services). DTS was with SQL 2000. One way of doing this which
can be implemented in DTS and SSIS is to load the file into a staging table
and then update the existing data using the values in the staging table
(using a SQL Command Task) and then inserting rows from the staging table
into the live table that don't currently exist in the live table (using a SQL
Command Task).
This may give you some idea! http://www.sqlis.com/311.aspx
John
"axwack" wrote:
> I am new to using DTS so forgive the newbie post.
> I have CSV file that has records that lookup a value from a table in a
> database and then write the record to the table with the lookup value.
> This works for insert.
> What I need is a way to check and see if the record already exists and
> if so update.
> It looks like a EXECUTE SQL transaction will do this but then how do
> intercede the write to the actual table? I would like to use DTS.
>

Insert or update into a Image column causes data to double in size

We are experiencing problems inserting or updating image fields from
one table to another in SQL Server.

When we do this what ever size of file we insert is doubled in size
when it is inserted into the destination table.

This happens in insert and update queries, and if we use DTS.

Any help would be greatly appreciatedskyhawker9 (andyh@.bmtdsl.co.uk) writes:
> We are experiencing problems inserting or updating image fields from
> one table to another in SQL Server.
> When we do this what ever size of file we insert is doubled in size
> when it is inserted into the destination table.
> This happens in insert and update queries, and if we use DTS.
> Any help would be greatly appreciated

I'm afraid that it's difficult to help, since you don't disclose very
many details. Could you provide the code you use to copy the fields?

What is the typical size of the data you copy?

What you mean by "file ... is doubled in size"? What file? The database
file? But then you talk about inserting the file? How do you know
that it doubles in size?

Sorry for asking so many questions, but while you know very well what is
going on at your site, I know next to nothing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||We are storing documents such as PDFs, word documents etc in a object
field in a table. When we transfer the the contents from one table to
another using even a simple

INSERT INTO tblTest (ObjectField)
SELECT ObjectField
FROM tblTest2

The data in the destination is exactly doubled in size when you load
the document again ie when you save a 1Mb document into the table
transfer it to the second table it becomes 2Mb.

Hope this makes it more clear.|||skyhawker9 (andyh@.bmtdsl.co.uk) writes:
> We are storing documents such as PDFs, word documents etc in a object
> field in a table. When we transfer the the contents from one table to
> another using even a simple
> INSERT INTO tblTest (ObjectField)
> SELECT ObjectField
> FROM tblTest2
> The data in the destination is exactly doubled in size when you load
> the document again ie when you save a 1Mb document into the table
> transfer it to the second table it becomes 2Mb.

How do you deduce that?

It would help if you could provide a complete script that demonstrates
the problem. That would be a script that creates the table, populates
the first, and the loads the data into other, and then somehow gets
the size of the documents. I realise that including a 1MB object in
a script is not realistic, but the table pub..pub_info includes an
image column. Maybe you can build a repro script from that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

insert or update data records

I'd like to do the following thing with a data flow task

Get all the records from a source (for example customers from a textfile, flat file source) Then check for each record if the customer already exists in a table, for example with a customerID. If not, insert the record in the table (ole db destination), else copy the customer thats already in the table to another table (history table) and update the record with the customer from the textfile.

Is this possible?, and what kind of data flow transformation do I need?

Use a lookup to check if the customer exists. Use error output from the lookup to insert new customers, OLE-DB Destination. Use the normal output from the lookup to feed an OLE-DB Command to do the move and update. That may be easier if wrapped in a stored procedure.