November 28, 2011 at 2:04 am
Hi all,
I've been trying to split an input parameter table into multiple tables while ensuring that the data had an mutual reference. I came up with the following solution:
CREATE TYPE mytable AS TABLE
(
registrationIdentifier UNIQUEIDENTIFIER DEFAULT NEWID(), -- Added only to insure mutual reference.
val1 int,
val2 int
)
create proc (@testData AS mytable READONLY)
AS
insert into tab_a select registrationIdentifier ,val1 from @testData
insert into tab_b select registrationIdentifier ,val2 from @testData
go
I really wanted the registrationIdentifier to be an INTEGER but since mytable is a new table each time the proc is exec i cant use identity.
I tried using the output clause (while having an identity col on tab_a) but it doesnt work since there is no way to output the columns from the input table to the output table.
It seems to be possible only when updating and deleting (http://technet.microsoft.com/en-us/library/ms177564.aspx see ex. E and F where p.Name is outputtet).
So what I wanted was something like:
CREATE TYPE mytable AS TABLE
(
val1 int,
val2 int
)
!!!!tab_a has an identity column!!!!
DECLARE @MyTableVar table(id identity(0,1),
val2 int);
create proc (@testData AS mytable READONLY)
AS
insert into tab_a
output @@IDENTITY, td.val2 to @MyTableVar
select val1 from @testData td
insert into tab_b select id,val2 from @MyTableVar
go
Does anyone have a solution to this issue or another way to achieve the int instead of uid.
Thanks in advance,
Dan
November 28, 2011 at 5:46 am
I'm a bit confused.
If I understand correctly, you are trying to pass a table valued parameter to a stored procedure and insert the rows into two different tables (table_a and table_b).
Why should identity be a problem at all?
Are you trying to copy the identity values from table_a to table_b?
Can you clarify with an example based on some sample data?
-- Gianluca Sartori
November 28, 2011 at 7:04 am
thank for your reply 🙂
All I want to end up with is for (say a list of personnames and personages) to be able to insert them into two different tables and be able to match them again later. The input being a table type. Thats all I want. Below is an attempt to do this (that almost works ;))
I´ve tried to be a bit more specific with the example where im using output.
This is essentially what i am trying to do:
USE Test
GO
CREATE TABLE tab_name (id INT IDENTITY(1,1), personname varchar(50))
CREATE TABLE tab_age (id int, personage int)
GO
CREATE TYPE mytable AS TABLE
(
val1 int,
val2 int
)
GO
create proc sp_test(@testData AS mytable READONLY)
AS
DECLARE @MyTableVar table(id INT, personage varchar(50))
insert into tab_name (personname)
output @@IDENTITY, td.personage into @MyTableVar <<<--- The problem is I cant get td.personage
select personname from @testData td
insert into tab_age (id,personage) select id,personage from @MyTableVar
go
c# code:
class Program
{
static void Main(string[] args)
{
testItem teitem = new testItem() { personname = "Peter", personage = 33 };
testCollection pulist = new testCollection();
pulist.Add(teitem);
using (SqlConnection conn = new SqlConnection("............."))
{
using (SqlCommand sqlcmd = new SqlCommand("sp_test", conn))
{
try
{
sqlcmd.CommandType = CommandType.StoredProcedure;
//Populate input parameters
SqlParameter[] sqlParameter = new SqlParameter[1];
sqlParameter[1] = new SqlParameter("testData", SqlDbType.Structured);
sqlParameter[1].Value = pulist;
sqlParameter[1].TypeName = "dbo.mytable";
conn.Open();
sqlcmd.Parameters.AddRange(sqlParameter);
sqlcmd.BeginExecuteNonQuery();
}
finally
{
conn.Close();
}
}
}
}
}
public class testItem
{
public string personname { get; set; }
public int personage { get; set; }
}
public class testCollection : List<testItem>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
var sdr = new SqlDataRecord(new SqlMetaData("personname", SqlDbType.VarChar,20), new SqlMetaData("personage", SqlDbType.Int));
foreach (testItem ti in this)
{
Guid uid = Guid.NewGuid();
sdr.SetSqlString(0, ti.personname);
sdr.SetSqlInt32(1, ti.personage);
yield return sdr;
}
}
}
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply