Dynamic Insert into Query

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 2, 2005 at 2:05 pm

  #165783

  Hi,

  I am trying to insert data into a table. The columns in the table vary based on the database. I have to write a query which will insert data into the table. below is the sample code

  SET DATEFIRST 1

  CREATE TABLE #Periods (

  Period Datetime,

  WkStart Datetime,

  Primary Key Clustered (Period))

  INSERT INTO #Periods

  SELECT * from fnPeriods(@StartDate, @EndDate, @ClientWkStart)

  INSERT INTO Main(WkStart, col1, col2, col3, col4)

  select WkStart, col1, col2, col3, col4

  from XYZ.Main join #periods

  on mperiod = period

  group by WkStart, col1, col2, col3, col4

  here col1, col2, col3, col4 are columns from XYZ.Main table. The number of columns vary from database to database. If it is ABC.Main table then the columns may be col1, col2, col3, col4, col5, col6. please let me know if there is a way to do this.

  Thanks,

  Sridhar.

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  November 3, 2005 at 2:38 am

  #602060

  There is - write a proc that suits each different database.

  Better, more robust and much less prone to errors and maintenance and debugging problems (problems you will get if you try to be 'one-size-fits-all-generic')

  /Kenneth

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 3, 2005 at 8:18 am

  #602153

  Hi Kenneth,

  Thanks for the reply. I have written a stored procedure that suits each case. I thought that there might be some generic way to do that.

  Thanks,

  Sridhar.