Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

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.


James K
Flowing Fount of Yak Knowledge

3873 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  
 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.03 seconds. Powered By: Snitz Forums 2000