September 13, 2010 at 3:04 pm
Hi All,
I have a XYZ Database (50 Tables init and lots of data ) on 01 Server
and
I need to create a XYZ_MOCK Database on same Server 01 with 20 tables of XYZ Similarly with the exact data ....
(((for example :
table 'tyu' having FK with 'pot'
so i need to have table 'tyu 'and 'pot' and also Structure should be same ))))
I need to get data so i used
Select * into XYZ..Destination table from XYZ_MOCK..Source Table
(Note:not to use import export ,or DTS import/Export because we need Scripts )
I got the Structure,data similar and exactly same as in XYZ to XYZ_MOCK
....But,
PK and FK are missing (SP,Views,Triggers also)...doesnt Show up
I thought of running an alter table and adding PK and FK and FK related tables..and just copy and paste SP's ,Triggers ...but i donno to do this way or not ....
can you guys please help me on it ...
And is there any Script to say that table 'qwe' is having FK relatinoship to or with table 'yu'...
So that i can directly add a FK related table in XYZ_MOCK DB...
Thanks
September 13, 2010 at 3:18 pm
Hi Nari
I'm not really sure what your asking, but I think you might just need to back up XYZ and restore as XYZ_MOCK
MCITP SQL Server 2005/2008 DBA/DBD
September 13, 2010 at 3:23 pm
If it were me, I'd script it all out. From what I've read, I'd suggest you use the Import and Export Wizzard to copy the required tables. You have the option to include data or not, and there will be options asking what related objects like indexes, keys, or triggers you want to copy. Prior to clicking the Finish button, you can save the configured export task to an SSIS package, so it can be re-run again at a later time if needed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 13, 2010 at 3:33 pm
Thanks for the answers .I do appreciate that
But If i do backup and restore ...i will be having all 50 tables ...
but i need only 20 tables ..Structure Shud be same and also the data in selected 20 tables ...
and i tried using Export ..i having problems with Readonly columns ..So i must use Scripts i guess
September 13, 2010 at 3:40 pm
What do you mean by ReadOnly columns?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 13, 2010 at 4:59 pm
I am Sorry ......
my requirement changed
They asked me to create a XYZ_MOCK database so creatde that and I did Copied Scripts From XYZ and ran it in XYZ_MOCK database...now tables Structues are same for 20 selected tables...
Now there are 20 tables with lots of lots of Records in it ......
All i want is now I need to move all the records from XYZ DB to XYZ_MOCK. DB (20 Selected Tables)..
for ex:
From
Server01-XYZ Database -table abc (20columns and 1000rows)
TO
Server01 - XYZ_MOCK Database table abc(20 columns and 100rows)....
How can i Insert all records for 20 tables so that my Selected tables in Xyz and XYZ_MOCK lokks same
Thanks
September 13, 2010 at 5:45 pm
There are a couple of ways you can do this.
1. BCP the data out of one system into the other
2. Use Microsoft's import tool (which will let you work across instances)
3. Roll your own scripts along the lines of
INSERT <TableA_Mock>
SELECT * FROM <Production>..<TableA>
September 16, 2010 at 5:34 am
Hi
U can also Use
select * from XYZ into XYZ_MOCK
Ali
MCTS SQL Server2k8
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply