I need a stored procedure to import the excel data to sqlserver tables.
Please suggest.
Thanks Khtan
I want to create a stored procedure which would be called from the front end interface( java app) to process the data to the tables..
The java code will read the excel data and stored procedure be called to insert the data into the table..
My Excel format looks like.
Customers belongs to productsale.
Customers ID City STATE Country Region Productsale
Arnon 1 JA UT UK NR 10
Bob
Jon
Ran
Sam
I want the above excel values into 2 tables below..
ID City STATE Country Region Productsale
1 JA UT MA NR 10
Customer
CID Customer
1 Arnon
1 Bob
1 Jon
1 Ran
1 Sam
Thanks for your help..
Did you work through the examples in the link Khtan gave? Look like your answer is there
1 Like
Here is what i m trying to achieve with the procedure..
Create PROCEDURE [dbo].[loadinsert]
@CustomersData varchar(max) = NULL, --- How to capture all customers records in one call and process into the table
@ID BIGINT,
@City VARCHAR(100) = NULL,
@STATE VARCHAR(10) = NULL,
@Country VARCHAR(10) = NULL,
@Region VARCHAR(100) = NULL,
@Productsale VARCHAR(100) = NULL
AS
BEGIN TRY
---The product sale information will have one records in the file for each load.
INSERT INTO [dbo].[PRODUCTSALE] (
[ID]
, [City]
, [STATE]
, [Country]
, [Region]
, [Productsale]
) VALUES (
@ID,
@City,
@STATE,
@Country,
@Region,
@Productsale,
)
INSERT INTO [dbo].[Customer] (
[ID]
, [Customer]
)
----How to load the customers values which will have many customers
END TRY
Expected output with procedure:
Table: Productsale
ID City STATE Country Region Productsale
1 JA UT MA NR 10
Table:Customer
CID Customer
1 Arnon
1 Bob
1 Jon
1 Ran
1 Sam
Please let me know if i m not clear..
You're clearer. I see that this problem is quite different than what you stated at first. questions:
- why do all your sample customers have the same ID?
- What would the @CustomersData look like on a typical call?
FWIW I wouldn't do both inserts in the same proc. I'd write two procs: one for customers and one for product sales
You're clearer. I see that this problem is quite different than what you stated at first. questions:
why do all your sample customers have the same ID?
Because customerdata belongs to the same ID.
I am also suprised with the provided data format but have no control on the format.
What would the @CustomersData look like on a typical call?
I m hoping to be a comma separated values for the @CustomersData....
Please suggest if there is any better recommended way for this type of calls.
FWIW I wouldn't do both inserts in the same proc. I'd write two procs: one for customers and one for product sales
I would agree to have 2 seperate procedures instead of one procedure.
comma-separated is OK. See this article for help on turning comma-delimited data into a rowset for processing (e.g, for your INSERT)