Create Sql Table Based On Datatable C#

I've been searching for a long time with no useful answers. The problem I'm facing is to take some rows out of a SQL table and create a new SQL table to store them in a different d

Solution 1:

This worked for me in linqpad: ( after adding a nuget reference to "Microsoft.SQLServer.SMO"

copied and modified from answer at: Script table as CREATE TO by using vb.net

I had trouble trying to access Tables["[exd].[ABCINDICATORSET]"], couldn't figure out how to specify a table and domain properly, I was always getting null back.

// Define your database and table you want to script outstringdbName="Ivara77Install";

// set up the SMO server objects - I'm using "integrated security" here for simplicityServersrv=newServer();
srv.ConnectionContext.LoginSecure = true;
srv.ConnectionContext.ServerInstance = ".";

// get the database in questionDatabasedb=newDatabase();
db = srv.Databases[dbName];

StringBuildersb=newStringBuilder();

// define the scripting options - what options to include or notScriptingOptionsoptions=newScriptingOptions();
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;



// script out the table's creation Tabletbl= db.Tables.OfType<Table>().Single(t => t.Schema.ToLower() == "exd" && t.Name.ToLower() == "ABCINDICATORSET".ToLower() );

StringCollectioncoll= tbl.Script(options);

foreach (string str in coll)
{
    sb.Append(str);
    sb.Append(Environment.NewLine);
}

// you can get the string that makes up the CREATE script here// do with this CREATE script whatever you like!stringcreateScript= sb.ToString();

Some of the sql is slightly more verbose than what you get from sql server when you do Script Table As -> Create To -> New Query Editor Window

The changes to make it closer to what sql server generates were:

//options.Indexes = true;
options.IncludeHeaders = true;
options.NoCollation = true;

Solution 2:

Not sure if I've understood you, but if the schema of the table in the source and target databases is the same, can't you just use: SqlBulkCopy.WriteToServer.

You can query the source data and put it in a DataTable or stream it using an IDataReader.

Solution 3:

You can do this using SELECT INTO like this:

SELECT*INTO TableToCopyTo FROM TableToCopyFrom

This will copy all the rows of the TableToCopyFrom into newly created TableToCopyTo, but this will throw an exception in case if TableToCopyTo already exists. Also this will not create any keys and indexes for TableToCopyTo.

In case if your databases are on different SQL Servers this answer can be usefull for you https://stackoverflow.com/a/603518/6064728