• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

Table valued parameters in SqlServer 2005

Hi,
I want to use table type parameter for procedure because i need insert to to many rows(300000) quicly. This problem was solved in sqlserver 2008. I need like this code;

create proc(@tbl table(a int, b nvarchar(10))
as
begin
      insert into mytable(a,b)
      select a,b from @tbl
end

or

i change this row like to:
create proc(@tbl myTableType)
as
...
...

How can i insert to many rows in a one transaction.

I'm use ado.net 1.1(asp.net) and sqlserver 2005.

Best Regards.
0
OmerFarukZ
Asked:
OmerFarukZ
  • 4
  • 2
2 Solutions
 
OmerFarukZAuthor Commented:
And which sqldbtype must used?(ado.net not contain sqldbtype.structed)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sql 2005 does not have this feature.
so, you must do this differently.

one possibility would be passing the data as XML, but with 30000 records to be inserted, depending on the "row size" with the xml overhead, this might get a lot of data to pass to the procedure at once...
0
 
OmerFarukZAuthor Commented:
Click for more information the structed dbtype in .net: http://msdn.microsoft.com/en-us/library/bb675163.aspx. xml idea is very logical, i try and reply to you.

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
OmerFarukZAuthor Commented:
Does xml type kind of support more than 8000 characters?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Does xml type kind of support more than 8000 characters?
in sql 2005, yes. up to 2GB
0
 
Mark WillsTopic AdvisorCommented:
300,000 rows of data will be a big XML document, but as angelIII points out the XML datatype can take 2gb

Insert 300,000 rows from where ?
What is the source data that needs to be inserted ?
Is it going straight into "live" tables or using a "staging" table first ?

Might be able to use bulk insert, and a few other possible methods...
0
 
OmerFarukZAuthor Commented:
okay I'll try
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now