469,902 Members | 1,824 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,902 developers. It's quick & easy.

Mapping tables

i have to popuate one table(say sales) in database with the values in another table(say temp). the mapping details of sales and temp table exist in a table called mapper.

how can i populate(insert values into) sales table's fileds with temp table's fields WITHOUT using the column names of the tables, but instead i have to use the mapper table.

mapper table( SalesFieldName, TempFieldName)
Sep 7 '07 #1
4 4971
azimmer
200 Expert 100+
i have to popuate one table(say sales) in database with the values in another table(say temp). the mapping details of sales and temp table exist in a table called mapper.

how can i populate(insert values into) sales table's fileds with temp table's fields WITHOUT using the column names of the tables, but instead i have to use the mapper table.

mapper table( SalesFieldName, TempFieldName)
Compile the proper INSERT statement into a string then EXEC the string.
Sep 7 '07 #2
Compile the proper INSERT statement into a string then EXEC the string.



but my problem is i cannot write in the COLUMN NAMES, neither in insert part nor in values part.
i have to select colum names dynamically from mapping table.
Sep 10 '07 #3
azimmer
200 Expert 100+
but my problem is i cannot write in the COLUMN NAMES, neither in insert part nor in values part.
i have to select colum names dynamically from mapping table.
Easy, easy. When I said you should compile the statement into a variable I meant something like this (not actually tried (I cannot now) but should illustrate the point):
Expand|Select|Wrap|Line Numbers
  1. declare @columns as varchar(255)
  2. declare @sqlstatement as varchar(1024)
  3.  
  4. select @columns = columnnamecol+',' from mappingtable
  5. set @columns=left(@columns,length(@columns)-2)   ' remove trailing comma
  6.  
  7. set @sqlstatement = 'INSERT INTO datatable (' + @columns
  8. set @sqlstatement = @sqlstatement + ') VALUES (1,2)'
  9.  
  10. exec (@sqlstatement)
  11.  
Sep 10 '07 #4
Easy, easy. When I said you should compile the statement into a variable I meant something like this (not actually tried (I cannot now) but should illustrate the point):
Expand|Select|Wrap|Line Numbers
  1. declare @columns as varchar(255)
  2. declare @sqlstatement as varchar(1024)
  3.  
  4. select @columns = columnnamecol+',' from mappingtable
  5. set @columns=left(@columns,length(@columns)-2)   ' remove trailing comma
  6.  
  7. set @sqlstatement = 'INSERT INTO datatable (' + @columns
  8. set @sqlstatement = @sqlstatement + ') VALUES (1,2)'
  9.  
  10. exec (@sqlstatement)
  11.  

hey thanks a lot.
it worked
Sep 11 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jon Sequeira | last post: by
reply views Thread by Bill | last post: by
reply views Thread by Bill | last post: by
reply views Thread by Ads B | last post: by
1 post views Thread by Ram | last post: by
1 post views Thread by MattM | last post: by
reply views Thread by ishakteyran | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.