Copy table data and populate new columns

So I'm trying to copy some data from database table to another. The problem is though, the target database table has 2 new columns that are required. I wanted to use the export/import wizard on SQL Server Management Studio but if I use that I will need to write a query for each table and I can only execute 1 query at a time. Was wondering if there are a more efficient way of doing it.

Here's an example of 1 table:

dbase1.dbo.Appointment { id, name, description, createdate }
dbase2.dbo.Appointment { id, name, description, createdate, auditby, auditat}

I have a total of 8 tables with those 2 additional columns. and most of them are related to each other via fk, so I wanted to use the wizard as it figures out which table gets inserted first. The problem with that is, it only works if I do a "copy data from one or more tables " and not the "write a query to specify data" (I use this to populate those two new columns).

I've been doing this very slow process in copying data as I'm using MVC Code First for my application and I dont have access to the server to be able to drop and create the table at my leisure. So I have to resort to this to maintain the data that I already have.

Answers


An idea: temporarily disable the foreign key constraints in the destination database. Then it doesn't matter what order you run your inserts. In order to populate the two new and required columns, you just need to pick some stock values to put in there (since obviously these rows initially are not subject to initial auditing). For example:

INSERT dbase2.dbo.appointment
(id, name, description, createdate, auditby, auditat)
  SELECT id, name, description, createdate, 
    auditby = 'me', auditat = GETDATE()
  FROM dbo.appointment;

Since it seems the challenge is merely that the destination requires columns that aren't in the source, and that you need to determine what should be populated in these audit columns, this seems to solve multiple problems at once. You just need to figure out what to put in there instead of 'me' and GETDATE().

(To get the wizard to pull these 8 tables for you, you might be able to create a view similar to the select portion of the above query, but that's more work and it won't see the underlying FK constraints to generate them in the right order anyway.)


Write the sql query for each of the insert processes in the order you want it. That would be the simplest approach.


Set the Default values for these two columns

Like for AuditAt - Default Date i.e. GetDate() For AuditBy - The Person ID/Name

Now, you can Insert into these tables without entering for these two columns


Need Your Help

How to change 'Django Administration' name to a custom name in admin login and admin page

django django-templates django-admin

I am trying to change the name of django administration to custom name how do i do that.Is there any way completely customize the admin page give it more professional look

Migrated from TFS to GIT to SVN. In SVN all the version dates are resetting to current date instead of historic dates

git svn version-control tfs

We are retiring TFS Box and moving our Repo from TFS to SVN. We could sucessfully migrate from TFS = GIT = SVN , however the code history information contains current dates instead of historic date...