How do I create a dataset with only table definitions

Question

Post reply

How do I create a dataset with only table definitions

david myers-292056

SSC-Addicted

Points: 413
More actions
February 9, 2006 at 5:59 am

#170889

Hi
How do I create an empty Dataset containing only the table definitions from my sql server database.
I want to insert new records into the Dataset tables and then update the database.
Im working with vb.net
Code example appriciated.
Thanks for any help

david

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

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

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

If you want to quickly make an empty table the same 'shape'' as an existing table you can do

SELECT TOP 0 tblMyTable.*

INTO tblMyNewTable

FROM tblMyTable

This will give you the right shape but will not cop contraints/defaults/indexes.

You could also script out the object in EM

david myers-292056 SSC-Addicted Points: 413 More actions · Answer 2

Thanks for the reply

I guess that's the same as

Select * from tblMyTable Where 1 = 2;

that I found on my wanderings after posting here.

You say

"This will give you the right shape but will not cop contraints/defaults/indexes."

Does this mean I have to add the Primary key to the DataTable?

or do I wait until I update the database.

allen davidson SSCertifiable Points: 7042 More actions · Answer 3

Yes - you will need to define anything other than the actual columns and their data type.

If you have any constraints etc they will not be copied. It does copy Identity columns , NULL / NOT NULL and Collationn.

I'm don't think it copies DEFAULTS.

david myers-292056 SSC-Addicted Points: 413 More actions · Answer 4

Is this the best way to prepare a dataTable for an Insert operation, or are there alternative methods.?

In my case what I'm doing is reading thru an ascii text file, extracting data and creating about 5000 new records for input to a table.

The other question is do I need a seperate dataAdapter for each table or for each SQL operation or can I combine several selects from different table into 1 dataAdapter, if so what is the syntax. ?

Bill Scott SSCrazy Points: 2338 More actions · Answer 5

Sounds like you want to script the database and then create an empty one? If so, you can use Tools/Generate SQL Script... from Enterprise manager. Lots of options on what you want to include, tables, constraints, procs, types, users... Then just create a new database and run the script.