Create an 'Insert Into' statement with schema and data from existing table.. possible?

Question

Create an 'Insert Into' statement with schema and data from existing table.. possible?

samalex

SSCertifiable

Points: 5429
More actions
October 8, 2009 at 9:37 am

#210751

Hi,
I need to deploy data from one database server to another, but they're not on the same network. The table being copied over needs to basically be dropped and recreated, then repopulated.
What I'd like to do is find some process that'll take an existing table on a Source server and create a TSQL script with the Create statement to create the table along with any constraints, then Insert Into statements to populate the data with whatever's in the table on the Source Server. Ideally this can then be ran on the Destination Server to recreate the table, constraints, and data from the Source SErver.
With only a hand full of tables to copy over it's not really ideal to mess with backups and so forth, and with these servers being in different locations it's not possible to link them.
Thanks for any suggestions or simple solutions on how to do this. Granted I can write a stored procedure to piece this all together, but can SSMS or VS DBA do this?
Thanks --
Sam

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

465789psw Hall of Fame Points: 3314 More actions · Answer 1

I am little lost, the other server is not on the network?

so how are going to copy and move the data, the script part just go to SSMS, your table then right click on the table edit.

Sorry if i miss understanding a bit.

Lowell SSC Guru Points: 323518 More actions · Answer 2

two links in my signature for CREATE TABLE and INSERT INTO will do exactly what you want, but in TSQL vs an external process:

sp_GetDDL takes a tablename, or optionally schema.tablename, and returns the CREATE TABLE DDL statement, including EVERYTHING...indexes,constraints and a lot more.

Narayana Vyas Kondreddi's sp_generate_inserts procedure takes a tablename and generates INSERT INTO[yourtablename] DML statements.

works greate, as long as you are not moving a million rows of data; anything over 10K rows, and I'd consider using bcp to export and import the data out to a file and then isnert it again.

simple to use together:

exec sp_getDDL YourTable

exec sp_generate_inserts YourTable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

samalex SSCertifiable Points: 5429 More actions · Answer 3

Sorry for the delayed reply...

Lowell - I'm checking out your stored procedures now and I think those might just do the trick. Thanks!

465789psw - The problem is the server I'm deploying to is not on our local network, instead it's on a remote network and my only access into it is through Terminal Services. Because of this I can't connect through SSMS, so to deploy anything the simplest way is to save it as a .sql file, copy it to the remote server, then execute it there. This is quite simple for schema changes but I didn't know of any canned way to get table content changes into a .SQL file. Granted this isn't ideal for LARGE tables, but most of what we'll be deploying have 100 or less rows.

Thanks for the replies and take care --

Sam

Lowell SSC Guru Points: 323518 More actions · Answer 4

glad i could help sam; let me know if this wroks out for you.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!