Bulk Insert with Mirroring

Can anyone suggest a bulk insert solution in a mirroring environment?
Basically, I'd like to import a text or excel file using SSIS.
Do all the "Flat File" and "Excel" data flow sources use bulk inserts?
Just wondering if I would need to use a "staging" table or something like that since bulk inserts won't be logged when dbs are mirrored. I'm assuming I'd be able to import data using the dataflow sources into a sql destination "staging" table, then do a "INSERT INTO [production] SELECT * FROM [staging]". I'm assuming this will be logged (but slow).

Any help would be appreciated.

BULK INSERT is fully logged in FULL mode, so you should not need to do the intermediate step.

Bulk Insert Utility

I need to import data from an excel file to a SQL SERVER databse with the help of BUlk insert utility (with out running a DTS).Can anybody tell me the code?I need to place the code in a Stored Procedure.
SubhasishHi!

Bulk Insert task is a kind of task that can be included into a DTS package.
I believe you CANT import from an excel file using an stored procedure, you have to create a DTS package

see ya
Lorena

Originally posted by subhasishray
I need to import data from an excel file to a SQL SERVER databse with the help of BUlk insert utility (with out running a DTS).Can anybody tell me the code?I need to place the code in a Stored Procedure.

Subhasish|||Can I do it with the help of BCP?|||No, it needs to be a csv (or some delimited) or fixed width file...|||So either save the xls as csv or call the dts package from your stored procedure. Any reason why you do not want to use dts ?

Bulk Insert of excel sheet

Hi

I need to bulk insert a excel sheet into a sql 2005 db datatable. I have to do this with three different excel files, inserting them into three different tables (each excel file has one sheet). This works like a charm for two of them, one excel file is causing troubles, as data types of the columns of the inserted data sheet are 'ntext'. This ntext declaration is causing problems within my app where I access that table.

So, any idea where I can set what datatype the columns of an inserted excel sheet should be within the sql datatable? I need the columns to be varchar(255) as it is by doing this with the two other excel files. The excel file causing troubles is being generated by another app.

Any help would be much appreciated!

t-sql code:

USE KOMAX
GO

EXEC sp_dboption Komax, 'select into/bulkcopy',True
EXEC sp_dboption Komax, 'ansi_nulls',True
EXEC sp_dboption Komax, 'ansi_warnings',True
GO

-- Delete existing Table
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Messages')
DROP TABLE Messages
-- Insert Excel Sheet
SELECT * INTO Messages FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\Messages.XLS', [Messages$])
GO

EXEC sp_dboption Komax, 'select into/bulkcopy',False
EXEC sp_dboption Komax, 'ansi_nulls',False
EXEC sp_dboption Komax, 'ansi_warnings',False
GO

Best Regards
Benjamin

Capone wrote:

The excel file causing troubles is being generated by another app.

I would say you've already found the problem.

Adamus

|||But it somehow has to be possible to define what datatype the excel columns are after inserted in sql!|||

Of course there are many ways.

Traditionally:

ALTER TABLE MyTable

ALTER COLUMN MyField varchar(255)

Adamus

|||That's a way, right. I'm just trying to figure out where this ntext is coming from and where to change that behaviour. My concern is: what if one of the other fil starts causing this problem as well? For example: if the 3rd part app which generates this files changes just something? I prefer a safe solution...

I don't wanna alter my whole database after a bulk insert.

But thanks a lot for your posts though!|||

Is the application creating the table (at runtime) before the insert?

If so, the datatype is hardcoded into the application. You can make the change there.

If the table already exists, change the datatype with the previous posted code and that should resolve the issue. There shouldnt' be a conversion problem frm ntext to varchar

Adamus

Bulk Insert from Excel file

Hi ...
Having trouble getting Bulk Insert to work with .xls file.
Following code works fine for text file:
BULK INSERT MyTable FROM 'C:\bulkinsert.txt'
with (fieldterminator ='\t', datafiletype ='char')
Anybody know what changes I need to make to above code so it works for Excel
file?
Thanks in advance for your help ...
bill morganBill,
BULK INSERT cannot be used to import data from Excel.
You can use OpenRowset, or you can set up a linked server
with an Excel provider.
Here is one way, assuming ReportData is a named range of
the Excel sheet.
insert into MyTable
select <columns> from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Report.xls',
'select * from ReportData'
) -- will be called F1, F2, F3, ... if there are no headers.
If it's just a sheet, try
insert into MyTable
select <columns> from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Report.xls',
'select * from [Sheet1$]'
)
The $ is added to the sheet name, which itself has no $ character.
There are a few other things to watch out for, like type conversion,
and you can find other examples in these threads if the ones above
don't help.
http://groups.google.com/groups?hl=...cel%2Bsqlserver
Also, MSDN has a little more information than Books Online about this.
-- Steve Kass
-- Drew University
bill_morgan wrote:

>Hi ...
>Having trouble getting Bulk Insert to work with .xls file.
>Following code works fine for text file:
>BULK INSERT MyTable FROM 'C:\bulkinsert.txt'
>with (fieldterminator ='\t', datafiletype ='char')
>Anybody know what changes I need to make to above code so it works for Exce
l
>file?
>Thanks in advance for your help ...
>bill morgan
>
>
>|||Steve,
Great stuff. Thank you. Since I have over 200 Excel files to import (but
only one sheet per file) I'm setting up your suggestion_2 below. If I can
get it to work for one, then I'm sure I can loop through others, as files us
e
standardized names.
Thanks again ...
"Steve Kass" wrote:

> Bill,
> BULK INSERT cannot be used to import data from Excel.
> You can use OpenRowset, or you can set up a linked server
> with an Excel provider.
> Here is one way, assuming ReportData is a named range of
> the Excel sheet.
> insert into MyTable
> select <columns> from OpenRowset(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\Report.xls',
> 'select * from ReportData'
> ) -- will be called F1, F2, F3, ... if there are no headers.
> If it's just a sheet, try
> insert into MyTable
> select <columns> from OpenRowset(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\Report.xls',
> 'select * from [Sheet1$]'
> )
> The $ is added to the sheet name, which itself has no $ character.
> There are a few other things to watch out for, like type conversion,
> and you can find other examples in these threads if the ones above
> don't help.
> http://groups.google.com/groups?hl=...cel%2Bsqlserver
> Also, MSDN has a little more information than Books Online about this.
> -- Steve Kass
> -- Drew University
>
>
> bill_morgan wrote:
>
>|||PS - suggestion_2 worked perfectly...! Now for the loop ...
"Steve Kass" wrote:

> Bill,
> BULK INSERT cannot be used to import data from Excel.
> You can use OpenRowset, or you can set up a linked server
> with an Excel provider.
> Here is one way, assuming ReportData is a named range of
> the Excel sheet.
> insert into MyTable
> select <columns> from OpenRowset(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\Report.xls',
> 'select * from ReportData'
> ) -- will be called F1, F2, F3, ... if there are no headers.
> If it's just a sheet, try
> insert into MyTable
> select <columns> from OpenRowset(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\Report.xls',
> 'select * from [Sheet1$]'
> )
> The $ is added to the sheet name, which itself has no $ character.
> There are a few other things to watch out for, like type conversion,
> and you can find other examples in these threads if the ones above
> don't help.
> http://groups.google.com/groups?hl=...cel%2Bsqlserver
> Also, MSDN has a little more information than Books Online about this.
> -- Steve Kass
> -- Drew University
>
>
> bill_morgan wrote:
>
>

Bulk Insert Excel File

Hi there,
What parameters do I need to define in order to bulk insert data in an
Excel file into a SQL table?
FIELDTERMINATOR = ?
ROW TERMINATOR = ?
Are there any other parameters I need to specify?
Thanks!What version of SQL are you using? If you are using 2000, then create
a DTS package. If it's 2005, then an SSIS package/job.
On May 22, 3:57 pm, Yvette <yvetteyc...@.gmail.com> wrote:
> Hi there,
> What parameters do I need to define in order to bulk insert data in an
> Excel file into a SQL table?
> FIELDTERMINATOR = ?
> ROW TERMINATOR = ?
> Are there any other parameters I need to specify?
> Thanks!

Bulk Insert Excel File

Hi there,
What parameters do I need to define in order to bulk insert data in an
Excel file into a SQL table?
FIELDTERMINATOR = ?
ROW TERMINATOR = ?
Are there any other parameters I need to specify?
Thanks!What version of SQL are you using? If you are using 2000, then create
a DTS package. If it's 2005, then an SSIS package/job.
On May 22, 3:57 pm, Yvette <yvetteyc...@.gmail.com> wrote:
> Hi there,
> What parameters do I need to define in order to bulk insert data in an
> Excel file into a SQL table?
> FIELDTERMINATOR = ?
> ROW TERMINATOR = ?
> Are there any other parameters I need to specify?
> Thanks!

Bulk Insert Excel File

Hi there,
What parameters do I need to define in order to bulk insert data in an
Excel file into a SQL table?
FIELDTERMINATOR = ?
ROW TERMINATOR = ?
Are there any other parameters I need to specify?
Thanks!
What version of SQL are you using? If you are using 2000, then create
a DTS package. If it's 2005, then an SSIS package/job.
On May 22, 3:57 pm, Yvette <yvetteyc...@.gmail.com> wrote:
> Hi there,
> What parameters do I need to define in order to bulk insert data in an
> Excel file into a SQL table?
> FIELDTERMINATOR = ?
> ROW TERMINATOR = ?
> Are there any other parameters I need to specify?
> Thanks!

Bulk copy to publication

Hello

I need to copy from excel table to SQL 2005 publication (many record > 1000)

How can i do this. Bulk copy? or ...

I have experience problem with bulk copy so how can i manange it for my own needs.

right click on the database you wish to import data to. Select Tasks, and Import Data, next, and for your data source, select Excel, browse to find your Excel spread sheet, click next, next, next, next, and Finish.

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