- How-To: Copy SQL table with identity to other database
-
Posted by oVan on Wednesday, May 28, 2008 | PermaLink | 0 commentsIf you ever wanted to copy a database table with contents to a different database, including the identity columns used for primary and foreign key relations, you might encounter an error message similar to this:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'D:\Db\Target.MDF.dbo.Languages' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Here is the working SQL Query code to solve this problem:
SET IDENTITY_INSERT [D:\Db\Target.MDF].dbo.Languages ON
GO
INSERT INTO [D:\Db\Target.MDF].dbo.Languages (LanguageID, LanguageCodeID, CountryID, NameType, Name)
SELECT LanguageID, LanguageCodeID, CountryID, NameType, [Name]
FROM [D:\Db\Source.MDF].dbo.Languages AS SourceTable
GO
SET IDENTITY_INSERT [D:\Db\Target.MDF].dbo.Languages OFF
GO
Note the use of IDENTITY_INSERT and the list of columns before the SELECT statement.
- Download SQL Server Compact 3.5 and Sync for ADO.NET 1.0
-
Posted by oVan on Tuesday, November 27, 2007 | PermaLink | 0 comments
Microsoft made available the Microsoft SQL Server Compact 3.5 and Microsoft Synchronization Services for ADO.Net v1.0 for Windows Desktop package on their download site.
SQL Server Compact 3.5 includes a host of new features including the following:
- LINQ to SQL enabling developers to integrate data access directly into their code
- Side-by-side installation together with SQL Server 2005 Compact Edition (version 3.1)
- Support for newer and more secure encryption algorithms
- Additional timestamp (rowversion) data type
- Enhanced support for Transact-SQL statements including:
- Nested query in FROM clause
- CROSS APPLY and OUTER APPLY
- CAST and DECIMAL
- SET IDENTITY INSERT
- TOP
- Synchronization Services for ADO.NET providing the ability to synchronize data from disparate sources over two-tier, N-tier, and service-based architectures
- Support for System.Transactions
More information:
- SQL Server Compact 3.5 Books Online
- Synchronization Services for ADO.Net Books Online
- For tips and tricks visit Steve Lasker blog, Synchronization Services for ADO.Net blog and SQL Server Compact blog
- For replicating data between SQL Server Compact 3.5 or SQL Server 2005 Compact Edition and SQL Server 2005 (or later versions of SQL Server 2005) install SQL Server Compact 3.5 Server Tools on the IIS server
- Microsoft Visual Studio 2008 installs SQL Server Compact 3.5 and Synchronization Services for ADO.Net by default. If you have already installed Visual Studio 2008 than you don't need to separately download and install SQL Server Compact 3.5