BULK INSERT maybe ??

I have a directory with files:

X.dbf
X.mdx
Y.dbf
Y.mdx
...

These files contain updates for my DB (I don't know their structure). How I can insert them in temporary tables on the SQL server ?

Note: I don't want to use Import/Export Tool, cause I will need this insert as scripts...I'd use DTS.

-PatP|||I'd use DTS.

-PatP

You would?

I'd create a sproc...

USing xp-Cmdshell, I would interogate the directory and load the file names to a table.

I would process the files 1 by 1

I would the bcp the data in to a single column varchar table

I would then process the data.

Bu if you don't know the structure of the data, what would you propose you'd do?|||Last time I did something like this, the .dbf extension files came from dBase V. DTS has an interface for that datafile type. As for .mdx ... ?|||It isn't hard to determine the schema of a DBF file, as Tom pointed out they are just dBase files which are effectively a single table with the schema tucked into the file header. You can relatively easily import arbitrary dbf files into a database from within a DTS package... It is more complex than dealing with a static structure, but not rocket science by any means.

The MDX files are just queries written as Multi-dimensional Expressions. Those can simply be stuffed into a TEXT column somewhere, probably the best organization would be to simply track what file they came from (X.MDX) and the text in a single table, maybe with some decorative columns to track when the file was timestamped, when it was imported into the table, etc.

-PatP|||Multi-dimensional Expressions? I may be way off, but I think I remember MDX to be primary indexes and NDX secondary (?)...Or maybe it's FoxPro? It's been awhile.|||I should just make it part of my sig...

"It really helps if you read these things"|||...and your point is...?

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 from Memory (without using physical file)

Greetings,
In SQL Server (7, 2000), can we bulk insert data from a data structure
(any possible kind) from the memory without using physical file as
source.
Thanks in advance,
Don
*** Sent via Developersdex http://www.examnotes.net ***For SQL Server 2000 you can bulk load XML from
a stream using the SQLXMLBulkLoad COM object.
Also you can use the 'C' callable bulk
load API (bcp_init/bcp_bind/bcp_sendrow etc).|||Check out the SqlBulkCopy class in System.Data.SqlClient in .NET Framework 2
.0
Linchi
"don larry" wrote:

> Greetings,
> In SQL Server (7, 2000), can we bulk insert data from a data structure
> (any possible kind) from the memory without using physical file as
> source.
> Thanks in advance,
> Don
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thank you Linchi (and unknown user), i appreaciate the feedback.
Alex
*** Sent via Developersdex http://www.examnotes.net ***

Bulk Copy Text Qualifier

Hi,
this is the structure of my file .txt:
'S012RM4','BBBBKMQBBBB8'
'S012RM4','BBBBKMQBBBB8'
and this is the structure of my file .fmt:
7.0
2
1 SQLCHAR 0 50 "," 1 c_1
2 SQLCHAR 0 50 "\r\n" 2 c_2
I'm using a DTS to import this recods with Bulk Copy.
This is the "Text qualifier" --> ' ... but the Bulk Copy import this record:
column 1 = 'S012RM4'
column 2 = 'BBBBKMQBBBB8'
and not
column 1 = S012RM4
column 2 = BBBBKMQBBBB8
why?
Thanks
Simo,
The ' is part of the string as defined. If you can, get the text file
delivered to you without quote marks and tab delimited. (Then you probably
won't need a format file.)
As you have it, you will need to include the ' as part of the field
terminator (e.g. ','). How do you get rid of the first quote? IF I
remember correctly, make field 1 terminate with ' and assign it to no SQL
column. You might read this article on format files:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp
Russell Fields
"Simo" <anonymous@.discussions.microsoft.com> wrote in message
news:E06D2D53-AB35-4450-A089-21909DBCF2D2@.microsoft.com...
> Hi,
> this is the structure of my file .txt:
> --
> 'S012RM4','BBBBKMQBBBB8'
> 'S012RM4','BBBBKMQBBBB8'
> --
> and this is the structure of my file .fmt:
> --
> 7.0
> 2
> 1 SQLCHAR 0 50 "," 1 c_1
> 2 SQLCHAR 0 50 "\r\n" 2 c_2
> --
> I'm using a DTS to import this recods with Bulk Copy.
> This is the "Text qualifier" --> ' ... but the Bulk Copy import this
record:
> column 1 = 'S012RM4'
> column 2 = 'BBBBKMQBBBB8'
> and not
> column 1 = S012RM4
> column 2 = BBBBKMQBBBB8
> why?
> Thanks
>

Bulk Copy Text Qualifier

Hi
this is the structure of my file .txt
--
'S012RM4','BBBBKMQBBBB8
'S012RM4','BBBBKMQBBBB8
--
and this is the structure of my file .fmt
--
7.
1 SQLCHAR 0 50 "," 1 c_
2 SQLCHAR 0 50 "\r\n" 2 c_
--
I'm using a DTS to import this recods with Bulk Copy
This is the "Text qualifier" --> ' ... but the Bulk Copy import this record
column 1 = 'S012RM4
column 2 = 'BBBBKMQBBBB8
and not
column 1 = S012RM
column 2 = BBBBKMQBBBB
why
ThankSimo,
The ' is part of the string as defined. If you can, get the text file
delivered to you without quote marks and tab delimited. (Then you probably
won't need a format file.)
As you have it, you will need to include the ' as part of the field
terminator (e.g. ','). How do you get rid of the first quote? IF I
remember correctly, make field 1 terminate with ' and assign it to no SQL
column. You might read this article on format files:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp
Russell Fields
"Simo" <anonymous@.discussions.microsoft.com> wrote in message
news:E06D2D53-AB35-4450-A089-21909DBCF2D2@.microsoft.com...
> Hi,
> this is the structure of my file .txt:
> --
> 'S012RM4','BBBBKMQBBBB8'
> 'S012RM4','BBBBKMQBBBB8'
> --
> and this is the structure of my file .fmt:
> --
> 7.0
> 2
> 1 SQLCHAR 0 50 "," 1 c_1
> 2 SQLCHAR 0 50 "\r\n" 2 c_2
> --
> I'm using a DTS to import this recods with Bulk Copy.
> This is the "Text qualifier" --> ' ... but the Bulk Copy import this
record:
> column 1 = 'S012RM4'
> column 2 = 'BBBBKMQBBBB8'
> and not
> column 1 = S012RM4
> column 2 = BBBBKMQBBBB8
> why?
> Thanks
>

Bulk Copy Text Qualifier

Hi,
this is the structure of my file .txt:
--
'S012RM4','BBBBKMQBBBB8'
'S012RM4','BBBBKMQBBBB8'
--
and this is the structure of my file .fmt:
--
7.0
2
1 SQLCHAR 0 50 "," 1 c_1
2 SQLCHAR 0 50 "\r\n" 2 c_2
--
I'm using a DTS to import this recods with Bulk Copy.
This is the "Text qualifier" --> ' ... but the Bulk Copy import this record
:
column 1 = 'S012RM4'
column 2 = 'BBBBKMQBBBB8'
and not
column 1 = S012RM4
column 2 = BBBBKMQBBBB8
why?
ThanksSimo,
The ' is part of the string as defined. If you can, get the text file
delivered to you without quote marks and tab delimited. (Then you probably
won't need a format file.)
As you have it, you will need to include the ' as part of the field
terminator (e.g. ','). How do you get rid of the first quote? IF I
remember correctly, make field 1 terminate with ' and assign it to no SQL
column. You might read this article on format files:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp
Russell Fields
"Simo" <anonymous@.discussions.microsoft.com> wrote in message
news:E06D2D53-AB35-4450-A089-21909DBCF2D2@.microsoft.com...
> Hi,
> this is the structure of my file .txt:
> --
> 'S012RM4','BBBBKMQBBBB8'
> 'S012RM4','BBBBKMQBBBB8'
> --
> and this is the structure of my file .fmt:
> --
> 7.0
> 2
> 1 SQLCHAR 0 50 "," 1 c_1
> 2 SQLCHAR 0 50 "\r\n" 2 c_2
> --
> I'm using a DTS to import this recods with Bulk Copy.
> This is the "Text qualifier" --> ' ... but the Bulk Copy import this
record:
> column 1 = 'S012RM4'
> column 2 = 'BBBBKMQBBBB8'
> and not
> column 1 = S012RM4
> column 2 = BBBBKMQBBBB8
> why?
> Thanks
>

Build SQL statement to show some tiers

in my relational sql db structure I have some items and its children, grandchildren items
to print these values do i need to build one sql statement for each tier?
into my sproc i tried something as following but it returns me only the last tier
SELECT * FROM A
INNER JOIN
B ON A.field1 = B.field2
INNER JOIN
C ON A.filed3 = C.field4
WHERE A.filed1 = @.myParam

thanks a lot

I'm not sure if you're looking to query an hierarchy but if so:
It sounds like you've got a table that's representing a hierarchial relationship, ie something like this:
create table myTable
(
id int,
parentID int null,
value varchar(30)
)
There are a bunch of good articles on sqlteam. Take a look at this one:
http://www.sqlteam.com/item.asp?ItemID=8866
Hope this helps,

Build datbase from xsd/xml ?

Hello all. I have an .xsd file and a .xml file that goes with it. Are there any tools available that will build a database structure in SQL Server from either of these two files? I can't see the .xml file being of much use for building the structure, but how about the .xsd file?
Nope - this requires good old analysis and database design the old fashioned way!

Brad