.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Copy table including triggers and forein keys

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :Sql Server

Hi All,


Need help.


I've deleted some of the products from the product table. from 32Million to 22 million products. and in that 22 million, I've added 1.2 Million products. Now, I want that 32 million table bacik.


I've that table on another server. and i copied that table to the server I want. but i can copy only data. i can't copy with all indexes, statistics, constraints etc.


Now, I need to get 32 million records into product table.


Any suggestions.. never done it before.and spent enough time. but getting screwed every time. Thanks in advance.

View Complete Post

More Related Resource Links

daily complete cube rebuild four dimensions and fact table including remapping of all surrogate keys

Hi SSIS Engineers: Forgive me if this is a multi-forum question. Our primary activity in the next week is to automate the processing in SSIS, where I led the team to create complete processing flows for Full and Add in the order of Dimension, Measure Group, Partition, Cube, Database. These work. The problem occurs in a complete refresh of the ERP database that caused me manual effort inside SSAS, which I plan to find a way to automate in SSIS. I performed a complete refresh of our cube from the ERP source from a time perspective. We are automating this process in SSIS. In SSAS, I had to manually delete the four dimensions from the UDM view via the Solution Explorer. Since the complete refresh increased the surrogate keys in the dimensions and since the names were the same, I couldn't just drop the partition and reprocess the dimensions, since, in effect, new fact rows would have to be mapped to the new keys. SSAS held on to the old keys even with Full Processing of the Dimensions first, then the Cube. Until I dropped--deleted-- the dimensional tables from the Solution Explorer and the UDM then later readded the dimensions with the new surrogate keys (both add, update and delete dimensional attribute changes in full refresh) via the Add Dimension wizard, the cube kept the old surrogate keys and failed in measure group, fact, database and partition processing.

Bulk Copy From Datatable To SQL Server Table


 I have read the data of a excle file and captured the data into the dataset to a datatable, now that data is to be inserted into a SQL Server table using bulk copy option. I am using -

 public bool BulkEnterData(DataTable dt, string tblName)
        SqlBulkCopy bulk = new SqlBulkCopy(con);
        bulk.DestinationTableName = tblName;

        return true;

ERROR Getting-
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.) 

How to resolve the above problem or any other solution is available.


I have 3 triggers defined on an sqlserver2008 table and I can't find them with c#

I've got the code below and when I run it, it always says there are 0 triggers in table.Triggers.  I added some debugging code checkin the table.HasDeleteTrigger, etc. and from that, I get the exception: "To accomplish this action, set property HasDeleteTrigger." when I try to set it to bool hasDeleteTrigger. I can get stored procedures find, just not triggers. Below is my problem code: foreach (Database database in databaseCollection) { if (database.Name.Equals(catalogName)) { // first we need to delete triggers associated with all tables that begin // with our prefix. we can skip our the framework tables because those // don't have triggers on them // grab all tables, filter for non prefixed ones in foreach loop so we can delete triggers from non prefixed ones results.AddRange(from Table table in database.Tables select table.Name); foreach (var tableName in results) { var table = new Table(database, tableName, CrSyncDbSchemaName); table.Refresh(); bool hasDeleteTrigger = table.HasDeleteTrigger; bool hasAfterTrigger = table.HasAfterTrigger; bool hasInsertTrigger = table.HasInsertTrigger;

How to copy table data from test to production database

I'm using SQL Server 2008 R2 and I'd like to copy all data rows from one table of a test database to the identical table in a production database. The primary key of the table is referred to by other tables so a DELETE followed by a number of INSERTs won't do it because of referential integrity issues. What is the best method to replace the data rows in the destination database with the values from the source database? Thanks, Guido

InfoPath repeating table Copy values from above row without Code

Hi, I have situation, where I want to copy the values from the above row to the newly created row in a Repeating table. Ex. I have a repeating table with 3 columns ID-Name-FileName. When I enter a data like 1-Customer1-File1.zip and hit "Insert new row", the new row should automatically populate the values of the above row. How can I do it without .Net code. Regards,

copy the image from table to disk drive

i have 2000  image he saved in the table "Employee" and i want to copy this pictures from table to diskdrive and save file name by the ID

SSRS2005: Is it safe to manually remove instances from the Keys table in the report server database?

I have a problem with redundant reporting services instances still hanging around in the initialization pane of the RS configuration wizard and I can't get rid of them. The problem arose when we had new webfarms and before I had removed all references to the old ones from the initialization screen, the old webfarms had been switched off, removed from the network and dismantled. Therefore when I try to 'de-initialize' them, I get an error stating that reporting services cannot connect to them and when I run reports, a fair few are trying to run on the old webfarms which no longer exist and are disappearing into the void. There's a table in the report server database called Keys whch contains details of these intances and i'm wondering if its safe to remove these via SQL commands without breaking reporting services on other machines.   As a side note: All this stems from the fact that when you uninstall Reporting Services, not all of the files / references are removed. So as a general word of warning, if you do an uninstall of RS2005, you'll need to make sure everything's gone before re-installing, throwing away old machines etc. These include folders in IIS and these old instances as mentioned above.  

How 2 join Multiple Keys based table???

I have a table INC with 2 Columns/Fields, i.e. YR and CL set as primary keys by selecting both the columns and selecting primary key symbol with right click. How to set up a FK with the other table INC_DTL's CL which I seek to be restricted to a combination of the INC's 2 fields? Thanx in advance.

