Re: Table Value Parameter for SQL server

--00000000000000609b05ed32e12d
Content-Type: text/plain; charset="UTF-8"

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 <[email protected]> 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>

--00000000000000609b05ed32e12d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Rich.<div><br></div><div>I had a similar task and for m=
e the best way to bulk import a large number of rows into SQL Server was to=
 use the bcp utility. Here&#39;s a great resource for it:</div><div><br></d=
iv><div><a href=3D"https://www.red-gate.com/simple-talk/sql/database-admini=
stration/working-with-the-bcp-command-line-utility/">https://www.red-gate.c=
om/simple-talk/sql/database-administration/working-with-the-bcp-command-lin=
e-utility/</a><br></div><div><br></div><div>I create a tab-delimited temp f=
ile of the data (using perl of course) and then use system() to execute bcp=
.</div><div><br></div><div>I know this isn&#39;t how you were asking to sol=
ve it, but my imports of 500,000 rows take seconds instead of minutes.</div=
><div><br></div><div>Keith Carangelo</div></div><br><div class=3D"gmail_quo=
te"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, Nov 5, 2022 at 1:11 PM Ri=
ch Duzenbury &lt;<a href=3D"mailto:[email protected]">[email protected]=
</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=
<div dir=3D"ltr">Hi All,<div><br></div><div>I am attempting to bulk merge a=
 lot of rows quickly to an mssql server and I understand that table value p=
arameters are a possible option for doing so.</div><div><br></div><div>I se=
t up the following test objects on the sql server (modeled after a python e=
xample of sorts from=C2=A0<a href=3D"https://github.com/mkleehammer/pyodbc/=
issues/595#issuecomment-584761512" target=3D"_blank">https://github.com/mkl=
eehammer/pyodbc/issues/595#issuecomment-584761512</a>):<br></div><div><br><=
/div><div><span style=3D"font-family:monospace">create table </span>testtab=
le<span style=3D"font-family:monospace"> (</span><br></div><div><font face=
=3D"monospace">=C2=A0 id int not null,<br>=C2=A0 primary key clustered (id =
asc)<br>);<br>GO<br><br>create type dbo.testtype as table (<br>=C2=A0 id in=
t not null, <br>=C2=A0 primary key clustered (id asc)<br>);<br>GO<br><br>cr=
eate procedure dbo.testproc(@tvp dbo.testtype READONLY)<br>as begin<br>=C2=
=A0 set nocount on;<br>=C2=A0 insert into testtable (id)<br>=C2=A0 select i=
d from @tvp<br>end;<br>GO<br></font></div><div><br></div><div>And am attemp=
ting to use the following script (name tvf):</div><div><br></div><div><div>=
<font face=3D"monospace">#!/usr/bin/perl<br><br>use DBI;<br><br>our $userid=
;<br>our $password;<br>our $dsn;<br><br>do( &#39;./tvf.conf&#39; );<br><br>=
my $dbh =3D DBI-&gt;connect( $dsn, $userid, $password, { RaiseError =3D&gt;=
 1 });<br><br>my $sth =3D $dbh-&gt;prepare( qq{ exec dbo.testproc ? } );<br=
><br>my $table_values =3D [<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0[1],<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0[2],<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0];<br><br>$sth-&gt;execute(=
 $table_values );<br></font></div><div><font face=3D"monospace"><br></font>=
</div><div>And I receive the following error:</div><div><span style=3D"font=
-family:monospace">Cannot bind a plain reference at ./tvf line 20.</span><b=
r></div><div><span style=3D"font-family:monospace"><br></span></div><div>I =
believe that error comes from the odbc driver and am uncertain what, if any=
thing, can be done about it.</div><div><br></div><div>My DSN starts with &q=
uot;dbi:ODBC:DRIVER=3Dtds;database=3D&quot; which I believe means that this=
 driver is used:</div><div><font face=3D"monospace">/usr/lib/x86_64-linux-g=
nu/odbc/libtdsodbc.so</font></div><div><br></div><div><span style=3D"font-f=
amily:arial,sans-serif">Has anyone had success in passing a tvf from perl v=
ia DBI?=C2=A0 Could anyone share an example, please?</span><br></div><div><=
font face=3D"arial, sans-serif"><br></font></div>-- <br><div dir=3D"ltr">Th=
ank you.<br><br>Regards,<br>Rich</div></div></div>
</blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"=
 class=3D"gmail_signature"><div dir=3D"ltr"><a href=3D"https://www.kcaran.c=
om" target=3D"_blank">kcaran.com</a></div></div>

--00000000000000609b05ed32e12d--