Bulk Insert Statement

Hi All

I have a text file (sample below) i am bulk loading it into a staging table, the problem i am getting is the data is being loaded and scrambling the rows and I need the data to be imported exactly the same row by row

040207,"1007","","3319506/"
031207,"1509",">","US78016"
031207,"1509",">","AA004388"
031207,"1509",">","COMD88"
031207,"1509",">","US78016"
031207,"1509",">","AA001601"
031207,"1509",">","COMD88"
031207,"1510",">","US78016"
031207,"1510",">","AA004337"
031207,"1510",">","COMD88"
031307,"1138",">","US78016"
031307,"1139",">","AA004293"
031307,"1139",">","COMD81"

set nocount on
bulk insert data_load_stage.dbo.
from 'C:\load\CM07.txt'
with ( fieldterminator = ',')

missing a switch i think

thanks in advance

rich

Richie,

You'll need to add a clustered index to your staging table and then add the ORDER hint to your BULK INSERT statement (both matching the ordering of the input file).

From BULK INSERT in BOL:

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )

Specifies how the data in the data file is sorted. Bulk load operation performance is improved if the data loaded is sorted according to the clustered index on the table. If the data file is sorted in a different order, or there is no clustered index on the table, the ORDER clause is ignored. The column names supplied must be valid columns in the destination table. By default, the bulk insert operation assumes the data file is unordered.

sql

Bulk Insert Problem

Hi to All!
I am trying to use the 'Bulk Insert' command to load a data file into a
MS-SQL db. The line I am using is:
BULK INSERT Data..tbl_load
FROM 'C:\Data\data.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
Sql Server is giving error:
Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file 'C:\Data\data.txt'
could not be opened. Operating system error code 3(error not found).
What is the problem in script
*** Sent via Developersdex http://www.examnotes.net ***Hi
Can you show us the text's file structure and data?
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:Or$4xS$HGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi to All!
> I am trying to use the 'Bulk Insert' command to load a data file into a
> MS-SQL db. The line I am using is:
> BULK INSERT Data..tbl_load
> FROM 'C:\Data\data.txt'
> WITH (
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n'
> )
>
> Sql Server is giving error:
> Server: Msg 4861, Level 16, State 1, Line 1
> Could not bulk insert because file 'C:\Data\data.txt'
> could not be opened. Operating system error code 3(error not found).
> What is the problem in script
>
> *** Sent via Developersdex http://www.examnotes.net ***|||There is only one column in the text file and data type is "char"
sample data is like
ASU0028010180
ASU0038010181
ASU0028010182
ASU0028010183
ASU0028010184
*** Sent via Developersdex http://www.examnotes.net ***|||two things that I can think of:
Does the account that SQL Server runs with has read permissions on that file
?
The contents of the file is not encrypted, right?
"Ghulam Farid" wrote:

> There is only one column in the text file and data type is "char"
> sample data is like
>
> ASU0028010180
> ASU0038010181
> ASU0028010182
> ASU0028010183
> ASU0028010184
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||I tried to do some testing
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
xp_cmdshell 'bcp database.dbo.ww out c:\file.fil -c -t, -SSRV -Usa -PRRR'
BULK INSERT ww FROM 'c:\file.fil' WITH (FIELDTERMINATOR = ',')
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:eMePaj$HGHA.1760@.TK2MSFTNGP10.phx.gbl...
> There is only one column in the text file and data type is "char"
> sample data is like
>
> ASU0028010180
> ASU0038010181
> ASU0028010182
> ASU0028010183
> ASU0028010184
>
> *** Sent via Developersdex http://www.examnotes.net ***|||From the command-prompt, I entered:
NET HELPMSG 3
The result was:
The system cannot find the path specified.
I suggest you check to ensure 'C:\Data\data.txt' exists at the specified
location on the SQL Server. Note that the path is accessed from the server
rather than the client. Of the file resides on the client machine, you'll
need to create a share and assign file/share permissions to the SQL Server
service in order to BULK INSERT from a network location.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:Or$4xS$HGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi to All!
> I am trying to use the 'Bulk Insert' command to load a data file into a
> MS-SQL db. The line I am using is:
> BULK INSERT Data..tbl_load
> FROM 'C:\Data\data.txt'
> WITH (
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n'
> )
>
> Sql Server is giving error:
> Server: Msg 4861, Level 16, State 1, Line 1
> Could not bulk insert because file 'C:\Data\data.txt'
> could not be opened. Operating system error code 3(error not found).
> What is the problem in script
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Consider that the BULK INSERT.. command processes on the database server, so
the file data.txt would need to exist on the server HD. However, the bulk
copy DOD command (BCP.EXE) processes on the machine that it is executed on.
BCP.EXE is what you would need to use when bulk copying from a local
workstation to SQL Server.
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:Or$4xS$HGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi to All!
> I am trying to use the 'Bulk Insert' command to load a data file into a
> MS-SQL db. The line I am using is:
> BULK INSERT Data..tbl_load
> FROM 'C:\Data\data.txt'
> WITH (
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n'
> )
>
> Sql Server is giving error:
> Server: Msg 4861, Level 16, State 1, Line 1
> Could not bulk insert because file 'C:\Data\data.txt'
> could not be opened. Operating system error code 3(error not found).
> What is the problem in script
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Or run the BCP.exe command from the client machine.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhbSbGCIGHA.3944@.tk2msftngp13.phx.gbl...
> From the command-prompt, I entered:
> NET HELPMSG 3
> The result was:
> The system cannot find the path specified.
> I suggest you check to ensure 'C:\Data\data.txt' exists at the specified
> location on the SQL Server. Note that the path is accessed from the
> server rather than the client. Of the file resides on the client machine,
> you'll need to create a share and assign file/share permissions to the SQL
> Server service in order to BULK INSERT from a network location.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
> news:Or$4xS$HGHA.3460@.TK2MSFTNGP12.phx.gbl...
>

Bulk insert into table with more columns than data within file

Hey all

I have a bulk insert situation that would be nice to be able to pull off. I have a flat file with 46 columns that are to go into a table. The table, I want to have a 47th column to be updated later on by means of a stored proc saying if the import into the system was sucessful or not. I have the rowterminator set as '"\n' thinking that would tell SQL to begin on the next row, leaving the importstatus column null but i still receive an error.

First of all, is this idea possible within this insert statement. Secondly, if so, what would be the syntax to tell the insert statement to skip that particular column. It is the last column listed in the table so it just needs to start on the next row after it inserts the last bit of data in the flatfile.

If this is not possible, is it possible to bulk insert into a temp table?

Thanksyou can do this if you specify a format file. read up on bcp in BOL about format files.

alternatively, you could bulk insert into a staging table or temp table and then do insert/select from there.

finally, if you can generate the file over again, you can include a null like this (assuming comma separated:

1,2,3,,5,

note the extra commas. in this case, a null would be inserted in the 4th and 6th positions.

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
Griff
Griff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff
|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/de...l7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Building SQL using variables

Hello all!

I am sure there is a technical name for this, but I am trying to build a sql statement using variables... where the variables would be entire clauses within the statement, not just values. This will ultimately be used in a stored procedure.

ie. Focus on the @.AndClause variable

-----------
declare @.AndClause varchar(128)
select @.AndClause = ' AND lastname like ''jharris%'''

SELECT *
FROM my_customer_table
WHERE 1=1
@.AndClause
-----------

I have seen this done before but can not find it in an of my references. Thank you for your helpdeclare @.AndClause varchar(128)
set @.AndClause = ' AND lastname like ''jharris%'''
declare @.vSQL varchar(200)
set @.vSQL = 'SELECT *
FROM my_customer_table
WHERE 1=1' + @.AndClause

exec(@.vSQL)|||I missed the plus sign... Thanks alot Jora! Do you know if there is technical name for this?|||euh ... building dynamic sql statements? Don't think there is one term for it. Also, you can use the stored procedure sp_executesql for executing dynamic queries. See Books Online for more info on the differences between the two methods.