June 9, 2011 at 2:07 am
Hi, I would like to export data from database A to Database B .
Database B must be identical in structure but it must only contain data
if the client record's LastName = "Smith".
Can anyone help me with this ?
I have searched but I cant find info on this.
Regards,
June 9, 2011 at 3:51 am
You have many options.
Is it a one-shot export or you have to it regularly?
Both databases on the same instance or different instances?
Are the tables identical or you have different structures?
Some methods that come to my mind:
1) Use the import/export wizard
2) Create a SSIS package with BIDS
3) Use a linked server
4) Set up merge replication
If you provide more info, you can get a better answer.
-- Gianluca Sartori
June 9, 2011 at 3:58 am
Okay , lets use this as an example.
MS SQL R2 Express 2008
Big live database has 10000 records. I want to use only a small portion of that for testing and development.
So, I would like an Identical database but without the bulk. Let say ... 500 records.
So I guess, more accurately, this would be a conditional split ?
Thanks for the answer, will take a look at the options you provided.
Regards,
June 9, 2011 at 4:00 am
It would be a one-shot deal.
Same instance.
Identical tables.
Gianluca Sartori (6/9/2011)
You have many options.Is it a one-shot export or you have to it regularly?
Both databases on the same instance or different instances?
Are the tables identical or you have different structures?
Some methods that come to my mind:
1) Use the import/export wizard
2) Create a SSIS package with BIDS
3) Use a linked server
4) Set up merge replication
If you provide more info, you can get a better answer.
June 9, 2011 at 4:03 am
This should be enough:
INSERT INTO DatabaseB..DestinationTable
SELECT *
FROM DatabaseA..SourceTable
WHERE LastName = 'Smith'
-- Gianluca Sartori
June 9, 2011 at 4:11 am
zaleeu (6/9/2011)
Okay , lets use this as an example.MS SQL R2 Express 2008
Big live database has 10000 records. I want to use only a small portion of that for testing and development.
So, I would like an Identical database but without the bulk. Let say ... 500 records.
So I guess, more accurately, this would be a conditional split ?
Thanks for the answer, will take a look at the options you provided.
Regards,
It's a quite common requirement and it's not as simple as it might seem.
The dev DB should contain actual production data? It's not always possible (privacy policies etc.)
When you have foreign keys constraints, you have to ensure that all tables contain consistent data. Even if you have no constraints, you could end up with non matching data.
Some considerations can be found here: http://www.simple-talk.com/sql/database-administration/obfuscating-your-sql-server-data/
Hope this helps
Gianluca
-- Gianluca Sartori
June 9, 2011 at 4:12 am
Thanks !
I need all the tables though , since I want the databases to be identical . One just smaller than the other.
-----------------------
Gianluca Sartori (6/9/2011)
This should be enough:
INSERT INTO DatabaseB..DestinationTable
SELECT *
FROM DatabaseA..SourceTable
WHERE LastName = 'Smith'
June 9, 2011 at 4:26 am
zaleeu (6/9/2011)
Hi, I would like to export data from database A to Database B .Database B must be identical in structure but it must only contain data
if the client record's LastName = "Smith".
Can anyone help me with this ?
I have searched but I cant find info on this.
Regards,
Just a small suggestion. Please someone confirm whether it is possible.
1) Create two databases Database A and Database B with identical table structure.
2) Create an SSIS package. Create a sequence container. In that declare an Execute SQL task giving the condition to insert the records from tables in Database A to identical tables in Database B only when the client's LastName = "Smith"
June 9, 2011 at 4:27 am
If you just want them smaller, you can use this script.
Just make sure that foreign keys are resolved.
DECLARE @tableName sysname
DECLARE @schemaName sysname
DECLARE @sql nvarchar(max)
DECLARE tabs CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT name, OBJECT_SCHEMA_NAME(object_id)
FROM DatabaseA.sys.tables
OPEN tabs
FETCH NEXT FROM tabs INTO @tableName, @schemaName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql =
N'INSERT INTO DatabaseB.'+ QUOTENAME(@schemaName) +'.' + QUOTENAME(@tableName) + '
SELECT TOP 20 PERCENT *
FROM DatabaseA.'+ QUOTENAME(@schemaName) +'.'+ QUOTENAME(@tableName)
PRINT @sql
EXECUTE(@sql)
FETCH NEXT FROM tabs INTO @tableName, @schemaName
END
CLOSE tabs
DEALLOCATE tabs
-- Gianluca Sartori
June 9, 2011 at 4:34 am
Great 😀 Thanks !
June 9, 2011 at 6:42 am
I'd always tackled this the other way...backup and restore to the dev database, then DELETE any data that doesn't match the saving criteria...works great if your FK's have the DELETE CASCADE options in place.
after that, we often would create a randomizer script that would change sensitive data...names, addresses, phone numbers, etc to fake random data.
that portion was a script that took some time to set up, but once save,d we could use it on demand.
Lowell
June 9, 2011 at 6:52 am
Lowell (6/9/2011)
I'd always tackled this the other way...backup and restore to the dev database, then DELETE any data that doesn't match the saving criteria...works great if your FK's have the DELETE CASCADE options in place.after that, we often would create a randomizer script that would change sensitive data...names, addresses, phone numbers, etc to fake random data.
that portion was a script that took some time to set up, but once save,d we could use it on demand.
This is a good way to solve it. I suspect the OP doesn't have any FKs in place, or my script would never have worked.
-- Gianluca Sartori
June 9, 2011 at 6:58 am
Ooops 🙂 .
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_LabLocationAnalysis_LabAnalysis". The conflict occurred in database "PM222", table "dbo.LabAnalysis".
The statement has been terminated.
INSERT INTO PM222.[dbo].[TrClaimBasis]
SELECT TOP 20 PERCENT *
FROM PM.[dbo].[TrClaimBasis]
---------------------------------------
No worries, I will try the other way 🙂
Thanks for all the help !
-----------------------------
Gianluca Sartori (6/9/2011)
Lowell (6/9/2011)
I'd always tackled this the other way...backup and restore to the dev database, then DELETE any data that doesn't match the saving criteria...works great if your FK's have the DELETE CASCADE options in place.after that, we often would create a randomizer script that would change sensitive data...names, addresses, phone numbers, etc to fake random data.
that portion was a script that took some time to set up, but once save,d we could use it on demand.
This is a good way to solve it. I suspect the OP doesn't have any FKs in place, or my script would never have worked.
June 9, 2011 at 7:08 am
Hehe. Try Lowell's suggestion, then.
Good luck!
-- Gianluca Sartori
June 9, 2011 at 7:53 am
Okay how do I drop all the contraints ?
If I try to delete I get
The DELETE statement conflicted with the REFERENCE constraint "FK_ReportLog_Client"
=========================
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply