How to insert the value from the text box (ASP.NET 2.0) to the microsoft sql server 2000 d

Hello Friends,

I have a problem with ASP.net with dynamic data transfer from asp page to microsoft sql server 2000. For example , I have asp web page with one text field and a buttion.When I click the buttion, the value entered in the text field should be transfered from the text field to database.

Kindly See the following section c# code ....

SqlConnection objconnect = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand();

cmd.Connection = objconnect;

cmd.CommandText = " select * from Table_Age where Age = @.Age";

cmd.CommandType = CommandType.Text;

SqlParameter parameter = new SqlParameter();

parameter.ParameterName = "@.Age";

parameter.SqlDbType = SqlDbType.VarChar;

parameter.Direction = ParameterDirection.Output;

parameter.Value = TextBox1.Text;

objconnect.Open();

// Add the parameter to the Parameters collection.

cmd.Parameters.Add(parameter);

SqlDataReader reader = cmd.ExecuteReader();

********************this section c# code is entered under the button control************************************

connection string is mentioned in the web.config file.

In the above c# code , I have a text field , where I entered the age , the value entered in the text field should be sent to database. I have used SqlParameter for selecting the type of data should be sent from ASP.NET 2.0 to the microsoft sql server 2000. I am facing a problem where I am unable to sent the random datas from a text field to the database server.I have a created a database file in the microsoft server 2000.

after the excution of the fIeld value is assinged to NULL in the main database i.e microsoft the sql server 2000.

Can anyone help with this issue.

My mail id phijop@.hotmail.com

- PHIJO MATHEW PHILIP.

Hi

Remove :

parameter.Direction = ParameterDirection.Output;

since your direction should be input.

How to INSERT text that contains single quotes?

When users enter text into a textbox, to be INSERTed into my table, SQL Server throws an error if their text contains a single quote.

For example, if they enter "It's great!" then it causes this error:
Error: Incorrect syntax near 's'. Unclosed quotation mark after the character string ''.

How can I allow text with single quotes to be inserted into the table?

Here's my code:

string strInsert = "INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES (@.GameID, @.UserID, @.Comment)";

SqlConnection myConnection = new SqlConnection(<<myconnectionstuff>>);
SqlCommand myCommand = new SqlCommand(strInsert, myConnection);

myCommand.Parameters.Add( "@.GameID", Request.QueryString["GameID"] );
myCommand.Parameters.Add( "@.UserID", (string)Session["UserID"] );
myCommand.Parameters.Add( "@.Comment", ThisUserCommentTextBox.Text );

try {
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}

catch (SqlException ex) {
ErrorLabel.Text = "Error: " + ex.Message;
}

finally {
myCommand.Connection.Close();
}

You write C# so they are called string literals in C# but in ANSI SQL they are Delimiters per ANSI SQL 92 and SQL Server is compliant so run a search for Qouted Identifier in SQL Server BOL(books online) and try the link below for SQL Server Delimiters. Hope this helps.

http://msdn2.microsoft.com/en-gb/library/ms176027.aspx

|||That code looks fine. Are you sure that is where the problem is?|||

(How can I allow text with single quotes to be inserted into the table?)

The person have not posted the code with the error but I replied the above and the error generated.

|||

Motley:

That code looks fine. Are you sure that is where the problem is?

Well it works fine unless I include a single quote in the text box somewhere.

Caddre, I've researched some more based on your reply, but I don't have easy access to the SQL Server settings - I'm on a shared hosting account. Is there some other workaround, maybe using a regular expression to replace a single quote with something else, at least temprarily while I'm processing the text? I tried entering /' instead, to see if an escape character would work, but SQL Server still won't take it.

|||

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

|||

Caddre:

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

I'm wondering how to send the "SET QUOTED_IDENTIFIER ON" command within my ASP.NET page.

I'm using a command called ExecuteNonQuery(); -- so does that mean I can just append it to the beginning of my INSERT string, like this?

string strInsert = "SET QUOTED_IDENTIFIER ON GO INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES ..."

Does it last only during this query or will it stay on somehow until I turn it off?

Sorry, I'm a newbie with SQL Server and ASP.NET.

|||You have to use it in your stored proc and ExecuteNonQuery takes stored proc. Hope this helps|||

Yes, I understand. It should work fine for any value you place within the textbox control. That includes quotes. I do this quite often with no problems.

As for can you put the SET statement before your query, the answer is yes, but do not separate the commands with GO. You may place a semi-colon between them if you want to however, like:

SET {stuff}; SELECT {stuff}

|||

OK, I am trying to make sense of this, and it's just not making any sense to me. (I have limited experience with SQL and SQL Server and VBScript; I paid a programmer to write a script and after delivering a partly-working script, he promised to fix it and then disappeared. I think he couldn't solve this problem himself.)

I am getting the error message "Unclosed quotation mark after the character string" when I execute this SQL statement from VBScript, butno error when I execute the same SQL statement from within SQL Management Studio. Any suggestions would be extremely welcome.

INSERT INTO Raw_Datafeeds (ProductID,Name,MerchantID,Merchant,Link,Thumbnail,BigImage,Price,RetailPrice,Category,SubCategory,Description,Custom1,Custom2,Custom3,Custom4,Custom5,LastUpdated,Status,Last_Loaded)
VALUES
('454848129','Healthy Ears Ear Cleanser - Buy 2 Get 1 Free','10052',
'Pet Nutrition Products','http://www.shareasale.com/m-pr.cfm?merchantID=10052&userID=YOURUSERID&productID=454848129',
'http://www.petnutritionproducts.com/images/products/100/cat_ears.jpg',
'http://www.petnutritionproducts.com/images/products/400/cat_ears.jpg',15.98,0,'Home/Family','Pets',
'Healthy Ears Ear Cleanser is a veterinarian formulated combination of gentle ingredients that aid in the removal of dirt, wax and other unwanted matter from your cat''s ears.',
'Dog','Ear Care',' ',' ',' ','6/6/2006 1:35:01 AM','instock','10/25/2006 4:06:00 PM')

|||

The original problem in this thread could probably be solved by specifying the type of each parameter rather than letting the system guess.

As for mark's problem, it's obviously related to "cat''s" in your insert string, however since you haven't posted the code you are using in VBScript, it'd be difficult to say where the problem is. As a quick hack, you can replace all quotes in any parameter with '+CHAR(39)+' so that cat's would then become cat'+CHAR(39)+'s.

how to insert text / bitmap column

how to insert text / bitmap column
example pls :-)Originally posted by ligang
how to insert text / bitmap column
example pls :-)

Try the WRITETEXT and UPDATETEXT statements, examples can be found in BOL.

How to Insert Raw text into SQL table

HI all,
I have raw text like these:
test1
test2
test3
...
I want to pass these text in to a SP from Query Analyzer as a paremeter and
the SP will handle to import them to a sql table exactly like the format I
passed in: In this case sql table should store:
test1
test2
test3
...
How do you handle things like this.
Thanks,
tomtom d wrote:
> HI all,
> I have raw text like these:
> test1
> test2
> test3
> ...
> I want to pass these text in to a SP from Query Analyzer as a
> paremeter and the SP will handle to import them to a sql table
> exactly like the format I passed in: In this case sql table should
> store:
> test1
> test2
> test3
> ...
> How do you handle things like this.
> Thanks,
> tom
You can try using the DTS Import-Export Wizard or BCP.
David Gugick
Imceda Software
www.imceda.com|||Hi
Try:
create table mytext ( col1 int not null identity(1,1), textval text )
CREATE PROCEDURE InsertMyText ( @.txtval text )
AS
INSERT INTO MyText ( textval ) VALUES ( @.txtval )
EXEC InsertMyText 'text1
text2
text3'
EXEC InsertMyText '
test1
test2
test3'
select * from MyText
"tom d" wrote:

> HI all,
> I have raw text like these:
> test1
> test2
> test3
> ...
> I want to pass these text in to a SP from Query Analyzer as a paremeter an
d
> the SP will handle to import them to a sql table exactly like the format I
> passed in: In this case sql table should store:
> test1
> test2
> test3
> ...
> How do you handle things like this.
> Thanks,
> tom

How to insert numbers from a text box to a Sql database table column’s type numeric?

Hi,

I am getting an error when I try to insert a number typed in a text box control into a Sql database table column's type numeric(6,2). For example: If I type 35,22 into the text box, or 35, and then I submit the form to insert the data into database, I get the follow error:

System.FormatException: Input string was not in a correct format.
Line 428: CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.decimal, "Measure"))

A piece of the SP inside Sql server:

USE market26
GO
ALTER PROC new_offer
@.Offer_id bigint, @.Measure numeric(6,2) = null, …

What is wrong? Why it doesn' t accept the number typed in my text box?

Thank you,
CesarThis is not correct:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

This would probably work (not tested):

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal);
mParm.Size = 13; // Max size in Bytes of the Decimal number
mParm.Precision = 8; // Total number of digits allowed (right + left of decimal point
mParm.Scale = 2; // Set the number of decimal places the Parameter value is resolved
mParm.set_IsNullable(true);
mParm.Value = decimal.Parse(myTextBox.Text.Trim());
CmdInsert.Parameters.Add(mParm)

|||Hi,

I always write my ASP.NET commands to talk with Sql Stored Procedures thus:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

I think that it is correct, and it works fine.

I have never used a text field to insert numeric data into Sql database, but, I have to define all these parameter properties in order to pass a numeric data type to the database?

Thanks|||That's interesting. There is no Constructor attributed to the SqlParameter Class which accepts a Constructor looking like this:

New SqlParameter(<string>,<SqlDbType Enum>, <string>)

as far as I know...But hey, go for it!|||Take a look at this piece of an article and let me know what do you think about it:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
' Set up parameter for stored procedure
Dim prmCustomerID As New SqlParameter()
prmCustomerID.ParameterName = "@.CustomerID"
prmCustomerID.SqlDbType = SqlDbType.VarChar
prmCustomerID.Size = 5
prmCustomerID.Value = "ALFKI"
cmdOrders.Parameters.Add(prmCustomerID)
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This example makes all the parameter settings explicit. Some developers like this style, and it's good for instructional purposes. However, some developers prefer an equivalent alternative that has fewer lines of code:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
cmdOrders.Parameters.Add(New _
 SqlParameter("@.CustomerID", SqlDbType.VarChar, 5))
cmdOrders.Parameters("@.CustomerID").Value = "ALFKI"
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This code behaves exactly the same as the previous example. However, it only needs two lines of code for each parameter rather than six. When a stored procedure has a lot of parameters (as some of our later examples do), this can be quite a difference on lines of code required, so we'll use that form from this point onward.

The complete article is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/html/vbnet09102002.asp|||Hey! Perhaps you have misunderstood me. I meant that I always use this structure:
(Which I knew that something was wrong)

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

Instead of this:

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal)
mParm.SourceColumn = "Measure"
CmdInsert.Parameters.Add(mParm)

I knew that something was wrong, I suspected that something it lacked in my SqlParameter, and you helped me with your example!, it lacked only the size property which in my case is 5. I am sorry, the only thing that I saw strange is the structure you wrote, and the amount of parameters which I don' t need to use (in this case). I always use the shorter alternative ;-).

So, the correct SqlParameter in my case is:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal,5, "Measure"))

And now works fine.
Thank you very much!
Cesar

how to insert into a table using a select from xml

Good Day,

I am passing some XML into a stored procedure:

<answers>
<answer id="60" text="" />
<answer id="65" text="A moderate form of learning disability" />
<answer id="68" text="We will keep ASD checked" />
<answer id="70" text="" />
</answers>

Along with a memberid and questionid.

I was wondering how I can get this into a table

CREATE TABLE [dbo].[Answers]([PrimaryKeyID] [int]NOT NULL,[MemberID] [int]NOT NULL,[QuestionID] [int]NOT NULL,[AnswerID] [int]NOT NULL,[FreText] [varchar](255) COLLATE Latin1_General_CI_ASNULL)ON [PRIMARY]

What I would also like to do is if the text attribute is empty then put a NULL in the FreText field.

I think I am looking for

 
Insert into MyTable (Select @.MemID, @.QuesID,'somexpathforanswer','somexpathfortext'-- if empty then NULLFrom @.MyXML )
  

Any ideas - places to to look - thoughts Aprreciated

Kal

you can try to use OPENXML see example from T_SQL help below

DECLARE @.idoc intDECLARE @.doc varchar(1000)SET @.doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/> </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/> </Order></Customer></ROOT>'--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc-- Execute a SELECT statement that uses the OPENXML rowset provider.SELECT *FROM OPENXML (@.idoc, '/ROOT/Customer',1) WITH (CustomerID varchar(10), ContactName varchar(20))

How to insert a datetime to sql sever

Hi everyone,

I need help in inserting a date time string to sql sever. For example, how do you insert textbox1.text="2006-08-30 09:00:00" into a datatable column names starttime (type datetime) in sql sever? How do I covert the format of this string before I do the insert?

Thanks.

a123.

You don't have to pass that value. In the colum starttime set the data type to 'starttime' then for the default value enter getdate()

That should fix your problem.

|||

That would be an acceptable format (YYYY-MM-DD HH:mm:ss), of course you lose the sub-second precision though. If you always want to insert the current datetime, then as the previous poster said, change your insert statement to use getdate() rather than take the value from a passed parameter.

How to insert a datetime into sql sever

Hi everyone,

How do you insert this string value lable1.text="2006-08-30 09:00:00" into a data column like startdate (type: datetime) in sql sever?

How do I convert this string value before I insert it into sql sever?

Thank you very much.

a123.

Here is a quick sample:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn2000"].ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO tbl_dt (dt) SELECT @.dt", conn);
cmd.Parameters.Add("@.dt", SqlDbType.DateTime).Value = DateTime.Parse(TextBox1.Text);
cmd.ExecuteNonQuery();

}

how to include an apostrophy inside a string

I am doing a row update in a table and the text contains a ' character, just
wondering how to do this?
for example
UPDATE table
SET field2 = 'text to enter to table's but does not work'
WHERE field1 = 133
the second apostrophy I want to not end the text section with.
Thanks.
--
Paul G
Software engineer.YOu have to double quote that:
UPDATE table
SET field2 = 'text to enter to table''s but does not work'
WHERE field1 = 133
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> I am doing a row update in a table and the text contains a ' character, just
> wondering how to do this?
> for example
> UPDATE table
> SET field2 = 'text to enter to table's but does not work'
> WHERE field1 = 133
> the second apostrophy I want to not end the text section with.
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks. One other question, when I use select from query analizer to
retreive a long text field and then copy and paste it to a word doc it looks
like it only returns the first portion of the large text string. Also is
there anyway to view a large text field from query analyzer when you open the
table? thanks again.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> YOu have to double quote that:
> UPDATE table
> SET field2 = 'text to enter to table''s but does not work'
> WHERE field1 = 133
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > I am doing a row update in a table and the text contains a ' character, just
> > wondering how to do this?
> >
> > for example
> > UPDATE table
> > SET field2 = 'text to enter to table's but does not work'
> > WHERE field1 = 133
> > the second apostrophy I want to not end the text section with.
> > Thanks.
> >
> > --
> > Paul G
> > Software engineer.|||QA is limited to a maximum outpur of 8000 characters, if you don´t have even
that you should look in Tools--> Options --> Results --> Maximum charcters
per column
to increase it to 8000
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> ok thanks. One other question, when I use select from query analizer to
> retreive a long text field and then copy and paste it to a word doc it looks
> like it only returns the first portion of the large text string. Also is
> there anyway to view a large text field from query analyzer when you open the
> table? thanks again.
> --
> Paul G
> Software engineer.
>
> "Jens Sü�meyer" wrote:
> > YOu have to double quote that:
> >
> > UPDATE table
> > SET field2 = 'text to enter to table''s but does not work'
> > WHERE field1 = 133
> >
> >
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> > "Paul" wrote:
> >
> > > I am doing a row update in a table and the text contains a ' character, just
> > > wondering how to do this?
> > >
> > > for example
> > > UPDATE table
> > > SET field2 = 'text to enter to table's but does not work'
> > > WHERE field1 = 133
> > > the second apostrophy I want to not end the text section with.
> > > Thanks.
> > >
> > > --
> > > Paul G
> > > Software engineer.|||ok thanks that should do it as it is way less than 8000 characters.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> that you should look in Tools--> Options --> Results --> Maximum charcters
> per column
> to increase it to 8000
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > ok thanks. One other question, when I use select from query analizer to
> > retreive a long text field and then copy and paste it to a word doc it looks
> > like it only returns the first portion of the large text string. Also is
> > there anyway to view a large text field from query analyzer when you open the
> > table? thanks again.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > YOu have to double quote that:
> > >
> > > UPDATE table
> > > SET field2 = 'text to enter to table''s but does not work'
> > > WHERE field1 = 133
> > >
> > >
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > I am doing a row update in a table and the text contains a ' character, just
> > > > wondering how to do this?
> > > >
> > > > for example
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table's but does not work'
> > > > WHERE field1 = 133
> > > > the second apostrophy I want to not end the text section with.
> > > > Thanks.
> > > >
> > > > --
> > > > Paul G
> > > > Software engineer.|||Just wondering if you know how to direct the results to a file?
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> that you should look in Tools--> Options --> Results --> Maximum charcters
> per column
> to increase it to 8000
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > ok thanks. One other question, when I use select from query analizer to
> > retreive a long text field and then copy and paste it to a word doc it looks
> > like it only returns the first portion of the large text string. Also is
> > there anyway to view a large text field from query analyzer when you open the
> > table? thanks again.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > YOu have to double quote that:
> > >
> > > UPDATE table
> > > SET field2 = 'text to enter to table''s but does not work'
> > > WHERE field1 = 133
> > >
> > >
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > I am doing a row update in a table and the text contains a ' character, just
> > > > wondering how to do this?
> > > >
> > > > for example
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table's but does not work'
> > > > WHERE field1 = 133
> > > > the second apostrophy I want to not end the text section with.
> > > > Thanks.
> > > >
> > > > --
> > > > Paul G
> > > > Software engineer.|||Press CTRL-Shift+F in WA then Execute the query or select the menu Query -->
Results in File
or you use OSQL on the commandline with the -o <outputfile >switch
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Paul" wrote:
> Just wondering if you know how to direct the results to a file?
> --
> Paul G
> Software engineer.
>
> "Jens Sü�meyer" wrote:
> > QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> > that you should look in Tools--> Options --> Results --> Maximum charcters
> > per column
> >
> > to increase it to 8000
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
> >
> > "Paul" wrote:
> >
> > > ok thanks. One other question, when I use select from query analizer to
> > > retreive a long text field and then copy and paste it to a word doc it looks
> > > like it only returns the first portion of the large text string. Also is
> > > there anyway to view a large text field from query analyzer when you open the
> > > table? thanks again.
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Jens Sü�meyer" wrote:
> > >
> > > > YOu have to double quote that:
> > > >
> > > > UPDATE table
> > > > SET field2 = 'text to enter to table''s but does not work'
> > > > WHERE field1 = 133
> > > >
> > > >
> > > > --
> > > > HTH, Jens Suessmeyer.
> > > >
> > > > --
> > > > http://www.sqlserver2005.de
> > > > --
> > > >
> > > >
> > > > "Paul" wrote:
> > > >
> > > > > I am doing a row update in a table and the text contains a ' character, just
> > > > > wondering how to do this?
> > > > >
> > > > > for example
> > > > > UPDATE table
> > > > > SET field2 = 'text to enter to table's but does not work'
> > > > > WHERE field1 = 133
> > > > > the second apostrophy I want to not end the text section with.
> > > > > Thanks.
> > > > >
> > > > > --
> > > > > Paul G
> > > > > Software engineer.|||ok thanks this seems very useful.
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> Press CTRL-Shift+F in WA then Execute the query or select the menu Query -->
> Results in File
> or you use OSQL on the commandline with the -o <outputfile >switch
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Paul" wrote:
> > Just wondering if you know how to direct the results to a file?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > QA is limited to a maximum outpur of 8000 characters, if you don´t have even
> > > that you should look in Tools--> Options --> Results --> Maximum charcters
> > > per column
> > >
> > > to increase it to 8000
> > > --
> > > HTH, Jens Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > ok thanks. One other question, when I use select from query analizer to
> > > > retreive a long text field and then copy and paste it to a word doc it looks
> > > > like it only returns the first portion of the large text string. Also is
> > > > there anyway to view a large text field from query analyzer when you open the
> > > > table? thanks again.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > > >
> > > >
> > > > "Jens Sü�meyer" wrote:
> > > >
> > > > > YOu have to double quote that:
> > > > >
> > > > > UPDATE table
> > > > > SET field2 = 'text to enter to table''s but does not work'
> > > > > WHERE field1 = 133
> > > > >
> > > > >
> > > > > --
> > > > > HTH, Jens Suessmeyer.
> > > > >
> > > > > --
> > > > > http://www.sqlserver2005.de
> > > > > --
> > > > >
> > > > >
> > > > > "Paul" wrote:
> > > > >
> > > > > > I am doing a row update in a table and the text contains a ' character, just
> > > > > > wondering how to do this?
> > > > > >
> > > > > > for example
> > > > > > UPDATE table
> > > > > > SET field2 = 'text to enter to table's but does not work'
> > > > > > WHERE field1 = 133
> > > > > > the second apostrophy I want to not end the text section with.
> > > > > > Thanks.
> > > > > >
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.sql

How to import in special character delimited text file by using SSIS ?

Hi,

I would like to know how to import in the custom delimited text file by using SSIS.

For example, instead by using tab or comma delimited, I use this character : '?'

The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.

I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.

Please help to share the solution on this :

A?B?C
1?2?3

thanks

best regards,

Tanipar

You can import this as a single column and then use a script transform to cycle through your row and break it down into the appropriate columns. There are various examples of doing this relating to uneven / unbalanced / dynamic number of columns...

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

|||I am a fan of the approach above , but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)

How to import in special character delimited text file by using SSIS ?

Hi,

I would like to know how to import in the custom delimited text file by using SSIS.

For example, instead by using tab or comma delimited, I use this character : '?'

The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.

I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.

Please help to share the solution on this :

A?B?C
1?2?3

thanks

best regards,

Tanipar

You can import this as a single column and then use a script transform to cycle through your row and break it down into the appropriate columns. There are various examples of doing this relating to uneven / unbalanced / dynamic number of columns...

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

|||I am a fan of the approach above , but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)

How to import from text file

Hi. Basic question: how do you import from a .csv file into a database table? Thank you.

See SQL Server 2000, 2005 Books Online topic:
bcp Utility

or check out DTS/SSIS import tasks

|||You can also use bulk insert.|||

