Insertion data via Stored Procedure [URGENT]!

Hi all;

Question:
=======
Q1) How can I insert a record into a table "Parent Table" and get its ID (its PK) (which is an Identity "Auto count" column) via one Stored Procedure??

Q2) How can I insert a record into a table "Child Table" whose (FK) is the (PK) of the "Parent Table"!! via another one Stored Procedure??

Example:
----
I have two tables "Customer" and "CustomerDetails"..

SP1: should insert all "Customer" data and return the value of an Identity column (I will use it later in SP2).

SP2: should insert all "CustomerDetials" data in the record whose ID (the returned value from SP1) is same as ID of the "Customer" table.

FYI:
--
MS SQL Server 2000
VS.NET EA 2003
Win XP SP1a
VB.NET/ASP.NET :)

Thanks in advanced!There are a couple of ways to get the last inserted IDENT, but I prefer to use IDENT_CURRENT('table_name') to get the ID from the last inserted record of thespecified table.

INSERT INTO Customer (val1,val2,etc) VALUES (@.Val1,@.val2,etc)
INSERT INTO CustomerDetails (ID,Val1,Val2, etc) VALUES ((IDENT_CURRENT('Customer'),@.val1,@.val2,etc)

|||Ok, what about if I want to do these process in two SPs??

I want to take the IDDENTITY value from the fitrst "INSERT INTO" statment, because I need this value in my source code as well as other Stored Procedure(s).

Thanks in advanced!|||Insert Blah...;
Return SCOPE_IDENTITY()|||Thanks gays.

I think my problem is how to get the returned value (IDDENTITY value) from the VB.NET code (in other words, how to extract it from VB.NET/ADO.NET code)?

Note:
--
VB.NET/ADO.NET
or
C#.NET/ADO.NET

Are are ok, if you would like to demonstrate your replay. ( I want the answer!).

Thanks again.|||YES!!

The problem was from my ADO.NET part.

Thanks for your help gays.

Inserting XML with SSIS - VERY URGENT!!!

Hello everybody,

I have a problem. I need to insert an unknown number of xml files in a database (all files are always in the same folder), in different tables, each file has the same name that the corresponding table. For example:

Files Tables

user.xml user

purchase.xml purchase

...and so

but the number of files is not always the same, I mean, it can be 6 one day and only 4 the next day.
Can I insert the data in the xml files into the tables with a Foreach Loop Container or any other way? If it's possible, how?

Thanks in advance for your help,

Radamante71

You might want to post your question at SQL Server Integration Services forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1.

Inserting with XML files - VERY URGENT!!!

Hello everybody,

I have a problem. I need to insert an unknown number of xml files in a database (all files are always in the same folder), in different tables, each file has the same name that the corresponding table. For example:

Files Tables

user.xml user

purchase.xml purchase

...and so

but the number of files is not always the same, I mean, it can be 6 one day and only 4 the next day.
Can I insert the data in the xml files into the tables with a Foreach Loop Container or any other way? If it's possible, how?

Thanks in advance for your help,

Radamante71

Create a ForEach Loop task to read the files from the directory and assign the name to a variable. Then add a dataflow task to the foreach loop and in your XML source file, select "XML File from variable", then select the variable name.|||

Tom suggestion will work if all file/tables had the same structure, which I doubt. To the original poster: I think what you want to accomplish is not possible. Basically you are asking to create the source-to-target data mapping on the fly and that is something you cannot do in a SSIS package (at least to my knowledge).

Rafael Salas

|||

Rafael,

One question:

Do you speak spanish? Because if you do, we can continue in spanish. I'm spanish and I think that I can explain you my problem in a better way if I do it in my language.

Thanks

|||You are correct. I don't see any way to use a variable XSD file.

However, you could use the "Inline Schema" in the file, that would work.|||

Radamante,

Yes, I do speak; but I don't think it would be a good idea to continue this thread in Spanish; that could irritate some non-Spanish speaker folks in this forum. If you want, send me an e-mail with your problem (be sure to de-spam my email address before) to see if I can help you.

Rafael Salas