February 18, 2008 at 6:35 am
Hi. I have been working on a product configuration tool. Rather than have to manually replicate what I have done on I would like to copy the data from 'TableA' in 'DatabaseA' to 'TableA' in 'DatabaseB'. Hope that makes sense!!
I am new to SQL so would appreciate a recommendation on a good book regards learning how to write SQL queries ( I really am a poor programmer so do not hole out much hope for T-SQL) lobotomy might help!!
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 18, 2008 at 6:55 am
So the databases reside at the same server instance ?
Did you use DRI (Foreign keys) ?
For the first time only ! you could use this :
If yes :
- Did you use identity columns ?
if yes
use your_destination_db
go
begin transaction
Set identity_insert your_target_table_name on
insert into your_target_table_name ( you need to specify every column
overhere )
select T1.*
from [the_source_db].[the_schema].[The_Source_Tablename] T1
where not exists (select 1 from [the_target_schema].[The_target_Tablename] T2
where T2.keycolumn = T1.keycolumn)
if @@error <> 0 rollback transaction
Set identity_insert your_target_table_name off
commit transaction
go
If you didn't use an identity column, you can skip the set identity_insert statements.
Be careful, if you use the keys in related tables, you may end up with
buggy relations.
You can also use SSIS (integration service) to migrate data, and that may be your choice if this is a recurrent activity.
This can be done with BI-dev studio. Create a new project, choose Business Intelligence integration service project.
Then launch the wizard that you can find in the project menu.
(ssis import export wizard)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 18, 2008 at 7:11 am
Thanks for your time. SSIS sounds like a plan.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 18, 2008 at 8:55 am
I have been having a play trying to import data using SSIS but am unable to resolve errors;
Validating (Error)
Messages
Error 0xc0202049: Data Flow Task: Failure inserting into the read-only column "ConfiguredComponent".
(SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task: Column metadata validation failed.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination - ConfiguredComponents" (67)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
I deleted all values within tables from DatabaseA where I was looking to import to.
I ran SSIS to import data from same tables in DatabaseB into DatabaseA.
Any ideas regards the error? (or where I am going wrong!!!!).
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 19, 2008 at 12:18 am
As you have noticed, SSIS can break at ## of places :crying:
And the wizard isn't as intuitive as the one for dts (sql7/sql2000)
You can enable ssispackage logging.
IMO SSIS is something you may want to get a sourse wize introduction of. It is a programming environment, just like VS.net.
Extreemly powerful, but very fragile.
- Are you inserting into a view or a table ??
- Did you check the keep-identity property ? (read only column)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 19, 2008 at 5:42 am
Hi. I am inserting into a table. I did not check the keep identity option.
I have given up trying for now!! I will have a re-think.
Thanks for your time and efforts.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 27, 2008 at 4:31 pm
You do want to check keep identity if you are bringing that field from the source data base.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply