Bulk Upload

I need to upload multiple reports produced on another server. Is there a
script or utility that will allow me to do this?
Thanks!On Jul 3, 10:58 am, REI <R...@.discussions.microsoft.com> wrote:
> I need to upload multiple reports produced on another server. Is there a
> script or utility that will allow me to do this?
> Thanks!
There is not a quick way to do it; however, this link might give you
some insight.
http://technet.microsoft.com/en-us/library/ms159720.aspx
Regards,
Enrique Martinez
Sr. Software Consultantsql

Bulk Load Text File Using Transact-SQL

I am a newbie (not a developer) trying to load reference tables with SQL script from text files. So far, I know I must use format files but the examples I get are not clear. I cannot find a simple syntax for the format file or the bulk load script. Please help!

Does the following topics help? BOL (SQL Server 2000 or 2005) has lot of examples including format files, statements to use etc.

http://msdn2.microsoft.com/en-us/library/ms189989.aspx

http://msdn2.microsoft.com/en-us/library/aa337544.aspx

http://msdn2.microsoft.com/en-us/library/ms175915.aspx

http://msdn2.microsoft.com/en-us/library/ms178129.aspx

http://msdn2.microsoft.com/en-us/library/ms189848.aspx

http://msdn2.microsoft.com/en-us/library/ms190625.aspx

sql

Bulk Load - net enough memory error- HELP!

I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
database) . When I change the connection to point to another machine (simila
r
hardware configuration) I get
[There is insufficient system memory to run this query.]
The script is running on my machine connecting to a MSDE database on another
machine.
The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
still generates the error. I think it is a configuration issue because if I
change the connection to another development machine it works with the 140
meg file.Solution - I checked the maximum memory that the instance was set to. It was
5 meg, I increased it to 500 meg and the bulkload ran like a champ. it's a
good thing that you can use enterprise manager to manage MSDE databases....
.
"xke" wrote:

> I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
> database) . When I change the connection to point to another machine (simi
lar
> hardware configuration) I get
> [There is insufficient system memory to run this query.]
> The script is running on my machine connecting to a MSDE database on anoth
er
> machine.
> The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
> still generates the error. I think it is a configuration issue because if
I
> change the connection to another development machine it works with the 140
> meg file.
>sql

Bulk Load - net enough memory error- HELP!

I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
database) . When I change the connection to point to another machine (similar
hardware configuration) I get
[There is insufficient system memory to run this query.]
The script is running on my machine connecting to a MSDE database on another
machine.
The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
still generates the error. I think it is a configuration issue because if I
change the connection to another development machine it works with the 140
meg file.
Solution - I checked the maximum memory that the instance was set to. It was
5 meg, I increased it to 500 meg and the bulkload ran like a champ. it's a
good thing that you can use enterprise manager to manage MSDE databases.....
"xke" wrote:

> I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
> database) . When I change the connection to point to another machine (similar
> hardware configuration) I get
> [There is insufficient system memory to run this query.]
> The script is running on my machine connecting to a MSDE database on another
> machine.
> The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
> still generates the error. I think it is a configuration issue because if I
> change the connection to another development machine it works with the 140
> meg file.
>

BULK INSERT SYNTAX

I am setting up a new database using a shopping cart SW, when I create the DB
using there script, I get the following error

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '('.

The line syntax is as follows.

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv';

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ','

)

GO

What is wrong?

hi,

ShaneShowers wrote:

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv';

you have the semicolon (;) in the wrong place... at the end of the first line after the file to be imported.. the semicolon indicates a statement termination so that the next statement just becomes

WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
)

which makes no sense.... remove it and place it at the end of the statement

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
);

regards

Bulk insert of bit values

Hi Gang:
I've inherited a database that contains a number of tables with bit value
columns. I am trying to write a script with bulk insert statements to
reconstruct the database. When I export the table data, the bit fields get
converted to string equivalents (True and False.) When the bulk insert
statement for the same table executes, I get errors saying that these values
can't be converted to bits.
Can you tell me how to export the data from my tables into text files so
that the bit values can be re-imported / bulk inserted correctly?Hi Robert,
Should be 1,0 or NULL
In what format did you export the data, if you export them to excel its
getting converted to True or False...
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
> Hi Gang:
> I've inherited a database that contains a number of tables with bit value
> columns. I am trying to write a script with bulk insert statements to
> reconstruct the database. When I export the table data, the bit fields
> get
> converted to string equivalents (True and False.) When the bulk insert
> statement for the same table executes, I get errors saying that these
> values
> can't be converted to bits.
> Can you tell me how to export the data from my tables into text files so
> that the bit values can be re-imported / bulk inserted correctly?
>|||Hi
Valid BIT values are 0, 1 and NULL.
True and False are not supported.
VB.NET, VB, VBScript regard True as -1, so that is not convertible. Most
other languages regard True as +1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com> wrote
in message news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
> Hi Gang:
> I've inherited a database that contains a number of tables with bit value
> columns. I am trying to write a script with bulk insert statements to
> reconstruct the database. When I export the table data, the bit fields
> get
> converted to string equivalents (True and False.) When the bulk insert
> statement for the same table executes, I get errors saying that these
> values
> can't be converted to bits.
> Can you tell me how to export the data from my tables into text files so
> that the bit values can be re-imported / bulk inserted correctly?
>|||I exported it from SQL Server using the import / export wizard. I exported
it as a comma delimited text file. Are there other export options I need to
check to make this work?
"Jens Sü?meyer" wrote:

> Hi Robert,
> Should be 1,0 or NULL
> In what format did you export the data, if you export them to excel its
> getting converted to True or False...
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com>
> schrieb im Newsbeitrag
> news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
>
>|||Hi
I don't have any issue with the following:
CREATE TABLE [MyBits] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Bit1] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit1] DEFAULT (0),
[Bit2] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit2] DEFAULT (0),
[Bit3] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit3] DEFAULT (1),
[Bit4] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit4] DEFAULT (1),
CONSTRAINT [PK_MyBits] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
)
GO
INSERT INTO MYBits ( Bit1 )
SELECT 1
UNION ALL SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 0
SELECT * FROM MyBits ORDER BY ID
/*
Id Bit1 Bit2 Bit3 Bit4
-- -- -- -- --
1 1 0 1 1
2 0 0 1 1
3 1 0 1 1
4 0 0 1 1
5 1 0 1 1
6 0 0 1 1
(6 row(s) affected)
*/
-- Create new table
SELECT * INTO MyBits2 FROM MyBits WHERE 1 = 0
-- Command Prompt
-- bcp test..mybits2 IN mybits.txt -c -T
/* MyBits.txt
1 1 0 1 1
2 0 0 1 1
3 1 0 1 1
4 0 0 1 1
5 1 0 1 1
6 0 0 1 1
*/
-- At command prompt import
-- bcp test..mybits2 IN mybits.txt -c -T
SELECT * FROM MyBits2 ORDER BY ID
/* Output
Id Bit1 Bit2 Bit3 Bit4
-- -- -- -- --
1 1 0 1 1
2 0 0 1 1
3 1 0 1 1
4 0 0 1 1
5 1 0 1 1
6 0 0 1 1
(6 row(s) affected)
*/
Can you post DDL, example data and your statements
http://www.aspfaq.com/etiquett_e.asp?id=5006
John
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com> wrote
in message news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
> Hi Gang:
> I've inherited a database that contains a number of tables with bit value
> columns. I am trying to write a script with bulk insert statements to
> reconstruct the database. When I export the table data, the bit fields
> get
> converted to string equivalents (True and False.) When the bulk insert
> statement for the same table executes, I get errors saying that these
> values
> can't be converted to bits.
> Can you tell me how to export the data from my tables into text files so
> that the bit values can be re-imported / bulk inserted correctly?
>|||If it wont work for you, you can cast the values to int, thatll work.
Jens Suessmeyer.
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:A1EF625E-F6AA-4C3F-AD0F-BFCF8CA98375@.microsoft.com...
> I exported it from SQL Server using the import / export wizard. I
> exported
> it as a comma delimited text file. Are there other export options I need
> to
> check to make this work?
> "Jens Smeyer" wrote:
>|||Hi
It seems that this may be feature of the text file driver! As Jens says you
can cast as an int and it will work. To do this choose the option to specify
a query as the source of your export and then put in a statement like:
SELECT [Id],
CAST([Bit1] AS INT) AS [Bit1],
CAST([Bit2] AS INT) AS [Bit2],
CAST([Bit3] AS INT) AS [Bit3],
CAST([Bit4] AS INT) AS [Bit4]
FROM [MyBits]
Alternatively use BCP as in my other post.
John
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com> wrote
in message news:A1EF625E-F6AA-4C3F-AD0F-BFCF8CA98375@.microsoft.com...
> I exported it from SQL Server using the import / export wizard. I
> exported
> it as a comma delimited text file. Are there other export options I need
> to
> check to make this work?
> "Jens Smeyer" wrote:
>|||Robert,
Ignoring why you have the strings True and False in your text
file, you can import your data into a staging table that matches
your ultimate destination except for the bit column, which in the
staging table should be varchar(5). Then once the 'True' and 'False'
strings are in the staging table,
insert into Destination
select
nonBitcolA,
nonBitcolB,
cast(case when preBit1 = 'True' then 1 when 'False' then 0 end as bit)
as BitCol1,
..
from Staging
Anything from your text file that is not 'True' or 'False' in the bit
columns will import as NULL. You can check for that either in
the staging table or the destination table with
select * from Staging
where preBit1 not in ('True','False')
or preBit2 not in ('True','False')
...
or
select * from Destination
where BitCol1 is null
or BitCol1 is null
...
Steve Kass
Drew University
Robert Burdick [eMVP] wrote:

>Hi Gang:
>I've inherited a database that contains a number of tables with bit value
>columns. I am trying to write a script with bulk insert statements to
>reconstruct the database. When I export the table data, the bit fields get
>converted to string equivalents (True and False.) When the bulk insert
>statement for the same table executes, I get errors saying that these value
s
>can't be converted to bits.
>Can you tell me how to export the data from my tables into text files so
>that the bit values can be re-imported / bulk inserted correctly?
>
>|||Thanks, I've almost got it. SQL Server Books Online doesn't explain how to
tell BCP to comma dellimit the fields. Any ideas?
"Steve Kass" wrote:

> Robert,
> Ignoring why you have the strings True and False in your text
> file, you can import your data into a staging table that matches
> your ultimate destination except for the bit column, which in the
> staging table should be varchar(5). Then once the 'True' and 'False'
> strings are in the staging table,
> insert into Destination
> select
> nonBitcolA,
> nonBitcolB,
> cast(case when preBit1 = 'True' then 1 when 'False' then 0 end as bit)
> as BitCol1,
> ...
> from Staging
> Anything from your text file that is not 'True' or 'False' in the bit
> columns will import as NULL. You can check for that either in
> the staging table or the destination table with
> select * from Staging
> where preBit1 not in ('True','False')
> or preBit2 not in ('True','False')
> ...
> or
> select * from Destination
> where BitCol1 is null
> or BitCol1 is null
> ...
> Steve Kass
> Drew University
>
> Robert Burdick [eMVP] wrote:
>
>|||>> I've inherited a database that contains a number of tables with bit
value columns. <<
The right answer is to re-design the database from an assembly language
model of data to an RDBMS and get rid of the proprietary BIT data.
reconstruct the database. When I export the table data, the bit fields
[sic] get converted to string equivalents (True and False.) <<
Why do you think that (0,1) mapping to (FALSE, TRUE) is a correct model
for casting BITs. Some host languages use (0,-1) and other use (1,0).
And there are no BOOLEANs in SQL-92 for very good reasons that have
to do with 3VL, NULLs and the data model in SQL.
The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors of
Hercules, he got to kill the bastard that gave him this job. Maybe you
will be lucky, too.

BULK INSERT halt script execution

Is there a way to continue the execution of a script after an error occured after a BULK INSERT execution?
I tried this but is not working:

ex:
declare @.m_strSQL nvarchar(4000)
declare @.m_RetCode int
select @.m_strSQL = N'BULK INSERT DBName.[dbo].[tableName] FROM ''c:\FileName.txt'' WITH (CODEPAGE=''ACP'')'
execute @.m_RetCode = sp_executesql @.m_strSQL
if @.m_RetCode > 0
print 'error'
else
print 'no error'Take a look at MaxErrors option for bulk insert. Also, do read through Erland's article:

http://www.sommarskog.se/error-handling-I.html

Bulk Insert Error

When I run a script, it gives me the following error
message: "You do not have permission to use the BULK
INSERT statement". We are using SQL Server 2000.
Does it related to Recovery Model used ?
ThanksYou need to be in sysadmin to run a bulk insert.
I think it should also work as bulkadmin but has problems.
"Peter" wrote:
> When I run a script, it gives me the following error
> message: "You do not have permission to use the BULK
> INSERT statement". We are using SQL Server 2000.
> Does it related to Recovery Model used ?
> Thanks
>|||Hi,
The user should have either "SYSADMIN" or "BULKADMIN" server fixed role
assigned.
How to assign the role:-
sp_addsrvrolemember <login_name>,'bulkadmin'
--
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20ff201c459b1$c0ed7f10$a001280a@.phx.gbl...
> When I run a script, it gives me the following error
> message: "You do not have permission to use the BULK
> INSERT statement". We are using SQL Server 2000.
> Does it related to Recovery Model used ?
> Thanks
>|||Does it mean that we can still preform Bulk Insert even
though the recovery model is "Simple" ?
Thanks
>--Original Message--
>You need to be in sysadmin to run a bulk insert.
>I think it should also work as bulkadmin but has problems.
>"Peter" wrote:
>> When I run a script, it gives me the following error
>> message: "You do not have permission to use the BULK
>> INSERT statement". We are using SQL Server 2000.
>> Does it related to Recovery Model used ?
>> Thanks
>>
>.
>|||Hi,
BULK INSERT is possible in all recovery models.
Only thing is in BULK_LOGGED and SIMPLE recovery model the loading will not
be logged.
--
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20ba701c459b4$a896b190$a101280a@.phx.gbl...
> Does it mean that we can still preform Bulk Insert even
> though the recovery model is "Simple" ?
> Thanks
> >--Original Message--
> >You need to be in sysadmin to run a bulk insert.
> >I think it should also work as bulkadmin but has problems.
> >
> >"Peter" wrote:
> >
> >> When I run a script, it gives me the following error
> >> message: "You do not have permission to use the BULK
> >> INSERT statement". We are using SQL Server 2000.
> >>
> >> Does it related to Recovery Model used ?
> >>
> >> Thanks
> >>
> >>
> >.
> >

Bulk Insert Error

When I run a script, it gives me the following error
message: "You do not have permission to use the BULK
INSERT statement". We are using SQL Server 2000.
Does it related to Recovery Model used ?
ThanksYou need to be in sysadmin to run a bulk insert.
I think it should also work as bulkadmin but has problems.
"Peter" wrote:

> When I run a script, it gives me the following error
> message: "You do not have permission to use the BULK
> INSERT statement". We are using SQL Server 2000.
> Does it related to Recovery Model used ?
> Thanks
>|||Hi,
The user should have either "SYSADMIN" or "BULKADMIN" server fixed role
assigned.
How to assign the role:-
sp_addsrvrolemember <login_name>,'bulkadmin'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20ff201c459b1$c0ed7f10$a001280a@.phx
.gbl...
> When I run a script, it gives me the following error
> message: "You do not have permission to use the BULK
> INSERT statement". We are using SQL Server 2000.
> Does it related to Recovery Model used ?
> Thanks
>|||Does it mean that we can still preform Bulk Insert even
though the recovery model is "Simple" ?
Thanks

