Bulk Inserting Data

Question

Post reply

Bulk Inserting Data

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

James Tech Right there with Babe Points: 762 More actions · Answer 1

Oh okay. Thanks for the informative input Adi. I will try this and will let you know on my progress.

Thanks so much.

James Tech Right there with Babe Points: 762 More actions · Answer 2

Hello guys,

There has been a changes with our design and i think it is good. Instead of having one internal file with multiple tables, the service beforehand created one file per table.

So on a certain directory the is batch header file which is an xml file and contains the path and filename of those batch internal files that belongs to a certain batch. Each batch internal file contains data for one table only and in CSV format. The structure would look like this now:

batch header file

<batch_id>...</batch_id>

</header>

<data>

<table>name of the table to be updated</table>

<table>name of the table to be updated</table>

<table>name of the table to be updated</table>

.....

</data>

batch internal file

batch_id, followed by a value (may be empty) for each value in the table followed by a comma (except for the last value in the record)

1,col1,,col2,col3,col4

1,col1,,,col3,col4

1,col1,,col2,col3,col4

1,col1,,col2,,col4

1,col1,,col2,col3,col4

.....

Each record in the internal file equates to a record in the table. There will be a separate file for each table.

I am planning on having an assembly (C# dll) to read the header file and traverse all the internal files and bulk insert it right away to the database. Is this the correct path on how to deal this kind of scenario? or if there is any better suggestion would greatly be appreciated.

Thanks for the help.

khelelo SSC Journeyman Points: 83 More actions · Answer 3

Hi,

Regarding this subject, I am battling in creating a proc to bcp distinct text files into distinct multi - tables by using one table as a parameter. The parameter table has all the table and file names. I have used cursors but their fail.

eg:

declare @filename varchar(100)

declare @bcpCommand as nvarchar(4000)

declare @defaultdirs as nvarchar(4000)

declare @dd as varchar(2)

--SET @bcpCommand = (' bcp General.dbo.'+@filename + ' in '+ @DefaultDirs +'\' +@filename +'.' + @dd + ' -T -f'+@DefaultDirs +'\Format\'+@filename +'.fmt' + ' -k -e'+@DefaultDirs +'\Error\'+@filename +'.err' )

--set @filename = (select DataSource FROM HO_Control_Table)

--select DataSource FROM HO_Control_Table

declare Next_Table_Cursor CURSOR FOR

select Filename FROM Parameter_Control_Table;

OPEN Next_Table_Cursor;

FETCH NEXT FROM Next_Table_Cursor;

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM Next_Table_Cursor;

END;

CLOSE Next_Table_Cursor;

DEALLOCATE Next_Table_Cursor;

GO

Please help!!

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Not with SQL Server on-board means. Thats the reason for SSIS. Sure you can write any external program or script to handle this.

Greets

Flo

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 5

[font="Verdana"]You should be able to bulk insert the header file into an XML column, and read it out from there. The second part just use normal bulk insert.[/font]

James Tech Right there with Babe Points: 762 More actions · Answer 6

Bruce W Cassidy (3/12/2009)
[font="Verdana"]You should be able to bulk insert the header file into an XML column, and read it out from there. The second part just use normal bulk insert.[/font]

Hi Bruce,

Do you have any example (working code) or reference on what you are trying to point out?

Thanks,

James

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 7

[font="Verdana"]Reading over what you were proposing, I'm not sure it actually would add any value to load the XML into SQL Server. Processing it with your application is probably the better approach.

No, I don't have a working example of this. We have a tool that largely does this sort of thing for us, but it uses pre-generated code based on metadata, rather than generating the code on the fly.

[/font]

James Tech Right there with Babe Points: 762 More actions · Answer 8

Bruce W Cassidy (3/12/2009)
[font="Verdana"]Reading over what you were proposing, I'm not sure it actually would add any value to load the XML into SQL Server. Processing it with your application is probably the better approach.
No, I don't have a working example of this. We have a tool that largely does this sort of thing for us, but it uses pre-generated code based on metadata, rather than generating the code on the fly.
[/font]

Oh okay. I already created an multi-threaded assembly to process the files and call a procedure to simply bulk insert it right away to the database. Thank you for the input Bruce and everyone. I really appreciate it.