0

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 database in order to perform fast calculation on them later. The original table has more than a million rows and I'm taking out no more than 2,000 of them. The table has 277 columns and could be changing periodically so I need to generate new table copying the schema at run-time.

I'm now using MVC 5 and Sql Server 2008.

I tried taking out the data with linq but it couldn't push the data back to SQL Server.

I also tried taking out the schema with Select * from Table Where 1 = 0, which gave me an empty datatable, but it still exists to create new table based on the schema.

I'm now trying to create the needed SQL based on datatype. It's a tiresome task and I have a strong feeling someone might have already done that.

Is there any out-of-the-box package or methods for this not-so-frequent task? Some of the questions I've searched for appeared about 9 years ago and I hope there's something for this at present.

The most ideal approach would be to use linq to query for the results and push the IQueryable back into database to create a new table.

Please help.

Wang Tianjian
  • 67
  • 9
  • 1
    This looks promising: https://stackoverflow.com/questions/17759404/script-table-as-create-to-by-using-vb-net – Derek Jul 12 '17 at 14:27
  • Why not simply "SELECT * INTO dbo.AnotherTable FROM dbo.table WHERE 1 = 0'? This creates a second table with the very same schema (without constraints, indexes, statistics and so on) but without any data. Then a second query would easily populate with the exact data needed. – Alejandro Jul 12 '17 at 17:32
  • And of course, LINQ or any other ORM are terrible tools for this. Keep this operation server-only and with pure SQL and it becomes trivial, as long as it's just copying data already on the server. – Alejandro Jul 12 '17 at 17:35

3 Answers3

0

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

Samvel Petrosov
  • 7,580
  • 2
  • 22
  • 46
0

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.

Joe
  • 122,218
  • 32
  • 205
  • 338
  • Isn't SqlBulkCopy expecting the table to be already there? I'm trying to create a new table first so maybe use it later. But I'm now stuck at first step. – Wang Tianjian Jul 12 '17 at 15:18
  • It wasn't clear to me from your question ("I couldn't push the data back to SQL Server") that your problem was copying the schema. There are several ways to do it, for example use SMO as suggested in another answer. – Joe Jul 12 '17 at 18:51
0

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 out
string dbName = "Ivara77Install";

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

// get the database in question
Database db = new Database();
db = srv.Databases[dbName];

StringBuilder sb = new StringBuilder();

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



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

StringCollection coll = 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!
string createScript = 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;
Derek
  • 7,615
  • 5
  • 33
  • 58
  • This looks good but too bad the boss do not plan to change anything on the database. – Wang Tianjian Jul 12 '17 at 15:36
  • You said "and create a new SQL table to store them in a different database". This code will give you the SQL to do that. – Derek Jul 12 '17 at 15:45
  • I mean I'm supposed to make the code work without adding additional references/ changing code structure. (For some business reasons.) And I was not able to find 'Microsoft.SQLServer.Connection' or 'Microsoft.SQLServer.SMO' in References. So too bad. – Wang Tianjian Jul 12 '17 at 16:00
  • I think it would be a very bad idea to try to write your own instead of using a nuget package delivered by Microsoft. https://www.nuget.org/packages/Microsoft.SQLServer.SMO/ – Derek Jul 12 '17 at 17:41