Inserting into variable no. of columns

• chrisdaniel09

  Newbie

  Points: 5

  More actions

  May 10, 2009 at 3:58 am

  #103734

  I have a table which will have variable no of columns, with name col1, col2, col 3

  I have to insert into this table, but the data comes from a excel sheet, which will have different column names, i have to insert that data into col1, col2, ....... in order...

  i read the data from .xls file into .net dataset, the columns will have different name then the table columns, also the number of columns in excel will be same as that in Table or less.

  rt now i am building up a string with a complete insert statment in my datalayer and sending it to sql server where it is executed using sp_executeSQL.

  Can any one help me to use a better approach.

  Thanks,

  Daniel.

• Lowell

  SSC Guru

  Points: 323753

  More actions

  May 10, 2009 at 5:31 am

  #991770

  are the columns in the spreadsheet in the same order as the columns in the dataset?

  what i mean is, if the spreadsheet has 10 columns today, and 14 columns tomorrow,

  do you KNOW that spreadsheet column1=dataset column1, 2=2, etc?

  if that is true, then you can build your query on the number of columns in the spreadsheet,

  something like

  this:

  dim collist as String

  dim xlslist as String

  dim dt as New DataTable

  dim cnt as integer

  dim i as integer

  --assuming you've already set up a connection myConn

  --and command object myCmd, connected and populated a datatable.

  i=[ your spreadsheets number of columns]

  for cnt=0 to i-1

  collist = collist & dt.Columns(cnt).name + ","

  xlslist = xlslist & Xls.Columns(cnt).Name + ","

  Next

  '--assume the list is now "PKID,col1,col2,col3,col4,"

  '--you now have a two parallel comma delimited lists of columns with a trailing comma...remove that extra comma, , and use it to build a sql

  newSQL = "INSERT INTO TABLE(" + collist + ")" SELECT " + xlslist + " FROM LinkedServerToExcel...SheetName"

  the above is untested psuedo code, but should get you started.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!