SolvedPrivate

Create table with data

Posted on 2013-11-20
9
36 Views
Last Modified: 2016-02-11
Is there a way to generate a script on a table that will
Create the table if not exists
Insert the current data
0
Comment
Question by:lrbrister
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 25

Expert Comment

by:SStory
Comment Utility
From Management Studio

Right-click the table, from the menu "Script Table as - > CREATE to->"
This will recreate the table
0
 
LVL 25

Accepted Solution

by:
SStory earned 500 total points
Comment Utility
Better yet, select the database, right-click, Tasks->Generate Scripts and you should be able to do anything.
0
 

Author Comment

by:lrbrister
Comment Utility
SStory,
 That doesn't get me the data to insert as well
0
 
LVL 25

Expert Comment

by:SStory
Comment Utility
When it gets to Set Scripting Options, click the Advanced button and change
Types of data to script to "Schema and data"

You do actually have to look around in these tools to get them to do what you need, but this should do it.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Closing Comment

by:lrbrister
Comment Utility
In the Generate Scripts Advanced Tag was the ability to generate schema as well as data.

Thanks
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>Insert the current data
None that I'm aware of that will save it as a script.  Have fun with that.

The work-around that is easiest to pull off would be to export the table data as a .csv, save it with the script, then the target db will have to...
Execute the CREATE TABLE script
Do an SSIS / Import wizard to connect to the .csv as the source, the created table as the target, and execute.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
If the table doesn't exist, what "current data" are you talking about?

If you mean copying data from one db to another using the same table name, then, yes, there are ways to do that.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>Advanced tab, Types of data to script ... Schema and Data
Learn something new every day.  Now that's handy..
0
 

Author Comment

by:lrbrister
Comment Utility
Yes it is
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Move SQL 2005 Express to Server 2012R2 19 68
c# code 19 56
Link SQL table to Webpage 9 35
SQL Server memory Issue 7 73
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now