Raster Catalog: insert

Showing posts with label insert. Show all posts

Friday, March 30, 2012

Reading a text file from a stored procedure.

A rookie question - all I want to do is open a text file x.txt and read each line - no bcp or bulk insert required.
Is there a simple way to do this ?
Thanks in advance to all who reply !Not with ANSI-92 syntax. You'd have to use specific DBMS extensions for it. Pick the engine (Oracle, SQL Server, UDB, etc.) and post accordingly.|||I am using SQL Server 7.0.

I know how to do this in Oracle using the DBMS functions. Are there similar functions in MSSQL ?

Thanks for your reply !|||I use sp_OAxxx with FileSystemObject.|||Originally posted by BrutusBuckeye
I am using SQL Server 7.0.

I know how to do this in Oracle using the DBMS functions. Are there similar functions in MSSQL ?

Thanks for your reply ! In my opinion, this is one of the fundamental design flaws in Oracle. They are attempting to make PL/SQL a programming language instead of a data[base] manipulation language.

If you stop and think about it, reading text the way that you want to do it is a client side activity. Using BCP or BULK INSERT are server side activities. There is a fundamental difference between them (which machine the code actually runs on)!

Any solution you find for MS-SQL will involve server side activity. Sybase (now Microsoft) never intended for Transact-SQL scripts to run on the client, they always assumed that those Transact-SQL scripts would run on the server. That is exactly why user interface code, file access, etc are absent from Transact-SQL... The absence is by design.

Using Microsoft Transact-SQL, you'll need to either adopt a server centric point of view, or write your client side code using the client language. There is a clear distinction between the client and server in Transact-SQL.

-PatP|||BrutusBuckeye, Pat has a very strong opinion about all this ;)

I'd still use sp_OAxxx if you insist on reading a text file one line at a time, but why bother? Use BULK INSERT and then deal with it in a recordset-based fashion!|||UNCLE !!!! :)

Thanks for all your help. I am indeed going the Bulk Insert route.

Thanks again from the school of hard knocks.. :)|||Originally posted by rdjabarov
BrutusBuckeye, Pat has a very strong opinion about all this ;) Dang! Did I let that secret out again ?!?!

-PatP

Wednesday, March 28, 2012

Read Timeout

Hi Friends,

I have the text file in my server which is 21 MB size.

i want to insert that text file into temporary table and then based on the conditions will insert the original table.
But before inserting all the records in temporary table, i got the below error message but the backend process still running(inserting into temp table)

The following error was encountered:Read Timeout

The system returned:

 [No Error]

A Timeout occurred while waiting to read data from the network. The network or server may be down or congested. Please retry your request.

I am using the following code configuration section

sessionStatetimeout="600" />

httpRuntime

executionTimeout="110"

maxRequestLength="21504"

requestLengthDiskThreshold="80"

useFullyQualifiedRedirectUrl="false"

minFreeThreads="8"

minLocalRequestFreeThreads="4"

appRequestQueueLimit="5000"

enableKernelOutputCache="true"

enableVersionHeader="true"

requireRootedSaveAsPath="true"

enable="true"

shutdownTimeout="90"

delayNotificationTimeout="5"

waitChangeNotification="0"

maxWaitChangeNotification="0"

enableHeaderChecking="true"

sendCacheControlHeader="true"

apartmentThreading="false" />

How to implement the page keep active while all the records are inserted into DB?

If you are using AJAX, you could add an AJAX Timer to show the progress

Monday, March 26, 2012

Read Text file from SqlServer

Hi Frinds,

I have the text file in my server.

Is it possible to read the text file and insert into table via sql server stored procedure.

yes. You can use BCP or BULK INSERT to do the job. Refer to Books Online for more details. http://search.live.com/macros/sql_server_user_education/booksonline

read text file

hi
i need to know how can i read a text file and then insert it into the table. i dont need all the information in the text file and i just need to take some information

thanksCheck my respond here:

http://www.msdner.com/forum/thread715092.html|||hi
thanks for your response
but what i need is that i have atext file that so any rows and columns are in it but i just need some rows and columns not all of them.

thanks|||Insert text into a #temporary table and then do your query on what you need and insert it into a production table the rest will go away when procedure will end.

Good Luck.

Read Text file

Hi,

I have the text file in my server.

Is it possible to read that text file and insert into the table.

pls advice

Regards

Antony

SELECT PVListFile.F1 AS mytxt INTO MyTable FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\";User
ID="Admin";Password=;Extended Properties="text;HDR=No"')...filename#txt as PVListFile
|||

Refer to Books Online about using 'BULKINSERT', 'bcp', and/or 'OPENDATASOURCE'.

See this recent thread about using bcp.

sql

read only cell

I try to insert a value to a column and it gives me, "the cell is read
only" and won't allow me to type in the value. I am using MS SQL 2005. Can
anyone please tell me how to turn off the read only property? Thanks.I figured out that the column identity value is to true. How do I turn it to
false? It won't allow me to do it in SQL Server Management Studio. Thanks.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:exl6Eg3mHHA.4552@.TK2MSFTNGP05.phx.gbl...
>I try to insert a value to a column and it gives me, "the cell is read
>only" and won't allow me to type in the value. I am using MS SQL 2005. Can
>anyone please tell me how to turn off the read only property? Thanks.
>|||No sweat. I got it. Sorry.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uaMf2m3mHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I figured out that the column identity value is to true. How do I turn it
>to false? It won't allow me to do it in SQL Server Management Studio.
>Thanks.
>
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:exl6Eg3mHHA.4552@.TK2MSFTNGP05.phx.gbl...
>>I try to insert a value to a column and it gives me, "the cell is read
>>only" and won't allow me to type in the value. I am using MS SQL 2005. Can
>>anyone please tell me how to turn off the read only property? Thanks.
>|||" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:%23zgzYx3mHHA.596@.TK2MSFTNGP06.phx.gbl...
> No sweat. I got it. Sorry.
>
Hmm, I'm a little concerned.
Generally editing data using SMS isn't that great of an idea.
And turning off an Identity column is also generally a bad idea.
You may want to think about how you're doing whatever you're doing.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uaMf2m3mHHA.4772@.TK2MSFTNGP05.phx.gbl...
>>I figured out that the column identity value is to true. How do I turn it
>>to false? It won't allow me to do it in SQL Server Management Studio.
>>Thanks.
>>
>> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
>> news:exl6Eg3mHHA.4552@.TK2MSFTNGP05.phx.gbl...
>>I try to insert a value to a column and it gives me, "the cell is read
>>only" and won't allow me to type in the value. I am using MS SQL 2005.
>>Can anyone please tell me how to turn off the read only property? Thanks.
>>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for your concern. The identity field is wrong in the first place. So
don't worry.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23F%23MEu5mHHA.1388@.TK2MSFTNGP05.phx.gbl...
>" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
>news:%23zgzYx3mHHA.596@.TK2MSFTNGP06.phx.gbl...
>> No sweat. I got it. Sorry.
> Hmm, I'm a little concerned.
> Generally editing data using SMS isn't that great of an idea.
> And turning off an Identity column is also generally a bad idea.
> You may want to think about how you're doing whatever you're doing.
>
>> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
>> news:uaMf2m3mHHA.4772@.TK2MSFTNGP05.phx.gbl...
>>I figured out that the column identity value is to true. How do I turn it
>>to false? It won't allow me to do it in SQL Server Management Studio.
>>Thanks.
>>
>> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
>> news:exl6Eg3mHHA.4552@.TK2MSFTNGP05.phx.gbl...
>>I try to insert a value to a column and it gives me, "the cell is read
>>only" and won't allow me to type in the value. I am using MS SQL 2005.
>>Can anyone please tell me how to turn off the read only property?
>>Thanks.
>>
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>sql

Friday, March 23, 2012

Read one field in every row during SELECT

Hello

I have a table that keeps track of every access to the system. We insert the UserID, TimeOfAccess and TypeOfAccess.

We want to create a report and due to the limitations of Reporting Services for programatically processing, we want to create a temporary table to have the columns already set up for the report with the info that we need.

ORIGINAL TABLE

USERID | TIMEOFACCESS | TYPE OF ACCESS

2323 | 12/15/2007 03:52:54 | CLOCKIN

2323 | 12/15/2007 04:32:54 | CLOCKOUT

2323 | 12/15/2007 05:42:54 | CLOCKIN

2323 | 12/15/2007 07:53:54 | CLOCKOUT

2323 | 12/15/2007 09:18:54 | CLOCKIN

2323 | 12/15/2007 10:24:54 | CLOCKOUT

TEMPORARY TABLE

USERID | CLOCKIN | CLOCKOUT | ELAPSEDTIME

2323 | 03:52:54 | 04:32:54 | 0:40:54

OK. the problem is that I need to read the fields one by one in the SELECT statement so i can insert (and Update) the fields of the temporary table.

I create my temporary table

CREATE #TempTable

(

UserID int,

ClockIN DateTime null,

ClockOUT DateTime null,

ElapsedTime DateTime null,

)

I want to be able to do this in my stored procedure. I can doit in a form but i want to return from my database the datatable already suitable for my report.

Does anyone know how to read in a SELECT statement one field as it's been read?

you can do that using a cursor in your stored procedure.

See: http://www.sqlteam.com/item.asp?ItemID=553

If you dont want to use a cursor, its possible as well:

http://www.sql-server-performance.com/dp_no_cursors.asp

|||

hi,

you can achieve this using a better solution by dong a self join on userid and midnight(dateaccess)

you just have to drop the timepart of the date on the join. you dont even need the temp table.

here's the pseudocode: you just need to imporve this.

select userid, convert( varchar(20),dateaccess,102) as dateaccess, convert( varchar (20),dateaccess,108) as clockin, t2.clockout from table1 t1

where [type of access]='clockin'

join

(select userid, convert( varchar(20),dateaccess,102) as dateaccess,

convert( varchar(20),dateaccess,108) as clockout, from table1

where [type of access]='clockout')

as t2

on t1.userid=t2.userid and t1.dateaccess=t2.dateaccess

regards,

joey

|||

Try selecting the minimum "clockin" and maximum "clockout" per each userid, then calculate the diff in seconds.

Code Snippet

;with cte

(

select

userid,

min(case when [type of access] = 'clockin' then [time of access] end) as clockin,

max(case when [type of access] = 'clockout' then [time of access] end) as clockout,

datediff(

seconds,

min(case when [type of access] = 'clockin' then [time of access] end) as clockin,

max(case when [type of access] = 'clockout' then [time of access] end) as clockout

) as elapsed_time_sec

from

dbo.t1

group by

userid

)

select

userid,

clockin,

clockout,

right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)

from

cte;

AMB

|||

None of this solutions actually work.

I don't get the desired result. I think this is something impossible to do in a Stored Procedure wich is very disappointing because is not so difficult to do in a form. But that's what i don't want. There has to be a way... i guess i need to study more the t-sql language. I just can't believe t-sql is so poor.

|||

Try:

Code Snippet

;with cte_1

(

select

userid,

[TIME OF ACCESS],

[TYPE OF ACCESS],

row_number() over(partition by userid order by [TIME OF ACCESS]) as rn

from

dbo.t1

cte_2

(

select

a.userid,

a.[TIME OF ACCESS] as CLOCKIN,

b.[TIME OF ACCESS] as CLOCKOUT,

datediff(second, a.[TIME OF ACCESS], b.[TIME OF ACCESS]) as elapsed_time_sec

from

cte_1 as a

inner join

cte_1 as b

on a.userid = b.userid

and a.rn = b.rn - 1

and a.[TYPE OF ACCESS] = 'CLOCKIN'

and b.[TYPE OF ACCESS] = 'CLOCKOUT'

)

select

userid,

CLOCKIN,

CLOCKOUT,

right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)

from

cte_2

order by

userid,

CLOCKIN;

BTW, I wonder why elapsed time for the first two rows is "00:40:54", is they both have same number of seconds. I think it should be "00:40:00".

AMB

|||

This is it.

It works perfectly fine.

Definitely i am gonna buy a couple of T-SQL books. Any suggestion on the best title or publisher?

Jose

|||

I will suggest the serie "Inside SQL Server 2005".

Inside Microsoft SQL Server 2005: T-SQL Querying

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=pd_bbs_sr_3/002-2026708-7606405?ie=UTF8&s=books&qid=1179781170&sr=1-3

Inside Microsoft SQL Server 2005: T-SQL Programming

http://www.amazon.com/Inside-Microsoft-Server-2005-Pro-Developer/dp/0735621977/ref=pd_sim_b_1/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3

Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_sim_b_2/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3

AMB

P.S. Your alias exposed you.

PP --> Pepe (Jose)

CUBAN --> from Cuba

Oye chico, vinistes a bailar en casa del trompo.

|||

AMB

P.S. Your alias exposed you.

As one who belives in transparency, and thinks that a lot of folks are stuck in grade school with their childish and 'cutsy' nom de plumes, exactly how this is a problem in this venue?

Granted, anonymity has it purposes and places -but really, are these forums such place to fear exposure?

|||

good point. I am not affraid of exposure. As a matter of fact, most of the people online knows me by that name. ppcuban@.{most famous emails}.com are my address, the domain ppcuban.com also... hahaha. So, basically everybody call me ppcuban as a nickname even out of internet.

AMB? Tu eres cubano?

|||

Jose,

Nacido y criado en la Vibora. Emigre hacia los Estados Unidos de america hace 9 anios. Vivi por un tiempo en Miami y ahora radico en Carolina del Sur (No pastelitos de guayava y mucho menos masareal).

Saludos,

Alejandro Mesa

Read information from excel file into ms sql table ?

Hi,

I want to insert a long list of articles and prices from a excel ark to two different database tables. These tables are linked together with a unique id, called obj_num.

Is there any possible way to do this ? Right now I do i manually and it takes for ever!

My Excel ark,
Column A1 (Article)
----
Coke
Fanta
Sprite
etc ..

Column A2 (Price)
----
2
2.2
3
etc ..

My database
table_article
------
obj_num
name

table_price
------
obj_num
price

Best Regards
Magnus

Quote:

Originally Posted by mberggren

Hi,

I want to insert a long list of articles and prices from a excel ark to two different database tables. These tables are linked together with a unique id, called obj_num.

Is there any possible way to do this ? Right now I do i manually and it takes for ever!

My Excel ark,
Column A1 (Article)
----
Coke
Fanta
Sprite
etc ..

Column A2 (Price)
----
2
2.2
3
etc ..

My database
table_article
------
obj_num
name

table_price
------
obj_num
price

Best Regards
Magnus

if this is one time, i'd rather use a DTS (Enterprise Manager - Import Data). then do everything using query

read data from log

hi all

how i can read all the querys that send to the server

exmple

insert , update statement need all this statement at the end of the day how can i do that by sqlserver

thanks alot

SQL Server Profiler would be one way. TSQL events.|||Start up SQL Server Profiler. Create a new trace. Specify the desired events. Run the trace. You can collect the events into either a flat-file or a table (useful for issues additonal queries against it for integoration purposes).|||Thanks very much friends|||

If you are satisifed please mark an answer.

Thanks,

Wednesday, March 21, 2012

read data from a csv file and insert it in a sql server databse table

Hai Everbody,

for me in my project i want to read data from a csv file and insert it in a sql server databse table.The csv file may contain n number of columns,but i want only certain columns from that, and insert it in the database table.How to achieve this. Plz help me it is urgent. Thanks in advance.

Thanks and regards

Biju.S.G

http://www.dotnetspider.com/kb/Article1082.aspx

http://www.devarticles.com/c/a/ASP.NET/Reading-a-Delimited-File-Using-ASP.Net-and-VB.Net/

read cvs in c# and insert into sql server

Hi,

Anybody has an example of reading a csv comma delimited file and insert the fields into a datatable?

Thanks

You have serveral options: bcp utility, DTS (SSIS in SQL 2005), Import/Export Wizard, or bulk insert command. DTS is much easier than others, you can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_des_07xh.asp

Or you can use the Import/Export Wizard:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_wiz_8vsj.asp

|||

Sorry, I must've posted to the wrong forum. I thought I posted into the LogParser one. I'll repost there. Yeah, I was asking if anybody knows how to use LogParser to read a custom cvs log file into the db. Thanks though.

Read a file name from network folder automatically for a BULK INSERT

Hi again all,

Is there a way to read a file name automatically from a network folder? I can successfully bulk insert from this particular folder. The next step is as I add files, I wish to bulk insert the latest file added so the program must make that determination and import that specific file. I can delete the older files if necessary and save them elsewhere but it would still be nice to be able to read the file name. I then wish to store the name of this file, whatever it is, into a field called "SourceFileName" in my table that I am bulk inserting into. Does anyone have an example in dynamic SQL? Thanks.

ddaveDECLARE @.FilePathToSQLFiles VARCHAR(2000)
DECLARE @.Path VARCHAR(2000)

SET @.FilePathToSQLFiles = 'C:\'

CREATE TABLE #SQLFiles ( SQLFileName VARCHAR(2000))

SET @.Path = 'dir /b "' + @.FilePathToSQLFiles + '*.sql"'

INSERT INTO #SQLFiles
EXECUTE master.dbo.xp_cmdshell @.Pathsql

Wednesday, March 7, 2012

RDA question

i am using a combination of PULL and SubmitSql to synchronize data between
PPC and back-end Server.
I save the INSERT/UPDATE clauses in the PPC to send with SubmitSql later.
Clients use GPRS. I am thinking to send the SQL scripts partially. it seems
that sending one by one would be costly and is not prefered.
What is the ideal number of clauses to send
considering performance , bandwidth and
limitations related to RDA (such as the size of the sql script to
ubmit.. ) ?
In my tests , submitsql causes 3-4 Kb transfer between Server and PPC. There
are routine
requests independent from the size of the SQL script. i think , each clause
would add about extra 100 - 200 bytes to data traffic.
thanks so much
Yener
you're really not going to get a quality response to this - there has been
very little
published regarding RDA tuning. there is a configurable compression level
you
can play with, but other than that, you are going to have to run some tests
on your particular network/device/database to find the sweet-spot in terms
of how many scripts to submit with each RDA round trip.
Compression Level can vary from 0 to 3. Please refer to SQL Mobile Books
Online page for RDA.CompressionLevel @.
http://msdn2.microsoft.com/en-us/lib...sionlevel.aspx
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:%23UGi%23GRHGHA.240@.TK2MSFTNGP11.phx.gbl...
>i am using a combination of PULL and SubmitSql to synchronize data between
> PPC and back-end Server.
> I save the INSERT/UPDATE clauses in the PPC to send with SubmitSql
> later. Clients use GPRS. I am thinking to send the SQL scripts partially.
> it seems that sending one by one would be costly and is not prefered.
> What is the ideal number of clauses to send
> considering performance , bandwidth and
> limitations related to RDA (such as the size of the sql script to
> bmit.. ) ?
> In my tests , submitsql causes 3-4 Kb transfer between Server and PPC.
> There are routine
> requests independent from the size of the SQL script. i think , each
> clause would add about extra 100 - 200 bytes to data traffic.
> thanks so much
> Yener
>
>
|||thanks Darren.
"Darren Shaffer" <darrenshaffer@.discussions.microsoft.com> wrote in message
news:e1YSRaSHGHA.3700@.TK2MSFTNGP15.phx.gbl...
> you're really not going to get a quality response to this - there has been
> very little
> published regarding RDA tuning. there is a configurable compression level
> you
> can play with, but other than that, you are going to have to run some
> tests
> on your particular network/device/database to find the sweet-spot in terms
> of how many scripts to submit with each RDA round trip.
> Compression Level can vary from 0 to 3. Please refer to SQL Mobile Books
> Online page for RDA.CompressionLevel @.
> http://msdn2.microsoft.com/en-us/lib...sionlevel.aspx
> --
> Darren Shaffer
> .NET Compact Framework MVP
> Principal Architect
> Connected Innovation
> www.connectedinnovation.com
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%23UGi%23GRHGHA.240@.TK2MSFTNGP11.phx.gbl...
>

RDA Push to a table that has an INSERT Trigger

Hello,

I posted under the Smart Devices VB and C# Projects forum as well, but since my question relates to SQL Server Triggers, I thought I might get some help here too. I apologize if this is not the correct forum.

I am using SQL Server 2005 Standard.

On my PC, on the table that I Push to from the device, I have a Trigger that sends an e-mail upon INSERT.

This trigger works (sends e-mail) when I run an INSERT against the table within management studio, or when I run an INSERT against the table from a web app on the same PC. (I also tested using an UPDATE, and the trigger worked.)

Here is my problem:

The trigger does not kick off after I successfully Push records from the device to the table on my PC.

Here is my c# Push code on the device:

rda.Push("tablename", rdaOleDbConnectString, RdaBatchOption.BatchingOn);

NOTE: I am not having a problem with the actual Push itself. That is working just fine.

On the surface it seems like an INSERT is happening (a new row appears in the table with the trigger), but does the Push do some other kind of action other than a true INSERT?

I can't seem to find anything about what exactly the Push is doing behind the scenes.

Can triggers be set for anything other than UPDATE, INSERT, DELETE?

Any insight would be appreciated.

Thank you.

Patrick

According to this article: http://msdn2.microsoft.com/en-us/library/aa225639(SQL.80).aspx it should work, but you might have to use SET NOCOUNT ON in your trigger (if you already haven't)

If that doesn't work, try using profiler and see what commands are actually being sent.

|||

I realized that the Trigger on the table was causing problems. I decided to remove the trigger on the table I Push into. I then wrote a stored proc to INSERT INTO another table that contains a trigger. This trigger successfully sends the single e-mail.

Now, I want to send multiple e-mails. I'll enter another post.

Friday, March 30, 2012

Reading a text file from a stored procedure.

Wednesday, March 28, 2012

Read Timeout

Monday, March 26, 2012

Read Text file from SqlServer

read text file

Read Text file

read only cell

read only cell

read only cell

Friday, March 23, 2012

Read one field in every row during SELECT

Read information from excel file into ms sql table ?

read data from log

Wednesday, March 21, 2012

read data from a csv file and insert it in a sql server databse table

read cvs in c# and insert into sql server

Read a file name from network folder automatically for a BULK INSERT

Wednesday, March 7, 2012

RDA question

RDA Push to a table that has an INSERT Trigger

Raster Catalog

Blog Archive

About Me