Trying To Automate Copying the Contents of Multiple Tables

Question

Now, in my assigned quest to migrate - well, "duplicate" is more accurate - an existing database over to another server, while the original remains in production, I have been instructed to copy the contents of what had been made into views into actual tables.

Here's the progression;

Originally, I had been tasked to create views of certain existing tables so that, as the original tables were updated in the existing database, the views in the new one would always be looking at current data, rather than having to update multiple copies of the original tables. So;

old_db.tables => new_db.views

Now, they want the views so created to start from where the old tables left off, but to contain new data unique to the new database. So;

new_db.views => new_db.tables.

There are about 40 views affected by this and I've been laboriously going to the SELECT TO scripter, C&Ping the fields from the created script into the fields for the INSERT TO code that I'm creating.

INSERT INTO new_table
	(
	column_1,
	column_2,
	...
	column_n
	)
SELECT
	column_1,
	column_2,
	...
	column_n
FROM old_table
GO

But, it's tiring to C&P all the columns for each new table.

So, I tried to automate it using this code;

INSERT INTO new_table
	(
	SELECT column_name
	FROM information_schema.columns
	WHERE table_name = 'old_table'
	ORDER BY ordinal_position
	)
SELECT
	(
	SELECT column_name
	FROM information_schema.columns
	WHERE table_name = 'old_table'
	ORDER BY ordinal_position
	)
FROM old_table
GO

I figured I could use an outside SELECT statement to go through each of the tables I need to copy and then just hit, "Execute," and bang-o, zippo presto voila! all the tables would be populated with the data from the old database just ready and willing to accept the new data when it goes to production.

Unfortunately, no good. I get a syntax error at the SELECT in the INSERT TO block at the top.

I figure I'm maybe being too clever for my own good and will return to the C&P method. Of course, I'll probably have the task done by the time I can get an answer here - it's not that large a task, really - but, I'd like to get a handle on the technique, if there's a way to do what I'm thinking, for future use. And, on that point, we will soon be copying the same old database to another new one for another project. So, I'd like to automate the process as much as possible.

Thanx!

Answer 1

Hi Adam,

Out of curiosity, if your goal is to duplicate (copy, migrate) production database to another server, why have you decided to use insert/select? This is huge waste of time.

There are dozens of the ways how to accomplish that. You can restore the database from the recent backup (or, if you need, backup your production database with COPY ONLY option). You can setup log shipping for the migration. If you need to move data only, you can use import/export data wizards, SSIS, bcp.

The list is by no means completed. Either of those options would be easier to implement. And in most part of the cases would be faster to run.

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 2

Hi Adam,

Out of curiosity, if your goal is to duplicate (copy, migrate) production database to another server, why have you decided to use insert/select? This is huge waste of time.

Well, mainly because my experience is, until recently - like, this new employer - virtually all Access. In Access, it's simple; just select a table, Ctrl-C, Ctrl-V, et voila! The task, she iz done, mon ami.

Also, because not the entire database is being selected, only select parts of it.

But, I've found SQL Server to be far more complicated on, what were in Access, seemingly simple tasks.

Now, to be honest, I've discovered - via help here and exasperated searching on my part - ways of speeding up some SQL tasks, but these solutions seem to always require running a wizard or telling SQL to make a script. It seems that many tasks that were easily accomplished in Access are way more difficult in SSMS. This seems to be a backward progression to me. I would expect that what was easy in Access would remain easy in SSMS and that some things that were difficult in Access would be made easy in SSMS. Essentially, an increase in ease of use. But, that basic idea isn't what happened.

It seems that some of the things in SSMS are difficult merely so those experienced in the package can sniff and look down their noses at those peons who are merely experienced in Access.

But, maybe I'm biased because I learned on Access, rather than SSMS.

Answer 3

As it has turned out, management has changed their minds. They now want the new database to start from scratch and dispense with copying over the old data.

So, all I have to do is create the table structure and that's it.

My problem on this thread is now a non-issue.