SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SP with table variable and return varaibles
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanmugaraj
Posting Yak Master

219 Posts

Posted - 04/10/2013 :  10:23:05  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
I have data in excel with DeptId, EmpId where DeptId will be in 1st Row and all EmpId will be from 2nd Row in each column.
Ie: in excel sheet
A1,B1,C1,... will have DeptId
A2,A3,A4,A5,... , B2,B3,B4,B5,...., C2,C3 will have EmpId

I want to load into table validating data against existing records in database table.
Input: A1|A2,A3,A4,A5&B1|B2,B3,B4,B5&C1|C2,C3
| seperater
& new row

Return : No of rows inserted, no of rows invalid.
Invalid condition : if the EmpId doesnot present in Employee Table.
Table Retur Format to be same as input row format

Excel will have DeptId mathcing. but few EmpId might be new records and not matching Database.
I need to create SP for validation and return of empid which might not be present in database along with DeptId as table variable.

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 04/10/2013 :  10:59:06  Show Profile  Reply with Quote
This is not exactly a direct solution to the problem you posted, but an alternate suggestion which would make other tasks you may want to do (such as querying against the data, updating it etc.) easier:

1. Transpose the data in y our excel sheet. You can do this by copying and selecting "Paste Special" and checking the Transpose checkbox.
2. Create a normalized table with two columns DeptId and EmpId. Also create a second staging table with the same columns.
3. Use any of the standard methods such as SSIS or Import/Export Wizard to import the data into the staging table.
4. Compare the staging table against the normalized table you created and insert/update/delete as necessary.

If you do it that way, the comma-separated format that you want to get can be generated from that. But in addition, as I indicated earlier, importing, querying, and updating becomes much easier.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000