Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shanmugaraj
Posting Yak Master
219 Posts |
 Posted - 2013-04-10 : 10:23:05
|
| 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 sheetA1,B1,C1,... will have DeptId A2,A3,A4,A5,... , B2,B3,B4,B5,...., C2,C3 will have EmpIdI 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 rowReturn : 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 formatExcel 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.THANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-10 : 10:59:06
|
| 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. |
 |
|
|
|
|
|
|
|
SP with table variable and return varaibles