SQL：Insert into语句，但假设您不知道原始和目标标题 [英] SQL: Insert into statement, but suppose you do not know origin and target headers

问题描述

我试图想出一个插入sql stament，将插入数据从一个表到另一个现有的表。事实上，有一些方法可以做到这一点，但我没有找到一个符合我的要求的方法。

I am trying to come up with an insert sql stament that will insert data from a table into another existing table. There are in fact some ways of doing this but I did not find a way that matches my requirements.

我需要一个插入语句类型查询，将数据插入另一个表但它不知道这两个表有哪个头。例如，origin表有25个头，目标有20个，其中10个名称相匹配。

I need an insert statement type query that will insert data into another table but it does not knows which headers both table have. For instance the origin table has 25 headers and the target one has 20, which 10 of them match in name. I would like to transfer the ones that are matching the name of the headers ignoring the rest.

希望我很清楚，希望任何人都能够帮助我。

Hope I was clear and hope anyone will be able to help me

推荐答案

我想你必须获取两个表列，然后过滤em以获取匹配列名称，之后，您可以构建 insert into 语句并执行

I think you have to get the two tables columns then filter em to get match columns names after that you can build your insert into statement and exec it

以获取表中的列

Declare @cols varchar(max)

SELECT  @cols =COALESCE(@cols +',','')+'['+COLUMN_NAME+']'
FROM DbName.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = N'security' and COLUMN_NAME in( 
      SELECT COLUMN_NAME
      FROM DbName.INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_NAME = N'debt_securities')

然后创建insert语句

then create insert statement

declare @query varchar(max)='insert into debt_securities('+@cols+')
                             select '+@cols+' from [security]'

然后执行

exec(@query)

这篇关于SQL：Insert into语句，但假设您不知道原始和目标标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！