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.
| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2011-11-01 : 20:28:53
|
| I have two tables (SectionInfo and SectionCategories) in my that are identical in both my dev and production DBs. This inclused a PK idenity colum. The SectionInfo table has over 1000 rows and the SectionCategories nearly 3x that much. I have tried a normal import data but it errors out since it is trying to input the PK data from the dev DB into the PK column in the production DB. Does anyone know an eays way to transfer the data accross the DBs?-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2011-11-01 : 21:38:21
|
| If I could keep the identity it would be nice, but it is not needed. I am not sure what a linked server is, but both DBs run on the same server. I am not sure what type of relationship your looking for but they are 100% identical in design, the only difference is the data inside a table.-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-02 : 05:40:39
|
"INSERT INTO Table1SELECT * FROM Db2.dbo.Table1"I think you have to have a full column list for the INSERT with IDENTITY_INSERT ON  (Anyway, its easy enough to add if SQL gives an error using just INSERT INTO Table1) |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2011-11-02 : 06:07:18
|
quote:
Originally posted by tkizer
I mean the relationship between SectionInfo and SectionCategories. If there's an id that needs to stay in sync, well that matters.If they are on the same server, then it's easy. Here is an example.SET IDENTITY_INSERT Table1 ONINSERT INTO Table1SELECT * FROM Db2.dbo.Table1SET IDENTITY_INSERT Table1 OFFTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Yes, there is a relasion ship between the two tables. SectionCategories has a column SectionID which is a FK to the PK Column ID in SectionInfo.-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2011-11-02 : 19:34:44
|
| Thanks the set identity insert worked, and you do need an explicit column list-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Copy Data from DevDB to ProdDB
Tara Kizer