Re: Table Value Parameter for SQL server

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 <duzenbury@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>

• Table Value Parameter for SQL server by Rich Duzenbury
• Re: Table Value Parameter for SQL server by Keith Carangelo