can I take rows of data to split between two tables
-
This is one row from an excel spreadsheet. I want the first 5 items in the row to go to table 1 and the last two items to go to table2
Dare ID Type Brand Model Serial Vendor OuR PO 1135 PC Acer 7500 29057773122653749AK ABC 6009253 -
You just need two insert statements
insert into Table1 (fld1,fld2,fld3,fld4,fld5)
select DareID, Type, Brand, Model, Serial from XLSheet
--XLSheet is View wrapping a linked server access
insert into Table2 (fld1,fld2)
select Vendor, OuRPO from XLSheet -- same as above
You probably want to link the two but I leave that to you because no enough info was provided
Cheers
* Noel -
So if I have a large amount of data can I use BCP or DTS? linked is correct.
-
Can't you simply import in a temp table, then insert/select in 2 batches.
-
BCP only works with csv of fixed length text files
DTS can use XL Sheets directly
Or
You can create a Linked server or use Openrowset to access the file directly from SQL
* Noel -
Too many ways to skin the cat here
. -
That is great everyone... thanks for the help
-
Don't forget about OPENQUERY. It will help you access XLS sheets as normal tables or views without downloading it into temp tables.
Don't forget to add $ in the end of sheet name.
You can find complete reference for OPENQUERY in BOL with examples particulary for XLS files.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply