1

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.

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

Raidri
  • 17,258
  • 9
  • 62
  • 65
Tiago Martins
  • 129
  • 2
  • 11

2 Answers2

0

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

to get columns exists in both table

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')

then create insert statement

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

then execute it

exec(@query)
Amir Ismail
  • 3,865
  • 3
  • 20
  • 33
  • Thank you so much for your reply. But for some reason I am not being able to work it. The two tables in question are: security (origin) and debt_securities (target) – Tiago Martins Jul 27 '11 at 13:31
  • Obviously this is a quick answer to a quick question, but would be better using QuoteName() to handle column names that contain spaces, etc. – Tao Jul 27 '11 at 16:13
  • @Tao thanks for your note, I edited it but I used brackets not `QuoteName()` – Amir Ismail Jul 27 '11 at 16:55
  • I'd use quotename as @tao suggested or you'll have trouble handling the column "foo[]bar" which is valid, quotes to [foo[]]bar] – Chris Chilvers Jul 27 '11 at 17:02
0

I can not think of a single query which can do all this, but you can definitely write a sql server procedure to do this.

you can get the list of column names and datatype of the columns for the source and destination table from the following query

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'TABLENAME'

With this you can run a loop in the pl/sql to find out matching columns based on name and datatype and then form a on the fly dynamic plsql and execute it. this should solve your purpose i guess.