bulk sql insert task can do tables with identity?

i got some bulk insert tasks in SSIS inserting into some tables with identity set ON....

in 1 column. Can the bulk insert task go smoothly?

PS: i cannot find anywhere in the bulk insert task that can set the ignore identity columns...........

now my steps are prepare database -> create database -> bulk insert into tables

working on my previous problem, Jamie.

Open the Bulk Insert Task, select the Options page, select Options, open the drop down. Check "Enable Identity Insert".

bulk load: no error message, but not loading

hey guys, my xml bulk load creates no error message, but does not populate my tables.
I am very new to xml and sql server, so maybe this is a simple problem?
i have a root element i dont want to map, and then several tables of data in one document. I am unconcerned with any key constraints at this point.this board wont let me post all my data at once, so one at a time...
heres how i call the load
****************************
Function Main()
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=localhost;" & _
"DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:\myschema", "C:\myfile"
oXMLBulkLoad.SchemaGen = True
Set oXMLBulkLoad = Nothing
Main = DTSTaskExecResult_success
End Function
***************************************|||ill try attaching my schemasql

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 and SQL function defaults

Is it possible to include a field default in the schema file that represent
a database function?
For example, all of our SQL tables includes the following fields...
CreateDate datetime NOT NULL DEFAULT (getdate())
CreateUser char (255) NOT NULL DEFAULT (suser_sname())
When creating our schema we tried the following:
<xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
/>
<xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
/>
The "CreateDate" element fails with an "Invalid character value for cast
specification." error, while the second element will insert the string value
'suser_sname()' into the "CreateUser" field.
Thanks in advance
No, this is not possible. The default is an XML schema default clause and
cannot contain an T-SQL expression.
Instead, define a default on the relational table column to which you map
the element and make sure that there is no value added.
Best regards
Michael
"Cipher" <c@.c.com> wrote in message
news:OU8QUY0TEHA.3988@.tk2msftngp13.phx.gbl...
> Is it possible to include a field default in the schema file that
> represent
> a database function?
> For example, all of our SQL tables includes the following fields...
> CreateDate datetime NOT NULL DEFAULT (getdate())
> CreateUser char (255) NOT NULL DEFAULT (suser_sname())
> When creating our schema we tried the following:
> <xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
> />
> <xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
> />
> The "CreateDate" element fails with an "Invalid character value for cast
> specification." error, while the second element will insert the string
> value
> 'suser_sname()' into the "CreateUser" field.
>
> Thanks in advance
>
sql

Bulk load and passing parameters in

I'm doing a Bulk Load of an XML file.
The file consists of a parent/child relationship joined by an interger
column in both tables.
The parent table may already have some values in it.
How can I pass in the starting value and also have it auto increment for
each element ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netHello Michael,
If I understand this correctly, you parent table has a column (int)
referenced by a clild table. The parent table have some rows. You want to
change the column to identiy, and pass a starting value to this column. If
I'm off-base, please let me know.
Based on my research, you could not pass starting value or change column
propperty via mapping file. You may want to use "alter table" to change the
column. You could change this in Enterprise Manager, or you need to write
a sql code to do this by creating a temp table, copy from original table
when SET IDENTITY_INSERT is set to on, and then rename the temp table to
the original one. You may want to use profiler to capture the trace when
changing this in Enterprise Manager
You may want to refer to the following article to for some related
information
Using XML Bulk Load with Identity Columns
http://www.sqlmag.com/Article/Artic...rver_40239.html
Hope this is helpful. Please post back if you have further questions.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Bulk Insert statement with JOIN !!!

Hi,

I have two tables. Employees and departments as follows:

Employees
- Name
- Salary
- Department ID

Departments
-Department ID
-Department Name

Now what I am doing is that I am allowing the user to give me a CSV file with all the employees data to bulk insert it in the table Employees as follows:

Employee Name, Salary, Department Name

The problem is that the users know nothing about the department ID. So what they type in the CSV ffile as you can see above is the department name.

The SQL statement I normally use for bulk insert is:

BULK INSERT CAS.dbo.employees

FROM 'c:\list.csv' WITH

(FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

My question is, how can I use the same technique but insert the departments IDs not name as in the CSV file.!!!!

Appreciate your help.
Thanks

If you use SQL Server 2005 you could use OPENROWSET function with BULK option. OPENROWSET if Table-value function, so you could use it wiht JOIN. Then use INSERT INTO:

Code Snippet

create table Employees(

Name nvarchar(20),

Salary decimal,

DepartmentID int

)

go

create table Departments

(

DepartmentID int,

DepartmentName nvarchar(20)

)

go

insert into Departments values(1,'Dep1')

insert into Departments values(2,'Dep2')

--Select only

select Name, Salary, DepartmentID

from OPENROWSET(BULK 'C:\emp.txt', FORMATFILE='C:\format_file.txt') emp

JOIN Departments dep on (emp.DepartmentName=dep.DepartmentName)

--Insert data into Employees table

insert into Employees

select Name, Salary, DepartmentID

from OPENROWSET(BULK 'C:\emp.txt', FORMATFILE='C:\format_file.txt') emp

JOIN Departments dep on (emp.DepartmentName=dep.DepartmentName)

My CSV file:

Code Snippet

User1,100,Dep1
User2,1000,Dep2

My format file:

Code Snippet

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/formathttp://schemas.microsoft.com/sqlserver/2004/bulkload/format">http://schemas.microsoft.com/sqlserver/2004/bulkload/format</A< A>>" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
'>http://www.w3.org/2001/XMLSchema-instance">http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
; <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Name" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="Salary" xsi:type="SQLDECIMAL"/>
<COLUMN SOURCE="3" NAME="DepartmentName" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

|||Thanks, that does it.

Cheers

Bulk Insert Related Tables - PKs

Hi all,
I am in the process of building a SP to copy data from 3 temp tables into
production tables.
Say I have the following tables:
TempTable 1
TempTable 2
TempTable 3
TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
If I were to bulk Insert TempTable 1 into another table - how do I grab the
newly created PKs so I can fetch, bulk insert the related rows in TempTable
2 and TempTable 3?
I believe in SQLXML this feature is called ID propogation? Is it supported
in standard SQL?
Or do I basically have to loop through each of the entires in TempTable
2/3?
Thanks.Spam Catcher wrote:
> Hi all,
> I am in the process of building a SP to copy data from 3 temp tables into
> production tables.
> Say I have the following tables:
> TempTable 1
> TempTable 2
> TempTable 3
>
> TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
> If I were to bulk Insert TempTable 1 into another table - how do I grab th
e
> newly created PKs so I can fetch, bulk insert the related rows in TempTabl
e
> 2 and TempTable 3?
> I believe in SQLXML this feature is called ID propogation? Is it supported
> in standard SQL?
> Or do I basically have to loop through each of the entires in TempTable
> 2/3?
> Thanks.
Here's an example using Employees and Departments as the related
tables.
CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES Departments (deptid));
CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
deptname VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES New_Departments (deptid));
INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D ;
INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
news:1144304833.967938.155300@.v46g2000cwv.googlegroups.com:

> Here's an example using Employees and Departments as the related
> tables.
> CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
> INTEGER NOT NULL REFERENCES Departments (deptid));
> CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
> ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL,
> deptid INTEGER NOT NULL REFERENCES New_Departments (deptid));
> INSERT INTO New_Departments (deptname)
> SELECT D.deptname
> FROM Departments AS D ;
> INSERT INTO New_Employees (ssn, employeename, deptid)
> SELECT E1.ssn, E1.employeename, D2.deptid
> FROM Employees AS E1
> JOIN Departments AS D1
> ON E1.deptid = D1.deptid
> JOIN New_Departments AS D2
> ON D1.deptname = D2.deptname ;
>
Thanks for your help. So this assumes the original tables had unique
data... what if I'm relying on the original temp PK to be unique - rather
than something like the department name?
In this case, would I have to loop over each record?
Thanks : )|||Spam Catcher wrote:
> Thanks for your help. So this assumes the original tables had unique
> data... what if I'm relying on the original temp PK to be unique - rather
> than something like the department name?
> In this case, would I have to loop over each record?
>
Uniqueness in the source data isn't essential - you can clear that up
with DISTINCT. Of course you do need alternate keys in the target
tables. You should always have those in any case. IDENTITY should not
be the only key of a table if you've got your logical design correct.
If you are forced to make a mess of it then looping is probably one way
to do it ;-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

bulk insert question

Hi! We are in the process of adding few columns to our large tables (200
million rows) and altering few columns from varchar to char. One of the
option I am thinking is to bcp data out, change the schema and bulk insert
data in. Looks like with either bcp.exe or bulk insert command, you won't be
able to load the data in, if the schema of table get changed. Is this true?
I know that I can use DTS export/import to do this task but since bulk
insert is the fastest method I would like to try that option if possible.
Besides, Bulk insert I could also change schema with Alter table command. I
don't know if thats better than unloading/change/reload method that I
mentioned above.
I would appreiciate it, if anyone who have export/change schema/import large
table, give me some direction here.
thanks
If you use the Native mode I don't think you can do it but have you actually
tried? BCP out a few thousand rows, create anew table and Bulk Insert it
back in. If native wont work I am pretty sure char mode will.
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> Hi! We are in the process of adding few columns to our large tables (200
> million rows) and altering few columns from varchar to char. One of the
> option I am thinking is to bcp data out, change the schema and bulk insert
> data in. Looks like with either bcp.exe or bulk insert command, you won't
> be
> able to load the data in, if the schema of table get changed. Is this
> true?
> I know that I can use DTS export/import to do this task but since bulk
> insert is the fastest method I would like to try that option if possible.
> Besides, Bulk insert I could also change schema with Alter table command.
> I
> don't know if thats better than unloading/change/reload method that I
> mentioned above.
> I would appreiciate it, if anyone who have export/change schema/import
> large
> table, give me some direction here.
> thanks
>
|||I have tried both native and character mode and both erroed out. Which is
kind of expected, since schema got changed, the program doesn't have any way
of knowing which column in datafile (native or char) maps to which column in
table.
I haven't tried format file so far, which is what I am going to do next and
see if I can alter format file and make this thing work.
Thanks for your Input.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> If you use the Native mode I don't think you can do it but have you
actually[vbcol=seagreen]
> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
> back in. If native wont work I am pretty sure char mode will.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
insert[vbcol=seagreen]
won't[vbcol=seagreen]
possible.[vbcol=seagreen]
command.
>
|||Yes if you change the columns around you need to use a format file. But I
don't see why it wont work with the format file.
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>I have tried both native and character mode and both erroed out. Which is
> kind of expected, since schema got changed, the program doesn't have any
> way
> of knowing which column in datafile (native or char) maps to which column
> in
> table.
> I haven't tried format file so far, which is what I am going to do next
> and
> see if I can alter format file and make this thing work.
> Thanks for your Input.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> actually
> insert
> won't
> possible.
> command.
>
|||With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ov1hlWrFHA.3352@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Yes if you change the columns around you need to use a format file. But I
> don't see why it wont work with the format file.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
column[vbcol=seagreen]
it[vbcol=seagreen]
the[vbcol=seagreen]
bulk[vbcol=seagreen]
schema/import
>

bulk insert question

Hi! We are in the process of adding few columns to our large tables (200
million rows) and altering few columns from varchar to char. One of the
option I am thinking is to bcp data out, change the schema and bulk insert
data in. Looks like with either bcp.exe or bulk insert command, you won't be
able to load the data in, if the schema of table get changed. Is this true?
I know that I can use DTS export/import to do this task but since bulk
insert is the fastest method I would like to try that option if possible.
Besides, Bulk insert I could also change schema with Alter table command. I
don't know if thats better than unloading/change/reload method that I
mentioned above.
I would appreiciate it, if anyone who have export/change schema/import large
table, give me some direction here.
thanksIf you use the Native mode I don't think you can do it but have you actually
tried? BCP out a few thousand rows, create anew table and Bulk Insert it
back in. If native wont work I am pretty sure char mode will.
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> Hi! We are in the process of adding few columns to our large tables (200
> million rows) and altering few columns from varchar to char. One of the
> option I am thinking is to bcp data out, change the schema and bulk insert
> data in. Looks like with either bcp.exe or bulk insert command, you won't
> be
> able to load the data in, if the schema of table get changed. Is this
> true?
> I know that I can use DTS export/import to do this task but since bulk
> insert is the fastest method I would like to try that option if possible.
> Besides, Bulk insert I could also change schema with Alter table command.
> I
> don't know if thats better than unloading/change/reload method that I
> mentioned above.
> I would appreiciate it, if anyone who have export/change schema/import
> large
> table, give me some direction here.
> thanks
>|||I have tried both native and character mode and both erroed out. Which is
kind of expected, since schema got changed, the program doesn't have any way
of knowing which column in datafile (native or char) maps to which column in
table.
I haven't tried format file so far, which is what I am going to do next and
see if I can alter format file and make this thing work.
Thanks for your Input.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> If you use the Native mode I don't think you can do it but have you
actually
> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
> back in. If native wont work I am pretty sure char mode will.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
insert[vbcol=seagreen]
won't[vbcol=seagreen]
possible.[vbcol=seagreen]
command.[vbcol=seagreen]
>|||Yes if you change the columns around you need to use a format file. But I
don't see why it wont work with the format file.
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>I have tried both native and character mode and both erroed out. Which is
> kind of expected, since schema got changed, the program doesn't have any
> way
> of knowing which column in datafile (native or char) maps to which column
> in
> table.
> I haven't tried format file so far, which is what I am going to do next
> and
> see if I can alter format file and make this thing work.
> Thanks for your Input.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> actually
> insert
> won't
> possible.
> command.
>|||With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ov1hlWrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Yes if you change the columns around you need to use a format file. But I
> don't see why it wont work with the format file.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
column[vbcol=seagreen]
it[vbcol=seagreen]
the[vbcol=seagreen]
bulk[vbcol=seagreen]
schema/import[vbcol=seagreen]
>sql

bulk insert question

Hi! We are in the process of adding few columns to our large tables (200
million rows) and altering few columns from varchar to char. One of the
option I am thinking is to bcp data out, change the schema and bulk insert
data in. Looks like with either bcp.exe or bulk insert command, you won't be
able to load the data in, if the schema of table get changed. Is this true?
I know that I can use DTS export/import to do this task but since bulk
insert is the fastest method I would like to try that option if possible.
Besides, Bulk insert I could also change schema with Alter table command. I
don't know if thats better than unloading/change/reload method that I
mentioned above.
I would appreiciate it, if anyone who have export/change schema/import large
table, give me some direction here.
thanksIf you use the Native mode I don't think you can do it but have you actually
tried? BCP out a few thousand rows, create anew table and Bulk Insert it
back in. If native wont work I am pretty sure char mode will.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> Hi! We are in the process of adding few columns to our large tables (200
> million rows) and altering few columns from varchar to char. One of the
> option I am thinking is to bcp data out, change the schema and bulk insert
> data in. Looks like with either bcp.exe or bulk insert command, you won't
> be
> able to load the data in, if the schema of table get changed. Is this
> true?
> I know that I can use DTS export/import to do this task but since bulk
> insert is the fastest method I would like to try that option if possible.
> Besides, Bulk insert I could also change schema with Alter table command.
> I
> don't know if thats better than unloading/change/reload method that I
> mentioned above.
> I would appreiciate it, if anyone who have export/change schema/import
> large
> table, give me some direction here.
> thanks
>|||I have tried both native and character mode and both erroed out. Which is
kind of expected, since schema got changed, the program doesn't have any way
of knowing which column in datafile (native or char) maps to which column in
table.
I haven't tried format file so far, which is what I am going to do next and
see if I can alter format file and make this thing work.
Thanks for your Input.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> If you use the Native mode I don't think you can do it but have you
actually
> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
> back in. If native wont work I am pretty sure char mode will.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> > Hi! We are in the process of adding few columns to our large tables (200
> > million rows) and altering few columns from varchar to char. One of the
> > option I am thinking is to bcp data out, change the schema and bulk
insert
> > data in. Looks like with either bcp.exe or bulk insert command, you
won't
> > be
> > able to load the data in, if the schema of table get changed. Is this
> > true?
> > I know that I can use DTS export/import to do this task but since bulk
> > insert is the fastest method I would like to try that option if
possible.
> > Besides, Bulk insert I could also change schema with Alter table
command.
> > I
> > don't know if thats better than unloading/change/reload method that I
> > mentioned above.
> > I would appreiciate it, if anyone who have export/change schema/import
> > large
> > table, give me some direction here.
> >
> > thanks
> >
> >
>|||Yes if you change the columns around you need to use a format file. But I
don't see why it wont work with the format file.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>I have tried both native and character mode and both erroed out. Which is
> kind of expected, since schema got changed, the program doesn't have any
> way
> of knowing which column in datafile (native or char) maps to which column
> in
> table.
> I haven't tried format file so far, which is what I am going to do next
> and
> see if I can alter format file and make this thing work.
> Thanks for your Input.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>> If you use the Native mode I don't think you can do it but have you
> actually
>> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
>> back in. If native wont work I am pretty sure char mode will.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "james" <kush@.brandes.com> wrote in message
>> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
>> > Hi! We are in the process of adding few columns to our large tables
>> > (200
>> > million rows) and altering few columns from varchar to char. One of the
>> > option I am thinking is to bcp data out, change the schema and bulk
> insert
>> > data in. Looks like with either bcp.exe or bulk insert command, you
> won't
>> > be
>> > able to load the data in, if the schema of table get changed. Is this
>> > true?
>> > I know that I can use DTS export/import to do this task but since bulk
>> > insert is the fastest method I would like to try that option if
> possible.
>> > Besides, Bulk insert I could also change schema with Alter table
> command.
>> > I
>> > don't know if thats better than unloading/change/reload method that I
>> > mentioned above.
>> > I would appreiciate it, if anyone who have export/change schema/import
>> > large
>> > table, give me some direction here.
>> >
>> > thanks
>> >
>> >
>>
>|||With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ov1hlWrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Yes if you change the columns around you need to use a format file. But I
> don't see why it wont work with the format file.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> >I have tried both native and character mode and both erroed out. Which is
> > kind of expected, since schema got changed, the program doesn't have any
> > way
> > of knowing which column in datafile (native or char) maps to which
column
> > in
> > table.
> > I haven't tried format file so far, which is what I am going to do next
> > and
> > see if I can alter format file and make this thing work.
> > Thanks for your Input.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> >> If you use the Native mode I don't think you can do it but have you
> > actually
> >> tried? BCP out a few thousand rows, create anew table and Bulk Insert
it
> >> back in. If native wont work I am pretty sure char mode will.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "james" <kush@.brandes.com> wrote in message
> >> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> >> > Hi! We are in the process of adding few columns to our large tables
> >> > (200
> >> > million rows) and altering few columns from varchar to char. One of
the
> >> > option I am thinking is to bcp data out, change the schema and bulk
> > insert
> >> > data in. Looks like with either bcp.exe or bulk insert command, you
> > won't
> >> > be
> >> > able to load the data in, if the schema of table get changed. Is this
> >> > true?
> >> > I know that I can use DTS export/import to do this task but since
bulk
> >> > insert is the fastest method I would like to try that option if
> > possible.
> >> > Besides, Bulk insert I could also change schema with Alter table
> > command.
> >> > I
> >> > don't know if thats better than unloading/change/reload method that I
> >> > mentioned above.
> >> > I would appreiciate it, if anyone who have export/change
schema/import
> >> > large
> >> > table, give me some direction here.
> >> >
> >> > thanks
> >> >
> >> >
> >>
> >>
> >
> >
>

BULK INSERT permissions for non-admin?