DTS is best to go when you are not sure about few things that are new to you.

http://www.microsoft.com/technet/prodtechnol/speech/library/loganalysis/log_files_UsingDTS.mspx

KBA http://support.microsoft.com/kb/285829

How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.datawarehouse:21642
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
Tom
You can use BCP or BULK INSERT with a format file.
In BOL, click Go, then URL, then paste this in:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_9yat.htm
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Tom" <tom@.killspam.com> wrote in message
news:l2UGd.14507$W33.456705@.news20.bellglobal.com. ..
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
necessary
> fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Tom
>

How to import fixed-width text file in MS SQL 2000?

Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
Les
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
The wizard does not have the option to use the Bulk Insert DTS task. The DTS
Bulk Insert task gives you graphical interface. Check it in Books OnLine if
it suits you.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Bulk insert with a format file?
http://www.mindsdoor.net/SQLTsql/BCP...rmat_file.html
Bulk insert with a staging table?
http://www.mindsdoor.net/SQLTsql/ImportTextFiles.html
You can generate the format file for the first one or the inser statement in
the second from the structure of the destination table if you have set the
column sizes to match the file.
If you have a lot of different files consider holding the metadata in a table
http://www.mindsdoor.net/SQLTsql/MoveImportData.html
"Tom" wrote:

> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the necessary
> fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Les
>
>

How to import fixed-width text file in MS SQL 2000?

Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
LesThis can be done through the DTS import wizard in Enterprise Manager under
Tools --> Data Transformation Services --> Import Data.
In the wizard select the source Data Source as 'Text File'. The wizard will
guide you through the steps.
Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
command.
--
Stuart Fish
www.technologic.co.nz
"Tom" <tom@.killspam.com> wrote in message
news:M0UGd.14491$W33.456289@.news20.bellglobal.com...
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
> necessary fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Les
>|||I've tried that and it and I found that through the wizard you have to
define the length of the fields.
But I have already a table with the fields. I don't want to go through 50
fields every time I want to import data.
Is that all what we pay MS for?
Les
"Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
> This can be done through the DTS import wizard in Enterprise Manager under
> Tools --> Data Transformation Services --> Import Data.
> In the wizard select the source Data Source as 'Text File'. The wizard
> will guide you through the steps.
> Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
> command.
>
> --
> --
> Stuart Fish
> www.technologic.co.nz
>
> "Tom" <tom@.killspam.com> wrote in message
> news:M0UGd.14491$W33.456289@.news20.bellglobal.com...
>> Hello,
>> Is there a simple answer to this simple question?
>> How to import fixed-width text file?
>> I know the width of the columns but there are so many that I don't want
>> redefine them. I have already a table build for that data with the
>> necessary fields width. In dbase it was such a simple thing to do...
>> Note:
>> I went through the Import wizard in MS SQL 2000 and I could not find any
>> thing there about importing fixed-width text file.
>>
>> Any help is greatly appreciated,
>> Les
>>
>|||At the end of the DTS Import/Export wizard there are options that allow you
to save the DTS package instead of running immediately. That way the DTS
package can then be executed or scheduled as you need it. You don't need to
redefine it each time, as long as your file and table structure remain the
same.
If the package is saved to the SQL Server it can be viewed in Enterprise
Manager under the Data Transformations Services --> Local Packages folder.
"Tom" <tom@.killspam.com> wrote in message
news:sOUGd.15394$W33.466935@.news20.bellglobal.com...
> I've tried that and it and I found that through the wizard you have to
> define the length of the fields.
> But I have already a table with the fields. I don't want to go through 50
> fields every time I want to import data.
> Is that all what we pay MS for?
> Les
>
> "Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
> news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
>> This can be done through the DTS import wizard in Enterprise Manager
>> under Tools --> Data Transformation Services --> Import Data.
>> In the wizard select the source Data Source as 'Text File'. The wizard
>> will guide you through the steps.
>> Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
>> command.
>>
>> --
>> --
>> Stuart Fish
>> www.technologic.co.nz
>>
>> "Tom" <tom@.killspam.com> wrote in message
>> news:M0UGd.14491$W33.456289@.news20.bellglobal.com...
>> Hello,
>> Is there a simple answer to this simple question?
>> How to import fixed-width text file?
>> I know the width of the columns but there are so many that I don't want
>> redefine them. I have already a table build for that data with the
>> necessary fields width. In dbase it was such a simple thing to do...
>> Note:
>> I went through the Import wizard in MS SQL 2000 and I could not find any
>> thing there about importing fixed-width text file.
>>
>> Any help is greatly appreciated,
>> Les
>>
>>
>|||Les,
Most of people choose to loading the data from import file in to a temporary table then doing manipulation on that temp table, this way is useful when during the import progress, you could also maintain your database integrity.
to solve your problem, you could you DTS or SQL stored procedure. i was selected stored procedure to manage this process.
e.g:
you have list of invoice need to be imported to invoice table every day. this file is a flat fixed length file storing in following desc
Invoice#ProductCategory
xxxx-xxxAAAAAAAACCCCCCCC
length of row was 24, since Invoice# was 8, Product 8, Category 8. This file name is invoice.txt
you could define a table as follow
INVOICE_TEMP(
ROWTMP VARCHAR(24)
)
In you stored procedure, you would have this command
BULK INSERT INVOICE_TEMP FROM invoice.txt WITH (BATCHSIZE = 10000, CODEPAGE ='RAW', ROWTERMINATOR = '\n')
Next: You could be able to insert the rows from this temp file to destination table.
INSERT INVOICE (InvoiceNo, Product, Category)
SELECT SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1, 8) FROM INVOICE_TEMP
*done*.
Next: your stored procedure could be called any time you want, or it could be called from DTS and have it running in a defined schedule.
Good luck!
Doan
--
Message posted via http://www.sqlmonster.com

How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.datawarehouse:21642
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
TomYou can use BCP or BULK INSERT with a format file.
In BOL, click Go, then URL, then paste this in:
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_9yat.htm
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom" <tom@.killspam.com> wrote in message
news:l2UGd.14507$W33.456705@.news20.bellglobal.com...
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
necessary
> fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Tom
>

How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:376852
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
LesThis can be done through the DTS import wizard in Enterprise Manager under
Tools --> Data Transformation Services --> Import Data.
In the wizard select the source Data Source as 'Text File'. The wizard will
guide you through the steps.
Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
command.
--
Stuart Fish
www.technologic.co.nz
"Tom" <tom@.killspam.com> wrote in message
news:M0UGd.14491$W33.456289@.news20.bellglobal.com...
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
> necessary fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Les
>|||I've tried that and it and I found that through the wizard you have to
define the length of the fields.
But I have already a table with the fields. I don't want to go through 50
fields every time I want to import data.
Is that all what we pay MS for?
Les
"Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
> This can be done through the DTS import wizard in Enterprise Manager under
> Tools --> Data Transformation Services --> Import Data.
> In the wizard select the source Data Source as 'Text File'. The wizard
> will guide you through the steps.
> Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
> command.
>
> --
> --
> Stuart Fish
> www.technologic.co.nz
>
> "Tom" <tom@.killspam.com> wrote in message
> news:M0UGd.14491$W33.456289@.news20.bellglobal.com...
>|||At the end of the DTS Import/Export wizard there are options that allow you
to save the DTS package instead of running immediately. That way the DTS
package can then be executed or scheduled as you need it. You don't need to
redefine it each time, as long as your file and table structure remain the
same.
If the package is saved to the SQL Server it can be viewed in Enterprise
Manager under the Data Transformations Services --> Local Packages folder.
"Tom" <tom@.killspam.com> wrote in message
news:sOUGd.15394$W33.466935@.news20.bellglobal.com...
> I've tried that and it and I found that through the wizard you have to
> define the length of the fields.
> But I have already a table with the fields. I don't want to go through 50
> fields every time I want to import data.
> Is that all what we pay MS for?
> Les
>
> "Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
> news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
>|||Les,
Most of people choose to loading the data from import file in to a temporary
table then doing manipulation on that temp table, this way is useful when d
uring the import progress, you could also maintain your database integrity.
to solve your problem, you could you DTS or SQL stored procedure. i was sele
cted stored procedure to manage this process.
e.g:
you have list of invoice need to be imported to invoice table every day. thi
s file is a flat fixed length file storing in following desc
Invoice#ProductCategory
xxxx-xxxAAAAAAAACCCCCCCC
length of row was 24, since Invoice# was 8, Product 8, Category 8. This file
name is invoice.txt
you could define a table as follow
INVOICE_TEMP(
ROWTMP VARCHAR(24)
)
In you stored procedure, you would have this command
BULK INSERT INVOICE_TEMP FROM invoice.txt WITH (BATCHSIZE = 10000, CODEPAGE
='RAW', ROWTERMINATOR = '\n')
Next: You could be able to insert the rows from this temp file to destinatio
n table.
INSERT INVOICE (InvoiceNo, Product, Category)
SELECT SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1
, 8) FROM INVOICE_TEMP
*done*.
Next: your stored procedure could be called any time you want, or it could b
e called from DTS and have it running in a defined schedule.
Good luck!
Doan
Message posted via http://www.droptable.com

