Hi Rich. I had a similar task and for me the best way to bulk import a large number of rows into SQL Server was to use the bcp utility. Here's a great resource for it:
https://www.red-gate.com/simple-talk/sql/database-administration/working-with-the-bcp-command-line-utility/ I create a tab-delimited temp file of the data (using perl of course) and then use system() to execute bcp. I know this isn't how you were asking to solve it, but my imports of 500,000 rows take seconds instead of minutes. Keith Carangelo On Sat, Nov 5, 2022 at 1:11 PM Rich Duzenbury <duzenb...@gmail.com> wrote: > Hi All, > > I am attempting to bulk merge a lot of rows quickly to an mssql server and > I understand that table value parameters are a possible option for doing so. > > I set up the following test objects on the sql server (modeled after a > python example of sorts from > https://github.com/mkleehammer/pyodbc/issues/595#issuecomment-584761512): > > create table testtable ( > id int not null, > primary key clustered (id asc) > ); > GO > > create type dbo.testtype as table ( > id int not null, > primary key clustered (id asc) > ); > GO > > create procedure dbo.testproc(@tvp dbo.testtype READONLY) > as begin > set nocount on; > insert into testtable (id) > select id from @tvp > end; > GO > > And am attempting to use the following script (name tvf): > > #!/usr/bin/perl > > use DBI; > > our $userid; > our $password; > our $dsn; > > do( './tvf.conf' ); > > my $dbh = DBI->connect( $dsn, $userid, $password, { RaiseError => 1 }); > > my $sth = $dbh->prepare( qq{ exec dbo.testproc ? } ); > > my $table_values = [ > [1], > [2], > ]; > > $sth->execute( $table_values ); > > And I receive the following error: > Cannot bind a plain reference at ./tvf line 20. > > I believe that error comes from the odbc driver and am uncertain what, if > anything, can be done about it. > > My DSN starts with "dbi:ODBC:DRIVER=tds;database=" which I believe means > that this driver is used: > /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so > > Has anyone had success in passing a tvf from perl via DBI? Could anyone > share an example, please? > > -- > Thank you. > > Regards, > Rich > -- kcaran.com <https://www.kcaran.com>
