How to bypass log file ?

I am basically archiving some data from one db to another...
This process causes a great increase in the size of the log file. Any way
to do the row transfer without affecting the size of the log file ? I have
filled up the hard drive before doing such activities.
ThanksRob wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any way
> to do the row transfer without affecting the size of the log file ? I have
> filled up the hard drive before doing such activities.
> Thanks
You can't "bypass" the transaction log, nor would you want to. What
you can do is archive your records in batches, rather than all at once.
Doing the work in batches will keep the individual transactions small,
allowing them to commit faster, and your transaction log backups that
you run at regular intervals will then remove those committed
transactions from the transaction log.|||You can't turn off or bypass transaction logging. If you are moving a very
large amount of data on a regular basis such as once every month, quater, or
year, you may consider using table partitioning in SQL2005, which allows you
to slide a partition (that may correspond to a month worth of data) in and
out of a table as a SQL Server meta-data operation thus without causing your
tran log to fill up. Contrast this with having to delete and insert every
single row for the month.
Linchi
"Rob" wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any way
> to do the row transfer without affecting the size of the log file ? I have
> filled up the hard drive before doing such activities.
> Thanks
>
>

How to by pass this error message

Hi,

I am trying to import transfer data from one database(sqlserver) to another database(sqlserver)...

But when i run the stored procedure... it gives me the following error

Msg 2627, Level 14, State 1, Procedure usp_ImportFunds_Growthof10K, Line 36

Violation of PRIMARY KEY constraint 'PK_Growthof10K'. Cannot insert duplicate key in object 'Growthof10K'.

and this is my sproc

Code Snippet

USE [StageFiserv_Dev]

GO

/****** Object: StoredProcedure [dbo].[usp_ImportFunds_Growthof10K] Script Date: 08/10/2007 12:53:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[usp_ImportFunds_Growthof10K]

AS

BEGIN

DECLARE @.Count int

SET NOCOUNT ON;

UPDATE Fiserv_Dev..Growthof10K

SET

ChartHeader = g.ChartHeader,

Dates = Substring(g.Dates,1,9),

NAV = g.NAV,

LastChangeDate = GetDate()

FROM

Fiserv_Dev..Growthof10K gk

Join [Growth] g ON gk.Cusip = g.Cusip

Where

gk.ChartHeader <> g.ChartHeader

OR

gk.Dates <> Substring(g.Dates,1,9)

OR

gk.NAV <> g.NAV

SET @.Count = @.@.ROWCOUNT

IF @.Count > 0

RAISERROR('Updated %d records(s) in Growthof10K.', 0, 1, @.Count) WITH NOWAIT

ELSE

RAISERROR('No changes made Growthof10K.', 0, 1) WITH NOWAIT

RAISERROR('Adding records to Growthof10K.',0,1) With NOWAIT

INSERT INTO Fiserv_Dev..Growthof10K

(

Cusip,

ChartHeader,

Dates,

NAV

)

SELECT

g.Cusip,

g.ChartHeader,

Substring(g.Dates,1,9),

g.NAV

FROM

Growth g

Where

NOT Exists (

Select *

FROM

Fiserv_Dev..Growthof10K gk

Where

gk.Cusip = g.Cusip

)

SET @.Count = @.@.ROWCOUNT

IF @.Count > 0

RAISERROR('Added %d records(s) to Growthof10K table.', 0, 1, @.Count) WITH NOWAIT

ELSE

RAISERROR('No records added to Growthof10K table.', 0, 1) WITH NOWAIT

SET NOCOUNT OFF

END

There around 763 records in the growth table

Can some one please help me.

Regards,

Karen

Karen

You are inserting a duplicate value in a primary key. You can't do that. Delete the duplicate values for the primary key field or remove the primary key constraint.

|||

thanks...|||

Did this solve your problem? If so then please mark it as answer.

How to Bulk Insert string data into a money colum?

How to Bulk Insert string data into a money colum?

Format files work great to import string data into a char column.

But I cannot convert a char column to either a money or numeric data type.

I get datatype errors when bulk inserting string data into a money column.

The string data is 22 characters long, no decimal point, trailing negative sign.

Here is an example: 000000000000007898384-

My goal: Bulk Insert the above sample string into a money column.

Often, for situations like this, it is useful to first import into a 'staging' table (same datatypes as import data),

AND then execute 'clean-up' routines

BEFORE moving the data into the production tables.

This allows correcting alot of data anomolies that cannot be handled with a format file.

|||

Thanks, Arnie, for the quick response. What clean-up routines did you have in mind?

Using Enterprise Manager to change the destination column's data type from "char" to "money" or "int" won't work.

Are you talking about concatenating a decimal point to the end of the source string, in hopes that the decimal point will help me get past the conversion issue?

|||

John,

Something like this could work for your situation:

Code Snippet

DECLARE
@.MyString varchar(25),
@.MyMoney money

SET @.MyString = '000000000000007898384-'

IF right( @.MyString, 1 ) = '-'
SET @.MyMoney = cast( ( '-' + left( @.MyString, ( len( @.MyString ) - 1 ))) AS money )
ELSE
SET @.MyMoney = cast( @.MyString AS money )

SELECT @.MyMoney

-7898384.0000

|||

So, it looks like you're putting the negative sign at the front as needed and leaving the positive numbers alone, before casting the result as money. This is fine.

My SQL table has 23 columns and millions of rows.

Column 10 is char, 22 characters wide. Negative signs are trailing.

How would I apply the code to all the rows of column 10 in that table?

|||

You would build a CASE structure (something like this):

DECLARE @.MyTable table
( RowID int IDENTITY,
Column10 varchar(50)
)

INSERT INTO @.MyTable VALUES ( '000000000000007898384-' )
INSERT INTO @.MyTable VALUES ( '000000000000007898385' )

SELECT
RowID,
MyMoney = cast( stuff( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END
, len( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END ) -1
, 0, '.' ) AS money )
FROM @.MyTable

RowID MyMoney
--
1 -78983.8400
2 78983.8500

For illustration purposes, I've also added a decimal point two places from the right. If you don't need that, then remove the STUFF() function. (I thought that just 'might' come up next...)

You 'should' be able to use a query like this to INSERT the staging table data into your production table.

|||

Ok, Arnie, I'll give it a shot. Thanks again.

John

|||

My brain wasn't operating on all cylinders this morning.

You can do without the STUFF() function -just divide by 100 if you need the partial money, remove the [ / 100 ] if you have whole money.

Code Snippet

SELECT
RowID,
MyMoney = cast( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END AS money ) / 100

FROM @.MyTable

|||This is great, Arnie, thanks.

How to bulk insert a file with fixed row length and no row terminator?

Hi All,

I have a file that has fixed row size of 148 and fixed column size, but the file has no end of line character. I know it is wierd but a client has made the file and refuses to change the format. So I am stuck with reading it the way it is.
In Enterprise Manager, I used the Import/Export wizard and I specified fixed length and it let me specify 148 as the lenght of each line. Then it recognized the file and I was able to read it in.
I saved the DTS package and I can run it over and over again using dtsrun. However I want to do the same thing using Bulk Insert. How do you specify fixed row length for Bulk insert and how do you give it individual column lengths?

Thanks,

ShabWouldn't know how to do this other than using a format-file. An handy way is to use 'bcp' from the command prompt and dump the file into the table. You can have it create a format-file for you.|||Try this:

create table testimport(col1 varchar(5))

BULK INSERT testimport FROM 'c:\test\test.txt'
WITH (
FORMATFILE = 'c:\test\test.fmt'
)

test.fmt:

8.0
1
1 SQLCHAR 0 5 "" 1 col1 Latin1_General_BIN|||You need a format file...

Do you have books online?

Look up BULK INSERT or bcp and you'll find it...

What version are you running?

Wait, I'll go look for a sample...

How to bulk insert a file from a computer other than the sql serve

I am executing the following bulk insert command:
BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
The sql server and file:'E:\myOutput.txt' are in different computers.
The sql server generated the following error msg:
Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
Operating system error code 21(The device is not ready.).
Any suggestions?
Thank you!
Is E: drive letter mapped to a network path to the share that has
myOutput.txt on it? If so, and you can see the file from a process on the
box with SQL Server on then BULK INSERT should work. I suspect you're
specifying E: as the drive letter of the remote computer - that's not going
to work.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"she" <she@.discussions.microsoft.com> wrote in message
news:6E44D770-FE7D-4EBC-B351-67273CB2C18B@.microsoft.com...
> I am executing the following bulk insert command:
> BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
> The sql server and file:'E:\myOutput.txt' are in different computers.
>
> The sql server generated the following error msg:
> Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
> Operating system error code 21(The device is not ready.).
> Any suggestions?
> Thank you!
>

How to bulk insert a file from a computer other than the sql serve

I am executing the following bulk insert command:
BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
The sql server and file:'E:\myOutput.txt' are in different computers.
The sql server generated the following error msg:
Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
Operating system error code 21(The device is not ready.).
Any suggestions?
Thank you!Is E: drive letter mapped to a network path to the share that has
myOutput.txt on it? If so, and you can see the file from a process on the
box with SQL Server on then BULK INSERT should work. I suspect you're
specifying E: as the drive letter of the remote computer - that's not going
to work.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"she" <she@.discussions.microsoft.com> wrote in message
news:6E44D770-FE7D-4EBC-B351-67273CB2C18B@.microsoft.com...
> I am executing the following bulk insert command:
> BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
> The sql server and file:'E:\myOutput.txt' are in different computers.
>
> The sql server generated the following error msg:
> Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
> Operating system error code 21(The device is not ready.).
> Any suggestions?
> Thank you!
>

How to bulk insert a file from a computer other than the sql serve

I am executing the following bulk insert command:
BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
The sql server and file:'E:\myOutput.txt' are in different computers.
The sql server generated the following error msg:
Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
Operating system error code 21(The device is not ready.).
Any suggestions?
Thank you!Is E: drive letter mapped to a network path to the share that has
myOutput.txt on it? If so, and you can see the file from a process on the
box with SQL Server on then BULK INSERT should work. I suspect you're
specifying E: as the drive letter of the remote computer - that's not going
to work.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"she" <she@.discussions.microsoft.com> wrote in message
news:6E44D770-FE7D-4EBC-B351-67273CB2C18B@.microsoft.com...
> I am executing the following bulk insert command:
> BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
> The sql server and file:'E:\myOutput.txt' are in different computers.
>
> The sql server generated the following error msg:
> Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
> Operating system error code 21(The device is not ready.).
> Any suggestions?
> Thank you!
>