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

SQL Server 2005 Forums

Transact-SQL (2005)

data from an updated database (column mismatch)

Author

Topic

ludachrisvt
Starting Member

5 Posts

Posted - 2011-02-02 : 09:58:37

Hello!

I accidentally posted this in the beginners section. Sorry about that gals/guys.

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

ludachrisvt
Starting Member

5 Posts

Posted - 2011-02-02 : 10:21:59

further explanation:

This statement gives me a list of overlapping columns
:

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

And this statement moves my data:

insert into devdb.dbo.tablename
select *
from dsnbsgmaster.dbo.claim
where controlcolumn = 'somevalue'

I need to mash them together to move data on the columns I selected in the first statement.

ludachrisvt
Starting Member

5 Posts

Posted - 2011-02-02 : 10:24:02

quote:
Originally posted by ludachrisvt

further explanation:

This statement gives me a list of overlapping columns
:

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

And this statement moves my data:

insert into devdb.dbo.tablename
select *
from dsnbsgmaster.dbo.claim
where controlcolumn = 'somevalue'

I need to mash them together to move data on the columns I selected in the first statement.

also please replace dsnbsgmaster with proddb for example purposes - copy paste error:

insert into devdb.dbo.tablename
select *
from proddb.dbo.claim
where controlcolumn = 'somevalue'

ludachrisvt
Starting Member

5 Posts

Posted - 2011-02-02 : 10:25:56

quote:
Originally posted by ludachrisvt

quote:
Originally posted by ludachrisvt

further explanation:

This statement gives me a list of overlapping columns
:

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

And this statement moves my data:

insert into devdb.dbo.tablename
select *
from dsnbsgmaster.dbo.claim
where controlcolumn = 'somevalue'

I need to mash them together to move data on the columns I selected in the first statement.

also please replace dsnbsgmaster with proddb for example purposes - copy paste error:

insert into devdb.dbo.tablename
select *
from proddb.dbo.claim
where controlcolumn = 'somevalue'

ugh I can't edit posts:

insert into devdb.dbo.tablename
select *
from proddb.dbo.tablename
where controlcolumn = 'somevalue'

orginal post fixed:
DECLARE @columnname varchar(30)
insert into devdb.dbo.tablename
select @columnname = name
from proddb.dbo.tablename
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'))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources