data from an updated database (column mismatch)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

data from an updated database (column mismatch)

Author

Topic

ludachrisvt
Starting Member

5 Posts

Posted - 2011-02-02 : 09:47:52

Hello!

Our production databases just got updated with some new fields. I'm trying to pull down some test data to our dev environment and am getting a column mismatch error as to be expected. Don't ask me why they won't update dev env's first ...

Anyway - my solution was to inner join the list of columns between the differing databases to get the "like columns" as the new columns aren't pertinent to my testing. Then use said list of column names for my original insert method.

The problem is that I don't know how to put the two together. Here is what I have :

DECLARE @columnname varchar(30)

insert into devdb.dbo.tablename
select @columnname = name
from dsnbsgmaster.dbo.claim
where controlcolumn = 'somevalue'
and name in
(
SELECT prod.name
FROM proddb.dbo.syscolumns prod
JOIN devdb.dbo.syscolumns dev
ON prod.name = dev.name
WHERE prod.id in
(SELECT id from proddb.dbo.sysobjects
WHERE name = 'tablename')
AND dev.id in
(SELECT id from proddb.dsnbsgdata.dbo.sysobjects WHERE name ='tablename'))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-11 : 10:06:32

Try this

select column_name from productiondb..information_schema.columns as pc where TABLE_NAME='table_name'
where not exists(select * from devdb..information_schema.columns where TABLE_NAME=pc.table_name)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-02-12 : 03:23:06

are your both dbs in same server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources