Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-14 : 07:16:26
|
| Sue writes "How do I generate a SQL script to create a table AND insert all the values already in the table. I have used the Generate SQL Scripts but I only get the table creation" |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-06-14 : 07:35:39
|
| SueI am really surprised that there isnt a handy script on this site to do just this.I have to say that long ago I got into the habit of using scripts to build my database, the scripts for the seed data were created at the same time.If you are looking to transfer your database to a different server, then I would consider BCP - there are many articles on this here.For small amounts of data, you could produce the results in Query Analyser and edit in the relevant insert commands (bear in mind the need to allow Identity Insert if using an Identity field for keys)Graham |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-14 : 14:16:12
|
quote:
Originally posted by AskSQLTeam
Sue writes "How do I generate a SQL script to create a table AND insert all the values already in the table. I have used the Generate SQL Scripts but I only get the table creation"
Alexander Chigrik has a script that will generate INSERT statements for all the data in a [comma delimited] list of tables:http://www.mssqlcity.com/Scripts/ImpExp/DataAsInsertCommand.sqlKristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-14 : 14:42:06
|
| Why don't you just bcp the data out and in...it's really very easyBrett8-) |
|
|
|
fan
Starting Member
10 Posts |
Posted - 2004-06-23 : 08:45:03
|
| Sorry, what do you guys mean BSP? |
|
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-06-23 : 11:43:45
|
| BCP? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-23 : 12:42:25
|
| Sorry..bcp...but if it's the same database and/or server...how about SELECT * INTO myNewTable99 FROM myOldTable99Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-23 : 16:46:20
|
quote:
Originally posted by fan
Sorry, what do you guys mean BSP?
I love this! You made a typo and the guys thought it was them, so they corrected the typo but didn't answer your question!Assuming I've got the right end of the stick ...BCP is the "Bulk Copy Program". This has the ability to EXPORT one, or several, table(s) to either some sort of delimted file, or a propretary format file. It can also do [the reverse] IMPORT. It has some special ability to do very large jobs in batches, thus ensuring that it doesn't 1) lock up the system for hours and 2) build a disk-swapingly-big Log file!But, being a system tool, its rather User Hostile.More details are avilable in BOL (that's "Books On Line" which hopefully you instaled along with the SQL utilities)Kristen |
|
|
|
|
|
|
|
|
Generating SQL scripts