>--Original Message--
>You need to be in sysadmin to run a bulk insert.
>I think it should also work as bulkadmin but has problems.
>"Peter" wrote:
>
>.
>|||Hi,
BULK INSERT is possible in all recovery models.
Only thing is in BULK_LOGGED and SIMPLE recovery model the loading will not
be logged.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20ba701c459b4$a896b190$a101280a@.phx
.gbl...[vbcol=seagreen]
> Does it mean that we can still preform Bulk Insert even
> though the recovery model is "Simple" ?
> Thanks
>

Bulk Insert Error

When I run a script, it gives me the following error
message: "You do not have permission to use the BULK
INSERT statement". We are using SQL Server 2000.
Does it related to Recovery Model used ?
Thanks
You need to be in sysadmin to run a bulk insert.
I think it should also work as bulkadmin but has problems.
"Peter" wrote:

> When I run a script, it gives me the following error
> message: "You do not have permission to use the BULK
> INSERT statement". We are using SQL Server 2000.
> Does it related to Recovery Model used ?
> Thanks
>
|||Hi,
The user should have either "SYSADMIN" or "BULKADMIN" server fixed role
assigned.
How to assign the role:-
sp_addsrvrolemember <login_name>,'bulkadmin'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20ff201c459b1$c0ed7f10$a001280a@.phx.gbl...
> When I run a script, it gives me the following error
> message: "You do not have permission to use the BULK
> INSERT statement". We are using SQL Server 2000.
> Does it related to Recovery Model used ?
> Thanks
>
|||Does it mean that we can still preform Bulk Insert even
though the recovery model is "Simple" ?
Thanks

>--Original Message--
>You need to be in sysadmin to run a bulk insert.
>I think it should also work as bulkadmin but has problems.
>"Peter" wrote:
>.
>
|||Hi,
BULK INSERT is possible in all recovery models.
Only thing is in BULK_LOGGED and SIMPLE recovery model the loading will not
be logged.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20ba701c459b4$a896b190$a101280a@.phx.gbl...[vbcol=seagreen]
> Does it mean that we can still preform Bulk Insert even
> though the recovery model is "Simple" ?
> Thanks

BULK INSERT datafile parameter

Hi everyone,
how can I pass a variable to the command "BULK INSERT", to the datafile
parameter? The following script:
create procedure some_procedure (@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
fails with error "Incorrect syntax near '@.filename'". Any idea?
Thanks,
Tamas BeriCreate a string with the bulk insert command and exec it ie
exec('bulk insert sometable from ' + @.filename + yad yada)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:546E659E-ABC3-4B34-B6D5-2EB647E3A6A9@.microsoft.com...
> Hi everyone,
> how can I pass a variable to the command "BULK INSERT", to the datafile
> parameter? The following script:
> create procedure some_procedure (@.filename varchar(256)) as
> begin
> bulk insert some_table from @.filename with(codepage='raw');
> end;
> fails with error "Incorrect syntax near '@.filename'". Any idea?
> Thanks,
> Tamas Beri
>|||Thanks,
it's finally working with the exec...
"Wayne Snyder" wrote:
> Create a string with the bulk insert command and exec it ie
> exec('bulk insert sometable from ' + @.filename + yad yada)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com

build sql script in excel

Hi,
I have been given an excel spreadsheet with two columns with data.
There are about 20 records in this excel sheet.
Would like to write an insert query to insert these data.
I am thinking of writing an insert query for the first line iin the excel sheet and then drag it down to the last row of data so that it automatically writes the values of the columns in the insert query and i just copy and paste the script into sql to run.
This is what I have but the values of the cells do not get reflected.
Any thoughts pls?
'insert into TBLData (RE_ID, EMAIL) values (' & c2 & ',' & d2 ')'I used a similar thing just today.

I wrote the first cell as:
"INSERT INTO into TBLData (RE_ID, EMAIL) "

then dragged the following formula down:

=" SELECT '" & c2 & "', '" & d2 & "' UNION ALL "

Copy -> Paste to QA and remove the last UNION ALL

HTH