Copy table (with content and links) from one page to another

Hi, I'm new to sharepoint and learning this software on my own.  I'm running WinXP Professional.  I've created a table on one page in a subfolder/directory (10Kgold) and would like to use the same table on a page in a different subfolder (24Kgold).  Both subfolders are in the same root folder.  Here are the steps I took: On 10kgold/bracelets.aspx page: right-click on element to copy --> select Copy On 24kgold/bracelets.aspx page: right-click --> Paste On the 24kgold/bracelets page, the "Paste" option was disabled, so then I knew I wouldn't be able to copy/paste the table I copied from 10K gold/bracelets page. I don't want to recreate this table again if I don't have to. Also, I've tried to copy and paste links within the same page, but that doesn't work either. Here are the steps: Select link (yellow-gold) on 10kgold/bracelets page: right-click --> Copy Scroll to where I want to paste the link Right-click (yellow-gold) on 24kgold/bracelets page: --> Paste Is there a way to copy and paste links and tables, etc. from one page to another, with the page/s being in either the same subfolder and/or a different subfolder as long as they are within the same root folder? Thanks in advance for your help.    

Copy Data From one table to another

Hi All, I have a XYZ Database (50 Tables init and lots of data ) on 01 Server and I need to create a XYZ_MOCK Database on same Server 01 with 20 tables of XYZ Similarly with the exact data of XYZ Database.... SO I DID Create XYZ_MOCK database AND 20 TABLES Now there are 20 tables with lots of lots of Records in XYZ ...... All i want is now I need to move all the records from XYZ DB to XYZ_MOCK. DB (20 Selected Tables).. for ex: From Server01-XYZ Database -table abc (20columns and 10,000rows) TO Server01 - XYZ_MOCK Database table abc(20 columns and 10,000rows).... How can i Insert all records for 20 tables so that my Selected tables in Xyz and XYZ_MOCK lokks same Thanks

How to copy sqlServer table into excel sheet with sheet name as sheet1?

hello all,I want to copy each table(from selserver database) in separate sheet of Excelwith Sheet name different from table name..........So the issue is that when I open Excel Sheet it show sheetname same as filename I just want to name it as Sheet1,Sheet2 and so onI can copy table from SqlServer(MS Sql Server 2005) into Excel Sheet as follows:public static void ExportDataTableToExcel(DataTable dt, string filename)         {             System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;             // first let's clean up the response.object             response.Clear();             response.Charset = "";             // set the response mime type for excel             response.ContentType = "application/vnd.ms-excel";             response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");   &nb

How to copy a table record and all related table records linked via relationships...

Hi!I'm using SQL Server 2005 in a project, and i need to duplicate a record from a table and all it's relationships. Can this be done? If so, can anyone tel me how please.

Joins on non-indexed fields and table primary keys.


I have two tables (a,b) each with a primary key (which are clustered indexes). When I issue the t-sql statement

delete XX from t1 XX inner join t2 on (xx.A2 = t2

copy from table to other exist table



I tried to insert new values (new rows into table) to Table A according to specific Select query on table A

Table B holds many datarows. I just want to add more from Table A to it.

Both tables are with the same columns (and properties).


I tried this :

FROM     A
WHERE   (YEAR(t_date) = 2009)

 I got an error:

An explicit value for the identity column in table 'Tnuot_Archive' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Copy row within same table -Revisited



In a Stored Procedure, I need to copy a row in a table, change some fields and then INSERT the row into the table as a new row. Something like:

Select @Field1=Field1, @Field1 = Filed2, ... @FieldN = FiledN FROM MyDB


Values (MyNew_PK_index, @Field1, @Field2, ... @FieldN )


My problem is that I have 60 something columns in each row and I would like to avoid using that many variables if it's possible.

Can I read the row into an array and then manipulate the members that need to be changed (like PK for example) and then write the array to the table in some simple way? Any other or better way?



Questions on partitioned table keys in SQL 2008


I've been experimenting with the partitioned table feature in SQL 2008 (evaluation version).  Nice work, I can see how the feature would be really useful.  I am investigating it for an application that involves a scrolling window based on a datetime column, so that the table holds one year's worth of data. The datetime is used as an event timestamp, recording it down to the millisecond.  New data is loaded every hour. I would like to keep the impact of the new data load minimal, so I want to take advantage of partition switching when a new hour's worth of data is ready.  Trimming old data would only happen once a day.  My questions/assertions based on experimenting a little with this and reading what I could find on the subject:

1) Both the partition function and the left-most column of the clustered index should be based on the datetime column value. This takes maximum advantage of the datetime range terms that are in every query generated by the UI  (show me events of this type in the last week) and also allows partition switching for adding new records and trimming old ones.

2)  It doesn't make sense to keep an identity column on the partitioned table, despite their convenience as a surrogate key for many editing tools.  You would have to add the datetime column to an identity column in a primary key or unique c

Copy from one table to another table???



   i have 2 table   tab1 and tab2.

tab1 has id,fname1.   here id is primary key.

tab2 has id,fname2.    here id is primary key.

i run this 

insert into tab1(id,fname)select id,fname2 from tab2

and i am able to copy from tab2 to tab1.

now i have some changes in tab2 and few more rows are added in tab2.

So i have to update tab1 table with new rows and update the old ones.

How to write query for this?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend