Re: Insert Into Without Log

From: John Kane (jt-kane_at_comcast.net)
Date: 02/23/05 

Date: Wed, 23 Feb 2005 08:22:57 -0800

Dario,
Basically, you cannot turn off transaction logging in SQL Server 2000, you
can only use the below methods (Bulk insert, SELECT INTO, BCP.exe) to
minimally log transactions to the database transaction log. FYI, this
question is often asked in other newsgroups, but the answers are the same
there...

Regards,
John 

-- 
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Dario Concilio [MCP]" <dco@experta-spa.com> wrote in message
news:eySLK6XGFHA.1528@TK2MSFTNGP09.phx.gbl...
> Is not there other way?
>
>
> "John Kane" <jt-kane@comcast.net> ha scritto nel messaggio
> news:%23plWErUGFHA.2932@TK2MSFTNGP15.phx.gbl...
> > Dario,
> > No, the BULK INSERT syntax only allows "BULK INSERT [ [
'database_name'.]
> > [ 'owner' ].] { 'table_name' FROM 'data_file' }  [ WITH ... various
> > options" - see SQL Server 2000 BOL title "Bulk Insert" for more
details...
> >
> > However, what you can do is use "SELECT INTO" and select into a new
table
> > as
> > SELECT INTO will only work with new and not existing tables,
specifically,
> >
> > SELECT * INTO DestinationTable
> > FROM OriginTable
> > WHERE [..]
> >
> > You can also use BCP.exe and you may want to read "Using SQL Server 2000
> > Recovery Models" at
> > http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
> >
> > Hope that helps!
> > John
> > -- 
> > SQL Full Text Search Blog
> > http://spaces.msn.com/members/jtkane/
> >
> >
> > "Dario Concilio [MCP]" <dco@experta-spa.com> wrote in message
> > news:OgG06vPGFHA.2992@TK2MSFTNGP09.phx.gbl...
> >> I see.
> >>
> >> Then..... Can I type this?
> >> BULK INSERT INTO DestinationTable [...]
> >>                                     FROM OriginTable
> >>                                     WHERE [..]
> >>
> >>
> >>
> >> "John Kane" <jt-kane@comcast.net> ha scritto nel messaggio
> >> news:ui2kkiPGFHA.428@TK2MSFTNGP15.phx.gbl...
> >> > Dario,
> >> > Effectively, you want to turn of logging of logged (inserts)
> >> > transaction
> >> > in
> >> > the database log. Correct?
> >> > If so, then the answer for INSERTs is no. There will always be a
> >> > certain
> >> > level of logging of logged (insert, delete, update) in the database
> >> > transaction log, however, you can minimize this via setting the
> >> > database
> >> > recovery model to "bulk insert" and then use BULK INSERT to insert
data
> >> > into
> >> > your (large?) table to minimize the growth of the transaction log...
> >> >
> >> > Hope that helps,
> >> > John
> >> > -- 
> >> > SQL Full Text Search Blog
> >> > http://spaces.msn.com/members/jtkane/
> >> >
> >> >
> >> >
> >> >
> >> > "Dario Concilio [MCP]" <dco@experta-spa.com> wrote in message
> >> > news:egDf2MPGFHA.228@TK2MSFTNGP15.phx.gbl...
> >> >> ok,
> >> >> for example when I delete all records in a table without trace in
sql
> >> > server
> >> >> log
> >> >> I type:
> >> >>
> >> >> TRUNCATE TABLE MyTable
> >> >>
> >> >> My question is:
> >> >> In this case? When I type INSERT INTO etc.. and I don't want to
trace
> >> >> this
> >> >> task in sql server log. What can I do this?
> >> >>
> >> >> thx.
> >> >>
> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> ha scritto nel messaggio
> >> >> news:%23rWuSGPGFHA.2976@TK2MSFTNGP09.phx.gbl...
> >> >> >I take it you are talking about
> >> >> > insert into tableName
> >> >> > Select * from TableName2
> >> >> >
> >> >> > if so, not
> >> >> > -- 
> >> >> > Hilary Cotter
> >> >> > Looking for a SQL Server replication book?
> >> >> > http://www.nwsu.com/0974973602.html
> >> >> >
> >> >> > Looking for a FAQ on Indexing Services/SQL FTS
> >> >> > http://www.indexserverfaq.com
> >> >> >
> >> >> > "Dario Concilio [MCP]" <dco@experta-spa.com> wrote in message
> >> >> > news:uteqqhOGFHA.2032@tk2msftngp13.phx.gbl...
> >> >> >> Hi,
> >> >> >> Can I create an Insert Into Query without sql server registration
> > log?
> >> >> >>
> >> >> >> Thx.
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Relevant Pages

  • Re: Cannot fetch a row from OLE DB provider "BULK" for linked serv
    ... Bulk load: An unexpected end of file was encountered in the data file. ... Anyway, there was an error in the format file, at least in regards to ... This is probably when you need to look at SQL Server Integration Services, ...
    (microsoft.public.sqlserver.programming)
  • LINQ to SQL with bulk insert
    ... I am using a combination of LINQ to SQL and bulk insert. ... One problem I am having is trying to use a 'transaction' to wrap ...
    (comp.databases.ms-sqlserver)
  • Bulk Insert with LINQ to SQL
    ... I am using a combination of LINQ to SQL and bulk insert. ... One problem I am having is trying to use a 'transaction' to wrap ...
    (comp.databases.ms-sqlserver)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Viewing SQl transacion logs...
    ... is there a way to have SQL keep track of specifc changes regardless ... > whether or not the change was part of a transaction? ... The SQL Server Transaction logs keep track of *all* changes to the database, ... triggers on any table to create an audit trail. ...
    (microsoft.public.sqlserver.odbc)
Loading