A quick SQL Server tip

Posted At : February 12, 2010 2:39 PM | Posted By : Andy Sandefer
Related Categories: ORACLE, RDBMS, SQL Server, MySQL

Have you ever wanted to quickly migrate selected table data back and fourth from your dev database to your live database? If the answer is yes then this one's for you.

So to begin with, this example is setup for databases that are on the same server. I will say that I've used this simple technique for ORACLE to SQL Server migration (this requires a linked server definition in most cases) and SQL Server to SQL Server migration as well, but for now let's just keep it simple.

The first thing that you need to do is verify that your schema for each target table are identical in terms of table structure as well as constraints. Once you've gotten that out of the way it is as simple as this...

view plain print about

1USE DEVDB
2GO
3
4SET IDENTITY_INSERT Vendor ON
5GO
6INSERT INTO Vendor
7    (
8    VendorID,
9    VendorName,
10    AccountNo,
11    AccountManagerAppUserID,
12    Address,
13    Address2,
14    City,
15    StateCode,
16    PostalCode,
17    CountryCode,
18    Phone,
19    Phone2,
20    Fax,
21    Fax2
22    )
23SELECT a.VendorID,
24            a.VendorName,
25            a.AccountNo,
26            a.AccountManagerAppUserID,
27            a.Address,
28            a.Address2,
29            a.City,
30            a.StateCode,
31            a.PostalCode,
32            a.CountryCode,
33            a.Phone,
34            a.Phone2,
35            a.Fax,
36            a.Fax2
37FROM    LIVEDB.dbo.Vendor a
38GO

In the example above we're trying to update the DEVDB with master records (Vendor table entries) from the LIVEDB. I tried to show a "toughest case scenario" here where the table has rules dictating that the primary key is manufactured (IDENTITY). The SET IDENTITY_INSERT Vendor ON statement allows us to bypass the table's constraint, which in a populated database table would be dangerous, however as we're just copying master records from live to an empty table in dev - we're safe. This rule does not apply when both tables have a user defined primary key, like a subsidiary (lookup) style table which may use code values like RED, BLUE, YELLOW, etc. for primary key values. In that instance we could have simply done this...

view plain print about

1USE DEVDB
2GO
3
4INSERT INTO ProductColor
5SELECT a.*
6FROM LIVEDB.dbo.ProductColor a
7GO

So there you have it, a simple way to migrate select areas from one database to another. Remember that you only need to use a named column list if you're dancing around constraints, otherwise just go for the SELECT * and tell them "Andy sent me".

Don't forget to be creative with this as it can be used in Stored Procedures and SQL Server Jobs as well. I have personally fetched data from ORACLE and imported it into SQL Server on an hourly basis using this technique. As I mentioned earlier, you must use a LINKED SERVER for heterogeneous data manipulation.

Questions and Comments are welcome. I haven't blogged in a long time but I'm back now and I'm going to try to be a bit more frequent with future posts.