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 multiple rows

Hi
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType> </xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when havin
g
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel Nhttp://msdn.microsoft.com/library/d... />
sqlxml.asp|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm" .

Bulk load multiple rows

Hi
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType></xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when having
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel N
http://msdn.microsoft.com/library/de...exchsqlxml.asp
|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm" .

Bulk insert

I m a newbie in Stored Proc. Here I m working with some stuff for importing the csv files then write those inside to MSSQL Server. However I get confused what steps I shoud take.

Here's the Stored Proc I've written.

Instead of running execute proc_TestIT '11113333', 'V', 'Tony Jones , those records would be kept in a csv file instead with at least 100 records per each csv file. I am wondering if I should use bulk insert and what I should do with the SP I 've written. As only 10 out of all the 20 columns in the interface file would be required for the updates/inserts. I m wondering where I should start.

execute proc_TestIT '11113333', 'V', 'Tony Jones'
CREATE Procedure proc_TestIT

@.Locker_No Varchar(10),
@.Locker_Type Varchar(10),
@.Member_Full_Name Varchar(30)

AS
declare @.Location Varchar(06)
set @.Location=' '
Begin
BEGIN TRANSACTION
SET @.Location = (select Location from tblLockers where Locker_No=@.Locker_No)
IF (@.Locker_Type='P')
begin
Update tblLockerIssues SET Remarks='Damaged' where Locker_No=@.Locker_No
end
IF(@.Locker_Type='V')
begin
insert into tblLockerIssues_VIP values(@.Locker_No,@.Location,'')
end
COMMIT TRANSACTION
RETURN
End
GO

There are several basic building blocks that you can use:

Consider using SSIS (DTS if you are running SQL Server 2000) instead of a stored procedure|||

Hi Dave,

Thanks a lot. For the stuff I m working with, we have to enable users to import the csv files with the use of ASP.

As such imports would trigger inserts/updates in several tables e.g. by check each record's member_type in the inteface files, I m wondering if I should make use of ASP to extract data from those columns I need from the csv files then import into the DB.

Cheers,

Manfred

built-in functions - owner required? (newbie)

Hi All,
Ingres has some functions for converting UUIDs. Specifically, they are
uuid_to_char() and uuid_from_char(). I'm new to MS SQL and would like
to write some built-in functions (or any other solution) that will
allow me to use the same code in MS SQL and Ingres.
Example:
select uuid_to_char(xyz_uuid) from xyz
I have written a simple built-in function to do this and it works just
fine. The one fly in the ointment is that the "database owner" seems
to be required to execute it.
"select uuid_to_char(xyz_uuid) from xyz" yields "'uuid_to_char' is not
a recognized function name".
"select dbo.uuid_to_char(xyz_uuid) from xyz" works just fine.
I have found in the documentation where it says that the owner is
required in this case.
Is there some other technique available to eliminate use of the owner
name?
Thanks!
Troy RudolphThere is, but it's undocumented/unsupported, and I guess that you wouldn't
want to rely on such a technique:
- create the function in master
- function name must start with fn_
- owner must be system_function_schema
- name must be all lowercase
- function should be created when the 'allow updates' server configuration
option is turned on
If you follow all the above requirements, you don't need to owner qualify
it, plus you can invoke it from any database. But again, I wouldn't rely on
an unsupported technique in a production environment.
The only supported way to invoke a scalar UDF in a query is to owner qualify
it, I'm afraid.
BG, SQL Server MVP
www.SolidQualityLearning.com
<Troy.Rudolph@.ca.com> wrote in message
news:1125340946.797761.324100@.z14g2000cwz.googlegroups.com...
> Hi All,
> Ingres has some functions for converting UUIDs. Specifically, they are
> uuid_to_char() and uuid_from_char(). I'm new to MS SQL and would like
> to write some built-in functions (or any other solution) that will
> allow me to use the same code in MS SQL and Ingres.
> Example:
> select uuid_to_char(xyz_uuid) from xyz
> I have written a simple built-in function to do this and it works just
> fine. The one fly in the ointment is that the "database owner" seems
> to be required to execute it.
> "select uuid_to_char(xyz_uuid) from xyz" yields "'uuid_to_char' is not
> a recognized function name".
> "select dbo.uuid_to_char(xyz_uuid) from xyz" works just fine.
> I have found in the documentation where it says that the owner is
> required in this case.
> Is there some other technique available to eliminate use of the owner
> name?
> Thanks!
> Troy Rudolph
>|||Thank you for the quick reply.
You mentioned that the function name has to start with "fn_". It that
true for invocation as well as definition? In other words, do I have
to invoke it like this:
select fn_uuid_to_char(xyz_uuid) from uuid
If so, it really won't help.
Thanks again
Troy|||> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
Yep.

> If so, it really won't help.
Probably for the best. ;-)
BG, SQL Server MVP
www.SolidQualityLearning.com
<Troy.Rudolph@.ca.com> wrote in message
news:1125342504.627559.165260@.o13g2000cwo.googlegroups.com...
> Thank you for the quick reply.
> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
> select fn_uuid_to_char(xyz_uuid) from uuid
> If so, it really won't help.
> Thanks again
> Troy
>|||Yes, defined as fn_myfunction, then called as fn_myfunction. There is no
shortcut.
I really would not recommend it for any custom database. Maybe, in some
exceptional situations I would use it for very generic functions that
Microsoft should have provided out of the box, never for customer or
business specific functions.
Reasons not to take this road:
- You have to do a lot of 'special' stuff. And everyone who ever has to
maintain this function needs to know all this. It would definitely add
to the cost of maintaining the software
- It is undocumented and therefore unsupported. Although unlikely, its
behavior could change with the next service pack
- Microsoft could decide to add system function and 'accidentally'
choose the same function name as you did
Gert-Jan
Troy.Rudolph@.ca.com wrote:
> Thank you for the quick reply.
> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
> select fn_uuid_to_char(xyz_uuid) from uuid
> If so, it really won't help.
> Thanks again
> Troy