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 INSERT skips a row

Hi. Not sure if this is a bug with bulk insert or standard behaviour.
We have a flat file with a header record. A simplified example follows:
HEADER
John,Smith
Fred,Bloggs
But when we set bulk insert to skip the first row it actually skips the
John,Smith row as well. Run the script below after saving the CSV data out
to a file called user.csv...
create table [User]
(
FirstName varchar ( 32 ),
LastName varchar ( 32 )
)
bulk insert [User] from 'user.csv'
with
(
FIRSTROW = 2,
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
select * from [user] -- only one record
Please help!!
McGy
[url]http://mcgy.blogspot.com[/url]Anyone with ideas on how to get around this limitation?
McGy
[url]http://mcgy.blogspot.com[/url]
"McGy" <anon@.anon.com> wrote in message
news:#1#7JPAVGHA.5332@.tk2msftngp13.phx.gbl...
> Hi. Not sure if this is a bug with bulk insert or standard behaviour.
> We have a flat file with a header record. A simplified example follows:
> HEADER
> John,Smith
> Fred,Bloggs
> But when we set bulk insert to skip the first row it actually skips the
> John,Smith row as well. Run the script below after saving the CSV data out
> to a file called user.csv...
> create table [User]
> (
> FirstName varchar ( 32 ),
> LastName varchar ( 32 )
> )
> bulk insert [User] from 'user.csv'
> with
> (
> FIRSTROW = 2,
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> select * from [user] -- only one record
> Please help!!
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
>|||Some solutions are
1. Pre-process the file to remove the header line (use a DOS
or Unix-like utility, for example).
2. Bulk insert the data into a staging table with one column,
then parse the data into columns using SQL instead of the
bulk insert process.
3. Use SQL Server Integration Services. I'm no expert, but I believe
it can skip (or import into a different table) rows based on the
contents of the row, which bulk insert cannot do.
The FIRSTROW parameter for bulk insert, as you have discovered,
is not a FIRST_LINE_OF_THE_TEXT_FILE parameter.
Steve Kass
Drew University
McGy wrote:

>Anyone with ideas on how to get around this limitation?
>--
>McGy
>[url]http://mcgy.blogspot.com[/url]
>
>"McGy" <anon@.anon.com> wrote in message
>news:#1#7JPAVGHA.5332@.tk2msftngp13.phx.gbl...
>
>
>|||McGy (anon@.anon.com) writes:
Alas, this is standard behaviour.
BULK INSERT and BCP live in a very squared world. They don't think in
terms of lines, they think in terms of records and fields. (But calls them
rows and columns to matters.)
The first field is from the beginning of file until the terminator for
the first field. (For terminated fields. Fields can also be fix-length,
or include a length-prefix.) The second field lasts until the terminator
for that field. And so on, up to the last field which lasts until the
terminator for that field. That terminator is called a "row terminator",
but as long as BCP is not reading the last field, the terminator carries
no meaning. Once the last field has been read, bulk copy starts with
the first field again.
This has some advantages and divantages. On the flip side, is that
you can handle embedded row terminators, for instance embedded newlines
(except in the last field). The bad part is that you can't skip a header
which does not conform with the file. Because as far as BCP is concerned,
that header is part of the first field.
The workaround is to run a program first that strips the header, which
for a large file is not very appealing.
I know I have subumitted a suggestion for an improvement to bulk copy
for a future release of SQL Server, so that you should be able to describe
a header to be skipped. The suggestion is on
http://lab.msdn.microsoft.com/productfeedback/ but alas, the site
appears to experience some problems right now, so I cannot give the
exact link. But when it's back, search for by name, "bulk" and SQL 2005
to find it and vote for it.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Bulk Insert remove extra quotes

I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.

Example 1: """"This is a just a test."""

Results should be "This is just a test"

Example 2: """This is only a test"" and nothing more"

Results should be:"This is only a test" and nothing more.

Now.

If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.

Question: Is there a way or a switch in Bulk Insert that I could use to get rid of the extra quotes?

Below is my syntax

SET @.sql ='BULK INSERT '+ @.table_name + ' FROM "' + @.conversion_data_in + '\' + @.source_file + '"
WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'

Thanks
Conrad...

There is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.|||

Thanks for the information.

When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.

On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert.

Keep in mind, that the quotes may not appeared in all of the fields.

Thanks

Conrad...

|||As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.|||

Umachandar Jayachandran - MS wrote:

As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.

Hello,

I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.

Any links to documentation or examples would be welcome!

Thanks a lot

Detlef

Bulk Insert remove extra quotes

I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.

Example 1: """"This is a just a test."""

Results should be "This is just a test"

Example 2: """This is only a test"" and nothing more"

Results should be:"This is only a test" and nothing more.

Now.

If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.

Question: Is there a way or a switch in Bulk Insert that I could use to get rid of the extra quotes?

Below is my syntax

SET @.sql ='BULK INSERT '+ @.table_name + ' FROM "' + @.conversion_data_in + '\' + @.source_file + '"
WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'

Thanks
Conrad...

There is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.|||

Thanks for the information.

When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.

On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert.

Keep in mind, that the quotes may not appeared in all of the fields.

Thanks

Conrad...

|||As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.|||

Umachandar Jayachandran - MS wrote:

As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.

Hello,

I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.

Any links to documentation or examples would be welcome!

Thanks a lot

Detlef

Bulk Insert into SQL Server Table With XML

Hey There,

Here, is the example of Bulk Insert into SQL Server Table.

From Application you have to pass a XML string to a Stored Procedure and it will insert all data into table using that XML.

Example SP.

CREATE PROCEDURE StoredProcName

(

@.strXML varchar(8000)

)

AS

Declare @.intPointer int

exec sp_xml_preparedocument @.intPointer output, @.strXML

INSERT into tbl_plnd_insertion

SELECT Column1, Column2, Column3, Column4, Column5

FROM OpenXml(@.intPointer,'/root/tbl_plnd_insertion',2)

WITH (Column1 varchar(20) '@.Column1' , Column2 varchar(20) '@.Column2', Column3 varchar(20) '@.Column3' , Column4 varchar(50) '@.Column4', Column5 varchar(50) '@.Column5')

exec sp_xml_removedocument @.intPointer

Thanks !!!!!

In SQL Server 2000, Text data type is reasonable to use for huge data,

@.strXML Text--varchar(8000)

This is not a bulk insert, it is reading the data from the XML string into a table. sp_xml_preparedocument is expensive, it will use lot of memory & resource when the HUGE data parsed.

In SQL Server 2005, the best option is using XML datatype. You don’t need expensive sp_xml_preparedocuemnt to parse your XML string into selectable values, no need OpenXML also.

Sample,

Code Snippet

Declare @.xml as XML

set @.xml = '<root><student name="Stud1" sex="m"/><student name="Stud2" sex="f"/></root>'

Select

[Table].[Column].value('./@.name', 'varchar(100)') as [Name],

[Table].[Column].value('./@.sex', 'char') as [Sex]

from

@.xml.nodes('/root/student') as [Table]([Column])

|||

Hey

I am really appreciating your input… J

I did not describe all details in my code.

Thanks

Bulk insert data with decimal point

I am having a problem with bulk insert when the data in the input file
contains a decimal point. I have given a shortened example below, in reality
the table has about 500 columns and about 5,000,000 records were inserted
sucessfully before records with decimal points were encountered. I have
verified in the regional settings that the decimal point is the period.
If necessary to solve the problem, I can easily drop the table, create it
with different data types, and start the insert again. Getting the input
data changed would be considerably more difficult.
CREATE TABLE [MY_TABLE] (
[ID] decimal (9,0) NOT NULL,
[CITY] varchar (50) NULL,
[BIRTH_DATE] datetime NULL,
[TOTAL_INCOME] decimal (10,2) NULL,
[NET_INCOME] decimal (10,0) NULL,
[NET_TAX_PAID] decimal (10,2) NULL)
BULK INSERT MYDB.me.MY_TABLE
FROM 'd:\batch\data1999.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
123456787|NEW YORK|1973/05/06|900|875|278|
123456788|LONDON|1946/08/01|563.75|550|125.27|
123456789|MADRID|1980/03/10|1067|987|338.27
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 4
(TOTAL_INCOME).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 6
(NET_TAX_PAID).
have you try to create an FMT file?
this file will help you to define each input column.
Because I think you have a problem to identify the . (dot) has the decimal
separator.
have you try to use DTS to do this job?
DTS will help you to define the links and can create the FMT file for you.
"Carolyn" <postreply@.dontemail.com> wrote in message
news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
>I am having a problem with bulk insert when the data in the input file
> contains a decimal point. I have given a shortened example below, in
> reality
> the table has about 500 columns and about 5,000,000 records were inserted
> sucessfully before records with decimal points were encountered. I have
> verified in the regional settings that the decimal point is the period.
> If necessary to solve the problem, I can easily drop the table, create it
> with different data types, and start the insert again. Getting the input
> data changed would be considerably more difficult.
> CREATE TABLE [MY_TABLE] (
> [ID] decimal (9,0) NOT NULL,
> [CITY] varchar (50) NULL,
> [BIRTH_DATE] datetime NULL,
> [TOTAL_INCOME] decimal (10,2) NULL,
> [NET_INCOME] decimal (10,0) NULL,
> [NET_TAX_PAID] decimal (10,2) NULL)
> BULK INSERT MYDB.me.MY_TABLE
> FROM 'd:\batch\data1999.txt'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '|\n'
> )
> 123456787|NEW YORK|1973/05/06|900|875|278|
> 123456788|LONDON|1946/08/01|563.75|550|125.27|
> 123456789|MADRID|1980/03/10|1067|987|338.27
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 2, column 4
> (TOTAL_INCOME).
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 6
> (NET_TAX_PAID).
>
|||Thank you Jj, I will try using DTS to create a FMT file.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OqyzCJleFHA.132@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> have you try to create an FMT file?
> this file will help you to define each input column.
> Because I think you have a problem to identify the . (dot) has the decimal
> separator.
> have you try to use DTS to do this job?
> DTS will help you to define the links and can create the FMT file for you.
>
> "Carolyn" <postreply@.dontemail.com> wrote in message
> news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
inserted[vbcol=seagreen]
it
>

Bulk insert data with decimal point

I am having a problem with bulk insert when the data in the input file
contains a decimal point. I have given a shortened example below, in reality
the table has about 500 columns and about 5,000,000 records were inserted
sucessfully before records with decimal points were encountered. I have
verified in the regional settings that the decimal point is the period.
If necessary to solve the problem, I can easily drop the table, create it
with different data types, and start the insert again. Getting the input
data changed would be considerably more difficult.
CREATE TABLE [MY_TABLE] (
[ID] decimal (9,0) NOT NULL,
[CITY] varchar (50) NULL,
[BIRTH_DATE] datetime NULL,
[TOTAL_INCOME] decimal (10,2) NULL,
[NET_INCOME] decimal (10,0) NULL,
[NET_TAX_PAID] decimal (10,2) NULL)
BULK INSERT MYDB.me.MY_TABLE
FROM 'd:\batch\data1999.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
123456787|NEW YORK|1973/05/06|900|875|278|
123456788|LONDON|1946/08/01|563.75|550|125.27|
123456789|MADRID|1980/03/10|1067|987|338.27
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 4
(TOTAL_INCOME).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 6
(NET_TAX_PAID).have you try to create an FMT file?
this file will help you to define each input column.
Because I think you have a problem to identify the . (dot) has the decimal
separator.
have you try to use DTS to do this job?
DTS will help you to define the links and can create the FMT file for you.
"Carolyn" <postreply@.dontemail.com> wrote in message
news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
>I am having a problem with bulk insert when the data in the input file
> contains a decimal point. I have given a shortened example below, in
> reality
> the table has about 500 columns and about 5,000,000 records were inserted
> sucessfully before records with decimal points were encountered. I have
> verified in the regional settings that the decimal point is the period.
> If necessary to solve the problem, I can easily drop the table, create it
> with different data types, and start the insert again. Getting the input
> data changed would be considerably more difficult.
> CREATE TABLE [MY_TABLE] (
> [ID] decimal (9,0) NOT NULL,
> [CITY] varchar (50) NULL,
> [BIRTH_DATE] datetime NULL,
> [TOTAL_INCOME] decimal (10,2) NULL,
> [NET_INCOME] decimal (10,0) NULL,
> [NET_TAX_PAID] decimal (10,2) NULL)
> BULK INSERT MYDB.me.MY_TABLE
> FROM 'd:\batch\data1999.txt'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '|\n'
> )
> 123456787|NEW YORK|1973/05/06|900|875|278|
> 123456788|LONDON|1946/08/01|563.75|550|125.27|
> 123456789|MADRID|1980/03/10|1067|987|338.27
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 2, column 4
> (TOTAL_INCOME).
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 6
> (NET_TAX_PAID).
>|||Thank you Jj, I will try using DTS to create a FMT file.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OqyzCJleFHA.132@.TK2MSFTNGP10.phx.gbl...
> have you try to create an FMT file?
> this file will help you to define each input column.
> Because I think you have a problem to identify the . (dot) has the decimal
> separator.
> have you try to use DTS to do this job?
> DTS will help you to define the links and can create the FMT file for you.
>
> "Carolyn" <postreply@.dontemail.com> wrote in message
> news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
inserted[vbcol=seagreen]
it[vbcol=seagreen]
>

bulk insert

I'm trying run a bulk insert statement to insert data into an existing table:

Here is an example of the text file data:

"BEGIN_APP_YR","2001"
"BISP_EXPD_THRU","200512"
"BISP_ITER","PRELIM"
"BISP_LAST_PUB_DT","02/14/200612:41PM"
"BISP_YR","2007"
"BISP_YRS","3"
"END_APP_YR","2006"

This is the bulk insert statement I'm using:

BULK INSERT AFR.dbo.[BISM_CONFIG]
FROM 'c:\sql\default\bism_config.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

I'm getting a syntax error near ')'

Any help is appreciated.
Thanks,
-D-hi, you run the bulk insert in the Query Analyzer?
what is the complete error?
the syntax of the statement is correct.
test change the ',' for ';' or '|' (sure you need change the txt)

abel

Building on MSDE, but deploying in SQL 2000?

I'm new to SQL, so please forgive my rather primitive question...

If I build, for example, the Portal Starter Kit using MSDE, can I upload it to a host that supports SQL 2000 without having to make any special configurations? In other words, are MSDE and SQL 2000 interchangeable, for the most part?

Try these links first is the MSDE deployment toolkit and the second is SQL Server main site, try the first and if you run into problems, then download the evaluation edition it is good for 120 days and you can reinstall it. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msdedepl.asp
http://www.microsoft.com/sql

|||That does help. Thank you very much! :)|||I am glad I was able to be of assistance.

Building a date in SQL

Is there any sql method that takes 3 parameter like, day, month and year . And return me the date.

For example

function(10,3,2007) and it returns 10-03-2007

Thanks,

There is no such function as part of SQL2000 or SQL2005, however you could write your own function. You refer to the function returning 10-03-2007 as an example, however is it as string (char(10)) or as datetime? Also what datatype are the input parameters - integer?

|||

I have find the solution.

Thanks

Build Dynamic Table Columns Issue

How I can build a dynamic temp table based upon the dynamic coulmn info from the other table? Please see my attached file as an example. Thanks!
J827use a hughe varchar variable and fill it with a create table statement. To determine which columnnames, try information_schema.tables. Then execute it using exec.|||You need this

http://www.sqlteam.com/item.asp?ItemID=2955|||Brett,

Thanks for the Link and it works for my case.

J827|||Hey, thank Rob Volk...he's the author...

I'm just the messenger...

Lots of good articles over there...

Good Luck