In SQL Server 2008, how should I copy data from database to another database? -


I am trying to write a stored procedure to copy a subset of data from a set of tables to a different The "source" database in the database requires a parameter to be stored functionality.

I have struggled with this for two days, and I thought I have a good solution:

  1. Validate that the schema is same.
  2. Create dynamic "rmt" synonyms for source tables using dynamic SQL.
  3. Use a copy of data to make a selection * from RMTA ou & lt; Criteria & gt;
  4. Delete synonyms.

This works very well for most tables, but for tables with an identity column, not only do I set IDENTITY_INSERT current & amp; Closed, but worse, I can not use SELECT *; I have to specify all the columns clearly if I add or remove columns later, this will be a nightmare.

I will go out of the door somewhat, so I am going to this solution now, but I want to think that there

It sounds like you 'are using dynamic SQL in your stored procedure, so you're ready to dynamically prepare your columns in the selected clause. Are there.

You can select from the sys.column to get the list of columns, and you can learn that there is an identity column in the table. Here is a query that shows you the information required to make the list of columns .

  SELECT c.name, is_identity FORM sys.columnsC WHERE Object_ID = Object_ID ('MyTable')  

In short, if at least one column For is_identity 1, you need to include the SET IDENTITY_INSERT . Also, you will remove any column from the SELECT section where is_identitude = 1.

And, this approach will be favorable for the new columns you add to the table.

Here is an example

  @TableName varchar (128) = 'MyTableName' declared @ColumnName varchar (128) declaration @IsIdentity bit declared @TableHasIdentity bit = 0 declared @ Sql varchar (2000) declaration = 'select' - Select the step through the list of columns, create cursor for c.name, c_name, c_name, c_name, c_name, c_name, c_name, object_id = object_id (@testname) column_id - Open the cursor and open the first line open MyCurs @ColumnName FETCH before MyCurs, @IsIdentity - start each column in the table WHILE @@ FETCH_STATUS = 0 if @IsIdentity = 0 - Chu Add column in section SET @sql = @sql + @ColumnName + ',' ELSE - indicates that the table has the identity column SET @TableHasIdentity = 1 - next column FETCH @columnName @IsI dentity end-cursor in the next column Cleansing MyCurs Reclaim MyCurs - FROM Segment Set @sql Link = Left (@sql, LEN (@sql) -1) + CHAR (13) + CHAR (10) + 'from' + @TableName - - Setup if necessary If you add identity, then @TableHasIdentity = 1 set @sql = 'SET identity_insert' + @TableName + '+ + CHAR (13) + CHAR (10) + @sql + CHAR (13) + CHAR (10) +' SET IDENTITY_INSERT '+ @TableName +' OFF 'print @sql  

Comments

Popular posts from this blog

Eclipse CDT variable colors in editor -

AJAX doesn't send POST query -

wpf - Custom Message Box Advice -