I've got a user, "joe", logging into a SqlServer 2005 database using
sqlserver authentication. Joe can create tables, insert, select, drop --
whatever -- but when he tries to do a bulk insert, he gets:
"The current user is not the database or object owner of table 'myTable'.
Cannot perform SET operation."
WARNING: I don't know much about SQL Server permissions administration. I
write queries. :)
When I look at the database in SQL Server Management Studio, I see that the
table is in the schema "dbo". What's the safest way to give Joe permission
to do a bulk insert into this table?
Thanks!"Jesse" <nospam@.nospam.com> wrote in message
news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
> I've got a user, "joe", logging into a SqlServer 2005 database using
> sqlserver authentication. Joe can create tables, insert, select, drop --
> whatever -- but when he tries to do a bulk insert, he gets:
> "The current user is not the database or object owner of table 'myTable'.
> Cannot perform SET operation."
> WARNING: I don't know much about SQL Server permissions administration. I
> write queries. :)
> When I look at the database in SQL Server Management Studio, I see that
> the table is in the schema "dbo". What's the safest way to give Joe
> permission to do a bulk insert into this table?
>
The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and perhaps
ALTER TABLE. In addition if the user is connected using SQL Server
authentication then the SQL Server account must be able to read the file.
If the user is authenticated by Windows Integrated Authentication, then the
user, not the SQL Server account, needs to be able to read the file.
It's all in the BOL:
BULK INSERT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
Security Considerations for Using Transact-SQL to Bulk Import Data
http://msdn2.microsoft.com/en-us/library/ms186286.aspx
David|||David --
Thanks for the quick reply! Unfortunately, I must still be missing
something -- I've gone through and granted everything I can think of, but I
still get the same error.
Specifically, here's what I did:
grant alter to [joe]
grant ADMINISTER BULK OPERATIONS to [joe]
grant insert to [joe]
Then, from Server Management Studio,
add the bulkadmin role to joe
add the symin role to joe
add the db_ddladmin role to joe
add the db_owner role to joe
Joe now has the power to destroy the whole database -- but not to bulk
insert. I still get, "The current user is not the database or object owner
of table 'myTable'. Cannot perform SET operation."
What else could I be missing?
Thanks again for your help!
Jesse
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OusnToXZGHA.428@.TK2MSFTNGP02.phx.gbl...
> "Jesse" <nospam@.nospam.com> wrote in message
> news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
>
> The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and
> perhaps ALTER TABLE. In addition if the user is connected using SQL
> Server authentication then the SQL Server account must be able to read the
> file. If the user is authenticated by Windows Integrated Authentication,
> then the user, not the SQL Server account, needs to be able to read the
> file.
> It's all in the BOL:
>
> BULK INSERT (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
>
> Security Considerations for Using Transact-SQL to Bulk Import Data
> http://msdn2.microsoft.com/en-us/library/ms186286.aspx
>
>
> David
>
>|||One more detail:
- if I log into the database using Server Management Studio, I can run the
bulk insert OK.
- if I try to run it from an ASP.NET script, I get the permissions error.
The asp.net script is connecting to the database with the same credentials I
use to log in via SMS! Something must be working differently, but what?
This is driving me nuts. Thanks for any help you can give!
Here's how my asp.net script connects:
<add name="production" connectionString="data source=myLocalMachine; initial
catalog=myCatalog; user=joe; password=joe;"
providerName="System.Data.SqlClient"/>
"Jesse" <nospam@.nospam.com> wrote in message
news:e8jp30XZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> David --
> Thanks for the quick reply! Unfortunately, I must still be missing
> something -- I've gone through and granted everything I can think of, but
> I still get the same error.
> Specifically, here's what I did:
> grant alter to [joe]
> grant ADMINISTER BULK OPERATIONS to [joe]
> grant insert to [joe]
> Then, from Server Management Studio,
> add the bulkadmin role to joe
> add the symin role to joe
> add the db_ddladmin role to joe
> add the db_owner role to joe
> Joe now has the power to destroy the whole database -- but not to bulk
> insert. I still get, "The current user is not the database or object owner
> of table 'myTable'. Cannot perform SET operation."
> What else could I be missing?
> Thanks again for your help!
>
> Jesse|||"Jesse" <nospam@.nospam.com> wrote in message
news:eiNZPSYZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> One more detail:
> - if I log into the database using Server Management Studio, I can run the
> bulk insert OK.
> - if I try to run it from an ASP.NET script, I get the permissions error.
> The asp.net script is connecting to the database with the same credentials
> I use to log in via SMS! Something must be working differently, but what?
> This is driving me nuts. Thanks for any help you can give!
>
> Here's how my asp.net script connects:
> <add name="production" connectionString="data source=myLocalMachine;
> initial catalog=myCatalog; user=joe; password=joe;"
> providerName="System.Data.SqlClient"/>
>
Strange.
This works for me.
Run this as a symin. I have a database called test and xp_cmdshell
enabled.
You might run Profiler to see what's actually being sent from the web site.
David
--setup.sql--
use test
master..xp_cmdshell 'dir > c:\data\foo.txt'
master..xp_cmdshell 'type c:\data\foo.txt'
create table LoadTable(data varchar(4000))
bulk insert LoadTable from 'c:\data\foo.txt'
select * from LoadTable
truncate table LoadTable
go
use master
create login Joe with password='Joe'
grant administer bulk operations to Joe
go
use test
create user Joe for login Joe
grant view definition on schema::dbo to Joe
grant select,insert,delete on LoadTable to Joe
--end setup.sql--
then run this .NET program
--Program.cs--
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Data.SqlClient;
namespace csTest
{
public static class Program
{
public static void Main()
{
SqlConnectionStringBuilder cb1 = new SqlConnectionStringBuilder();
cb1.DataSource = "192.168.2.10";
cb1.IntegratedSecurity = false;
cb1.UserID = "Joe";
cb1.Password = "Joe";
cb1.InitialCatalog = "Test";
using (SqlConnection con = new SqlConnection(cb1.ConnectionString))
{
con.Open();
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += new SqlInfoMessageEventHandler(con_InfoMessa
ge);
new SqlCommand(@."delete from LoadTable", con).ExecuteNonQuery();
new SqlCommand(@."bulk insert LoadTable from
'c:\data\foo.txt'",con).ExecuteNonQuery();
using (SqlDataReader rdr = new SqlCommand("select * from
LoadTable",con).ExecuteReader())
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
Console.ReadLine();
}
static void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine(e.Message);
}
}
}
--end Program.cs---

bulk insert optimization

Hi,
I need to use the BULK INSERT for pushing data into two of the tables
(PK=datetime + int + int). I had read about the optimizations that can be
done for this. But since my tables will keep growing with every inserts (in
GBs), droping & creating the indexes (clustered and non-clustered) itself
can be time comsuming. Are there other options for optimizations?
TIA
AjeyOne thing you can do is to have the file sorted in the same order as the
primary key or clustered index, and then specify the ORDER parameter of BULK
INSERT command. Have you read about it?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ajey" <ajey5@.hotmail.com> wrote in message
news:%235f4bHtsFHA.3080@.TK2MSFTNGP15.phx.gbl...
Hi,
I need to use the BULK INSERT for pushing data into two of the tables
(PK=datetime + int + int). I had read about the optimizations that can be
done for this. But since my tables will keep growing with every inserts (in
GBs), droping & creating the indexes (clustered and non-clustered) itself
can be time comsuming. Are there other options for optimizations?
TIA
Ajey|||Yes.
Instead of bulk inserting the data directly to the target table what i am
planing to do is:
- copy the file to the target sql server
- bulk insert into a temp table (same schema but no indexes)
- insert into the target table from the temp table
will this be efficient?
TIA
- Ajey
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:upHv44tsFHA.3040@.TK2MSFTNGP14.phx.gbl...
> One thing you can do is to have the file sorted in the same order as the
> primary key or clustered index, and then specify the ORDER parameter of
> BULK
> INSERT command. Have you read about it?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:%235f4bHtsFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I need to use the BULK INSERT for pushing data into two of the tables
> (PK=datetime + int + int). I had read about the optimizations that can be
> done for this. But since my tables will keep growing with every inserts
> (in
> GBs), droping & creating the indexes (clustered and non-clustered) itself
> can be time comsuming. Are there other options for optimizations?
> TIA
> Ajey
>
>|||Ajey <ajey5@.hotmail.com> wrote:
> Yes.
> Instead of bulk inserting the data directly to the target table what
> i am planing to do is:
> - copy the file to the target sql server
> - bulk insert into a temp table (same schema but no indexes)
> - insert into the target table from the temp table
> will this be efficient?
You should measure it for a definite answer. Personally I don't believe
this will be faster than a direct bulk load but you never know.
robert
> TIA
> - Ajey
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:upHv44tsFHA.3040@.TK2MSFTNGP14.phx.gbl...

bulk insert on remote computer

Hi there!
I am using bulk insert to enter data in tables. I try to bulk insert
data on a remote server but the bulk insert statement must access
files located on localhost computer (from where I query a bulk insert
with query analyser)..and I get a 'access denied' from sqlserver
the only way I found to prevent this, is to log on the remote computer
as the same user that the local computer, to give permissions...
is there any way to do this'
thanks a lot :)
++You need to put the file in some location accessible from the server.
If that location is on the network then make sure you specify the UNC
path name rather than use drive letters.
David Portas
SQL Server MVP
--|||On 26 Apr 2005 07:28:18 -0700, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
yes, I use UNC path..but I guess this problem is due to a windows
configuration..I mean I must define the same user on both computers to
allow one to access to other whatever programs are used...
++
Vince

>You need to put the file in some location accessible from the server.
>If that location is on the network then make sure you specify the UNC
>path name rather than use drive letters.|||Just give the access rights to whatever account you use for the SQL
Server service. If you don't know what account that is then check the
Log On properties of MSSQLSERVER in the Services dialog under Control
Panel.
David Portas
SQL Server MVP
--

Bulk Insert of multiple, associated tables

Hi, hope this is the right place for me to post this, but I couldn't find a
more appropriate group.
In a C# app, I have a large set of structured data that will map to multiple
tables, associated with foreign keys, similar in structure to:
The xxxID columns are Identity columns
TABLE Item (ItemID )
TABLE Exchange ( ExchangeID )
TABLE Batch( BatchID, fkExchange )
TABLE Group( GroupID, fkBatch )
TABLE Transaction ( TransactionID, fkGroup )
TABLE ItemToTransactionVector ( fkItem, fkTransaction )
'Item' rows already exist, and I'm creating the rest in my app to be added
to the database en-mass. Exchanges have a single Batch;Batches have multipl
e
Groups; Groups have multiple Transactions, etc.
For a single table, I would use the ADODB SqlBulkCopy class, unfortunately,
I need to set the foreign keys between the tables.
At the moment, I have an initial implementation that uses individual
inserts, but this is way too slow, even wrapping them inside a transaction.
My thoughts after a little research were:
-Create matching temporary tables, or permanent staging tables for each of
those to be imported.
-Preset the row data with 'fake' foreign keys in my application, to maintain
the relationship between the rows in different tables.
-Process the data somehow from the temporary tables into the real tables
Is this a ridiculous scheme? How else can I insert all that data and
maintain all the relationships - and do it fast?
Thanks! Ross"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:DE44B64C-5790-4D15-A35F-E82ECA15FF9A@.microsoft.com...
> Hi, hope this is the right place for me to post this, but I couldn't find
> a
> more appropriate group.
> In a C# app, I have a large set of structured data that will map to
> multiple
> tables, associated with foreign keys, similar in structure to:
> The xxxID columns are Identity columns
> TABLE Item (ItemID )
> TABLE Exchange ( ExchangeID )
> TABLE Batch( BatchID, fkExchange )
> TABLE Group( GroupID, fkBatch )
> TABLE Transaction ( TransactionID, fkGroup )
> TABLE ItemToTransactionVector ( fkItem, fkTransaction )
> 'Item' rows already exist, and I'm creating the rest in my app to be added
> to the database en-mass. Exchanges have a single Batch;Batches have
> multiple
> Groups; Groups have multiple Transactions, etc.
> For a single table, I would use the ADODB SqlBulkCopy class,
> unfortunately,
> I need to set the foreign keys between the tables.
> At the moment, I have an initial implementation that uses individual
> inserts, but this is way too slow, even wrapping them inside a
> transaction.
> My thoughts after a little research were:
> -Create matching temporary tables, or permanent staging tables for each of
> those to be imported.
> -Preset the row data with 'fake' foreign keys in my application, to
> maintain
> the relationship between the rows in different tables.
> -Process the data somehow from the temporary tables into the real tables
> Is this a ridiculous scheme? How else can I insert all that data and
> maintain all the relationships - and do it fast?
> Thanks! Ross
I can't help you with the ADO bulk load part but here's an example of how
you can load the data into a table variable and then populate the related
tables. Depending on the size of the tables you may prefer to try a temp
table or a permanent one.
/* Sample tables */
CREATE TABLE T1 (x INT IDENTITY PRIMARY KEY, z VARCHAR(10) NOT NULL UNIQUE);
CREATE TABLE T2 (x INT NOT NULL REFERENCES T1 (x), z1 VARCHAR(10) NOT NULL,
PRIMARY KEY (x,z1));
/* temp table variable */
DECLARE @.t TABLE (z VARCHAR(10) NOT NULL, z1 VARCHAR(10) NOT NULL, PRIMARY
KEY (z,z1));
/* Insert the raw data */
INSERT INTO @.t (z,z1)
SELECT 'XX', 'ABC' UNION ALL
SELECT 'XXX', 'DEF' UNION ALL
SELECT 'XXX', 'GHI' ;
/* Populate the parent table */
INSERT INTO T1 (z)
SELECT DISTINCT z
FROM @.t;
/* Join in the foreign key and populate the referencing table */
INSERT INTO T2 (x,z1)
SELECT T1.x, T.z1
FROM @.t AS T
JOIN T1
ON T.z = T1.z;
/* Result */
SELECT * FROM T1;
SELECT * FROM T2;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for you time David. I'll look at adapting that and see how well it
works for my case.
Regards,
Ross
"David Portas" wrote:

> "Ross" <Ross@.discussions.microsoft.com> wrote in message
...
> I can't help you with the ADO bulk load part but here's an example of how
> you can load the data into a table variable and then populate the related
> tables. Depending on the size of the tables you may prefer to try a temp
> table or a permanent one.
>
...

> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>

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 into partition view (sql 2000)

Hi, experts
I have ~ 50 table with the same structure in 4 databases. These tables are
union'ed in partition view.
Insert into partition view works perfect. I would like to load data into PV
with bulk insert from text file. But there is the restriction of PV - I
cannot do the bulk load into PV.
Then I created 3'rd table - and created the "instaed of insert" trigger.
This trigger do the insert into PV from inserted table. But I get the same
error.
Is it possible to implement insert into PV from text file using sql 2000 or
even sql 2005. Thanks in advance.
Ramunas BalukonisRamunas
ad for as I know, you should be able to do from bulk file also. Your problem
prablbly be with constraints. see especially that all NOT NULL columns are
updated and .txt is in proper format.
--
Regards
R.D
--Knowledge gets doubled when shared
"Ramunas Balukonis" wrote:

> Hi, experts
> I have ~ 50 table with the same structure in 4 databases. These tables are
> union'ed in partition view.
> Insert into partition view works perfect. I would like to load data into P
V
> with bulk insert from text file. But there is the restriction of PV - I
> cannot do the bulk load into PV.
> Then I created 3'rd table - and created the "instaed of insert" trigger.
> This trigger do the insert into PV from inserted table. But I get the same
> error.
> Is it possible to implement insert into PV from text file using sql 2000 o
r
> even sql 2005. Thanks in advance.
> Ramunas Balukonis
>
>|||RD, thanks for answer!
but when I do bulk insert into table directly, bulk insert success! So, the
problem is about bulk inserting into PV. Now I'm looking for a workaround.
Thanks
Ramunas
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:1FC22B8E-5B9C-4978-8DFA-63766189AA27@.microsoft.com...
> Ramunas
> ad for as I know, you should be able to do from bulk file also. Your
problem
> prablbly be with constraints. see especially that all NOT NULL columns are
> updated and .txt is in proper format.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ramunas Balukonis" wrote:
>
are
PV
same
or|||I found solution!
I do insert into PV using linked servers!
Ramunas
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1128423828.988065@.loger.vpmarket.int...
> RD, thanks for answer!
> but when I do bulk insert into table directly, bulk insert success! So,
the
> problem is about bulk inserting into PV. Now I'm looking for a workaround.
> Thanks
> Ramunas
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:1FC22B8E-5B9C-4978-8DFA-63766189AA27@.microsoft.com...
> problem
are
> are
into
> PV
I
trigger.
> same
2000
> or
>

Bulk Insert Import Question

Hi:
I am importing data from text files in sql tables using bulk insert object
in dts package. First row of text files contains headings. For some text
files, headings are also imported in the tables and for some headings are no
t
imported. I am not sure why is that happening.
I do not want to import headings. Is there anyway to tell bulk insert
object to not import first row (headings)? Please let me know.
Thanks a lotBULK INSERT [YourTable] FROM 'c:\yourfile.txt'
WITH (FIRSTROW = 5)
Where 5 indicates "start importing from the 5th row of text". Adjust for
your import as necessary. Don't use quotation marks around the number.
"Mike" wrote:

> Hi:
> I am importing data from text files in sql tables using bulk insert object
> in dts package. First row of text files contains headings. For some text
> files, headings are also imported in the tables and for some headings are
not
> imported. I am not sure why is that happening.
> I do not want to import headings. Is there anyway to tell bulk insert
> object to not import first row (headings)? Please let me know.
> Thanks a lot|||Thank you very much
"Mark Williams" wrote:
> BULK INSERT [YourTable] FROM 'c:\yourfile.txt'
> WITH (FIRSTROW = 5)
> Where 5 indicates "start importing from the 5th row of text". Adjust for
> your import as necessary. Don't use quotation marks around the number.
> --
>
> "Mike" wrote:
>

Bulk Insert from a table to Another

Hi guys,

Consider this Scenario.

I have two Tables.

Table1-Users

Fields are

id, name,joindate,designation, status

Table2-People

Fields are

id, name, status

The table Users have data in it say 100 records

I have to fill it toPeople Table where id=id and name=name and status=status

Any Way?

Regards,

Naveen

If it's an insert action, why do you need to match the row values?

For a simple insert, you can do:

INSERT INTO [people](id,name, status)SELECT id,name, status-- assumed id is PKFROM [users]WHERE idNOT IN (SELECT idFROM people)-- make sure you don't insert duplicate ID

Bulk Insert doubts

Hello Guys,

soon soon, ll have to devellop some procedures to read an ASCII file to supply MS SQL tables. As Ive read some old post, Ive understand that I have to use BULK INSERT , or else, BCP or DTS. Id like to know the diference between this commands and witch of them is more powerful, faster and efficient. If you can give me some implementation tips, I will be very grateful.

thanx allDid you look it up in BOL...

How is the data stored?

anyway...I almost exclusivley use bcp for Production code

BULK INSERT usually for quick data analysis..

and DTS for analysis, if the data is in Excel, Access, whatever...|||DTS is the most flexible, because it can go from more or less automated (using the wizard), to very task oriented (using the painter), to micro-managing the transfer (using VBA).

BCP and BULK INSERT are both tools that expose the current equivalents to the old Bulk Copy API. They are very efficient, but not extremely flexible or friendly.

-PatP|||But what's the most effecient?

EDIT: And do you not think releasing DTS to a production environment is more painful?|||BCP and BULK INSERT are simply differet front ends to the same code. Performancewise the difference is irrelevant.

I live and breathe in a replicated environment. One of our machines does virtually all of our DTS jobs against an "interface" server that has little or no user load.

We use DTS against our OLTP servers, our DW servers, and a number of "friendly" machines that need data. I've never had any real complaints about it.

-PatP|||Well thanx for all replies... Talking about what I have... Ill have 30 ASCII files to load as I told you, and approximatly 91292,42 KB for each... Itll run in production environment, to synchronize the ambient for a BI system... Which comand should I use?

Originally posted by Brett Kaiser
Did you look it up in BOL...

How is the data stored?

anyway...I almost exclusivley use bcp for Production code

BULK INSERT usually for quick data analysis..

and DTS for analysis, if the data is in Excel, Access, whatever...|||I suggest DTS is best and reliable tool to import those ASCII text files to the database. And even you can schedule the same package if its ongoing BI requirement.

As suggested you should follow books online for all the information.
For DTS specifically keep in touch with http://www.sqldts.com.

Bulk insert and collations

1). I was trying to do bulk insert t temp tables, so because temdb has a
Latin1_General_CI_AS collation, an my database has a
SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
special tables in my database to truncate before bulk inserts.
2). Another question: all fields in bulk insert file created not by me are
nullable varchars,but it is considers an error when there is no fields
terminators in the last row (it is a timestamp) and there is only only row
terminator .
Do I need to fix datafile or I can change something in bulk insert statement
format or settings?On Thu, 26 Jul 2007 07:56:04 -0700, UncleSam89
<UncleSam89@.discussions.microsoft.com> wrote:
>1). I was trying to do bulk insert t temp tables, so because temdb has a
>Latin1_General_CI_AS collation, an my database has a
>SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
>special tables in my database to truncate before bulk inserts.
Sorry, I don't see what the question is. You can specify the
collation for any character column in a table, so there is no reason
to use tempdb just to establish a specific colation.
>2). Another question: all fields in bulk insert file created not by me are
>nullable varchars,but it is considers an error when there is no fields
>terminators in the last row (it is a timestamp) and there is only only row
>terminator .
>Do I need to fix datafile or I can change something in bulk insert statement
>format or settings?
I think you are saying that the last row of the table isn't a valid
data row. In the past I have had a quick-and-dirty command line
program written to fix the data file before loading it.
Roy Harvey
Beacon Falls, CT|||I don't using tempdb to force the particular collation, but to avoid creating
too much bulk crap in my own database. I saw in some of collation relared
threads here the use of collation in the comparisons, but the 'collate'
keyword in create statements didn't help.
Does your answer means that I am right now doing only possible stuff to
resolve my problems?
"Roy Harvey" wrote:
> On Thu, 26 Jul 2007 07:56:04 -0700, UncleSam89
> <UncleSam89@.discussions.microsoft.com> wrote:
> >1). I was trying to do bulk insert t temp tables, so because temdb has a
> >Latin1_General_CI_AS collation, an my database has a
> >SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
> >special tables in my database to truncate before bulk inserts.
> Sorry, I don't see what the question is. You can specify the
> collation for any character column in a table, so there is no reason
> to use tempdb just to establish a specific colation.
> >2). Another question: all fields in bulk insert file created not by me are
> >nullable varchars,but it is considers an error when there is no fields
> >terminators in the last row (it is a timestamp) and there is only only row
> >terminator .
> >Do I need to fix datafile or I can change something in bulk insert statement
> >format or settings?
> I think you are saying that the last row of the table isn't a valid
> data row. In the past I have had a quick-and-dirty command line
> program written to fix the data file before loading it.
> Roy Harvey
> Beacon Falls, CT
>|||On Fri, 27 Jul 2007 06:02:01 -0700, UncleSam89
<UncleSam89@.discussions.microsoft.com> wrote:
>I don't using tempdb to force the particular collation, but to avoid creating
>too much bulk crap in my own database. I saw in some of collation relared
>threads here the use of collation in the comparisons, but the 'collate'
>keyword in create statements didn't help.
>Does your answer means that I am right now doing only possible stuff to
>resolve my problems?
When I need a few staging tables for imported data I generally put
them in the target database with an _Imported suffix on the name. When
I have a LOT of tables for importing data, or the import process
becomes too large or complex, I create a Staging database just for
those tables.
It might be worth mentioning that I never import data directly into
production tables, always into staging tables of some sort. In some
cases I even build history tables for the bulk loaded data and assign
batch identifiers so I can trace back any problems to their origin -
particularly useful when the data source is third party with a track
record of screwing things up.
I never explicitly create any objects on tempdb in production as
tempdb is reinitialized each time SQL Server starts and anything in
tempdb is lost.
Roy Harvey
Beacon Falls, CT