How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.tools:27686
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
Les
Already answered in numerous other groups. Please refrain from
multi-posting in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Tom" <tom@.killspam.com> wrote in message
news:_0UGd.14493$W33.456187@.news20.bellglobal.com. ..
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
necessary
> fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Les
>

How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:376852
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
Les
This can be done through the DTS import wizard in Enterprise Manager under
Tools --> Data Transformation Services --> Import Data.
In the wizard select the source Data Source as 'Text File'. The wizard will
guide you through the steps.
Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
command.
--
Stuart Fish
www.technologic.co.nz
"Tom" <tom@.killspam.com> wrote in message
news:M0UGd.14491$W33.456289@.news20.bellglobal.com. ..
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
> necessary fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Les
>
|||I've tried that and it and I found that through the wizard you have to
define the length of the fields.
But I have already a table with the fields. I don't want to go through 50
fields every time I want to import data.
Is that all what we pay MS for?
Les
"Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
> This can be done through the DTS import wizard in Enterprise Manager under
> Tools --> Data Transformation Services --> Import Data.
> In the wizard select the source Data Source as 'Text File'. The wizard
> will guide you through the steps.
> Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
> command.
>
> --
> --
> Stuart Fish
> www.technologic.co.nz
>
> "Tom" <tom@.killspam.com> wrote in message
> news:M0UGd.14491$W33.456289@.news20.bellglobal.com. ..
>
|||At the end of the DTS Import/Export wizard there are options that allow you
to save the DTS package instead of running immediately. That way the DTS
package can then be executed or scheduled as you need it. You don't need to
redefine it each time, as long as your file and table structure remain the
same.
If the package is saved to the SQL Server it can be viewed in Enterprise
Manager under the Data Transformations Services --> Local Packages folder.
"Tom" <tom@.killspam.com> wrote in message
news:sOUGd.15394$W33.466935@.news20.bellglobal.com. ..
> I've tried that and it and I found that through the wizard you have to
> define the length of the fields.
> But I have already a table with the fields. I don't want to go through 50
> fields every time I want to import data.
> Is that all what we pay MS for?
> Les
>
> "Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
> news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
>
|||Les,
Most of people choose to loading the data from import file in to a temporary table then doing manipulation on that temp table, this way is useful when during the import progress, you could also maintain your database integrity.
to solve your problem, you could you DTS or SQL stored procedure. i was selected stored procedure to manage this process.
e.g:
you have list of invoice need to be imported to invoice table every day. this file is a flat fixed length file storing in following desc
Invoice#ProductCategory
xxxx-xxxAAAAAAAACCCCCCCC
length of row was 24, since Invoice# was 8, Product 8, Category 8. This file name is invoice.txt
you could define a table as follow
INVOICE_TEMP(
ROWTMP VARCHAR(24)
)
In you stored procedure, you would have this command
BULK INSERT INVOICE_TEMP FROM invoice.txt WITH (BATCHSIZE = 10000, CODEPAGE ='RAW', ROWTERMINATOR = '\n')
Next: You could be able to insert the rows from this temp file to destination table.
INSERT INVOICE (InvoiceNo, Product, Category)
SELECT SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1, 8) FROM INVOICE_TEMP
*done*.
Next: your stored procedure could be called any time you want, or it could be called from DTS and have it running in a defined schedule.
Good luck!
Doan
Message posted via http://www.sqlmonster.com

How to import data from Text file which contains header?

Hi there,

How to import data from such text files into a specified table?

http://www.geocities.com/salan_alani/outputlog.txt

The data I want to import is only the last 5 lines (for instance) shown in the above text file. So, I want first to ignore the header in the beginning of the file, and the other thing that in my row lines (last 5 lines shown in the text file) there is no specific delimeter but each field has a fixed size and position in the line (for example: "RNC ID" field correspond to the first 8 charactes in the line)

I tried to use BULK INSERT statement but I could not figure out how to ignore the header.

I am using SQL Server 2005 Express Edition

Thanks in advanced,

Salan

You need to specify the FIRSTROW parameter with the number of teh first row to import.|||

All the options can be found in BOL

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/be3984e1-5ab3-4226-a539-a9f58e1e01e2.htm

|||

Thanks SimonSa,

But please could you show me how the FORMATFILE could be in my case, I just need an example to start over.

Thanks in advanced,

Salan

|||The FIRSTROW parameter is specified as an option to the BULK INSERT statement. You don't have to do anything to the format file itself. You can also pass this parameter to the BCP command-line utility using -F option.