I have data in an old database I would like to capture for my new
system but I dont have the original insert scripts. Is there a
tool (in SQL Server 2000 or thirdparty) that will help me export the
data as SQL inserts?
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABC]( [id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABCD]( [id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
Rookie qustion here quys, Using SQL Sever 2005 Express and Visual Web Devoloper Express. I'm trying to get data from my local SQL sever database to my shared hosting database. SImple enough but... 1.) GoDaddy does not allow remote connections to the DB server 2.) With GoDaddy you can't just upload an .MDF file to your remote directory and script a connection string. You have to reference the DB server. Can't upload DB file to DB server. As a work around, I've seen several .sql files that have all the T-SQL that will create a database and tables then insert the data. I've google'd extensively and searched several forums with no luck as to how to export my local data to file. I could then copy and paste into an online utility GoDaddys has to insert the data. Is there a relatively easy way to export table data to a .sql file??? When I try using SQL Server Management Studio Express and "Scipt Table As> Insert To> File", It just gives me something like the following... INSERT INTO [pubs].[dbo].[authors] ([au_id] ,[au_lname] ,[au_fname] ,[phone] ,[address] ,[city] ,[state] ,[zip] ,[contract]) VALUES (<au_id, id,> ,<au_lname, varchar(40),> ,<au_fname, varchar(20),> ,<phone, char(12),> ,<address, varchar(40),> ,<city, varchar(20),> ,<state, char(2),> ,<zip, char(5),> ,<contract, bit,>) Plenty of data in this table, no data in script ??? Can SQL Server Management Studio Express do what I wanting to do? If not, any suggestions? Know of a better hosing company?
hi , Can any body help me export data from sql database to excel? I am using datagrid method but it is not transferring complete table's data at a time through LAN .i.e. it works on local host ,but not working if I accessing this project from other computer.
public class gridviewexport { public gridviewexport() { // // TODO: Add constructor logic here // }
using (StringWriter sw = new StringWriter()) { using (HtmlTextWriter htw = new HtmlTextWriter(sw)) { // Create a table to contain the grid Table table = new Table();
// include the gridline settings table.GridLines = gv.GridLines;
// add the header row to the table if (gv.HeaderRow != null) { gridviewexport.PrepareControlForExport(gv.HeaderRow); table.Rows.Add(gv.HeaderRow); }
// add each of the data rows to the table foreach (GridViewRow row in gv.Rows) { gridviewexport.PrepareControlForExport(row); table.Rows.Add(row); }
// add the footer row to the table if (gv.FooterRow != null) { gridviewexport.PrepareControlForExport(gv.FooterRow); table.Rows.Add(gv.FooterRow); }
// render the table into the htmlwriter table.RenderControl(htw);
// render the htmlwriter into the response HttpContext.Current.Response.Write(sw.ToString()); HttpContext.Current.Response.End();
} } }
/// <summary> /// Replace any of the contained controls with literals /// </summary> /// <param name="control"></param> private static void PrepareControlForExport(Control control) { for (int i = 0; i < control.Controls.Count; i++) { Control current = control.Controls[i]; if (current is LinkButton) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text)); } else if (current is ImageButton) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText)); } else if (current is HyperLink) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text)); } else if (current is DropDownList) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text)); } else if (current is CheckBox) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False")); }
if (current.HasControls()) { gridviewexport.PrepareControlForExport(current); } } }
How to refresh table data on sqlserver without dts or backup/restore whole database. In sqlserver, is there a way to import table(s) data (like import export in oracle) in a file and export to another unconnected server/db.
I need to export data from a table to a text file, where the data in the table is deleted after written to the file. It is simple using DTS, but I want to do the export in "chunks" of data, committing the delete say after every 1000 rows.
My thought was a stored procedure would be easy enough to do this (done these in Oracle many times), but I don't know the quickest way to export a row of data from a stored procedure to a text file. Isn't using a command-line shell too slow? What are my options?
Hi everyone, I am new with SQL and I tried to use the code below to export data from Excel into an existing SQL table, but I keep on receiving the following message.
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
To export data from Excel to existing SQL Server table,
Insert into dbo.Base_Intraday Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Shortcuts6 - Bolsa de Valores1 - Bolsa de Valores - Bovespa;HDR=YES', 'SELECT * FROM [Link$]')
I'm trying to export data from one of the table in my SQL 7.0 database into text file. Can someone tell me how can i do this using SQL Query instead of using BCP (command line) ?? Thank you in advance.
Hi,I would have used the aspnet membership tool to auto-create all the ASP.NET membership tables. However, the hosting company don't allow remote connections which meant I had to create the tables by hand, scripting the tables using script to CREATE using management studio.However, I noticed one of the tables has data without any users: aspnet_SchemaVersions, which causes an error when trying to log onto my site.The fix is to make sure the table has the 4-5 rows of data in it (which is missing off the live server). Its just a few rows of data, but I want to script the inserts for each row so I don't have to type them in using myLittleAdmin (the host's web version of management studio). Can anyone point me in the right direction?
I have a table which I would like to export to a series of insert statements (in a file maybe). Is this possible somehow?
Alternatively, I could use an existing xml document which contains table metadata (table name, column names,types etc) to transfer data from these particular columns to another database replica.
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
Hello all. I've got a problem with really slow INSERTs on one (and only one) of the tables in a database. For example, using SQL Management Studio, it takes 4 minutes and 48 seconds to insert 25 rows. There are only about 8 columns in the table and only about 1500 records. All the other tables in the database are very fast for inserts.
Another odd thing uniquely associated with INSERTs on this table: prior to inserting the 25 new rows of data, SQL Management Studio tells me that it inserted 463 rows of data which I know did not happen. Here's the INSERT statement:
INSERT INTO FieldOps(StudySiteID , QA_StructureID , Notes , PersonID) SELECT DISTINCT StudySiteKey , QA_StructureKey , SampleComments1 , '25' FROM ScriptOutput_Nitrate WHERE (ScriptOutput_Nitrate.StudySiteKey IS NOT NULL)
and SQL Management Studio (eventually) says: (463 row(s) affected) (463 row(s) affected)
(25 row(s) affected)
The table has an index on the primary key (INT data type with auto increment). I tried running the following code to fix things but it made no difference:
USE [master] GO ALTER DATABASE [FieldData] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
use FieldData GO DBCC CHECKTABLE ('FieldOps', REPAIR_REBUILD) With ALL_ERRORMSGS GO
USE [master] GO ALTER DATABASE [FieldData] SET MULTI_USER WITH ROLLBACK IMMEDIATE GO
I'm guessing that the problem might be related to the index (??). I don't know... Does anyone here have a suggestion as to what I should do to fix this problem.
Dear All, I developed an application years ago where I have to insert records in multiple table from asp.net page during registration process with one button click. I have seperate Insert statements and then i have to select Identity column value and insert into another table as a foriegn key and so on. I want to run all these SQL insert in one statement or Stored procedure. becuase currently, if one of insert statemetn fails, I have no way to roll back previous inserts and start over again.
Any advice or sample T-SQL Where I can insert in Table A, and then read the identity from Table A and insert in Table B, in one single Transaction, so if it fails at end of Transaction, i want to roll back the all the inserts and updates in table(s).
How can I Export Database with foreing Key and primary key.
Operation is that SQL2005 Management Studio/Database/Tasks/Export Data
Before Version is SQL2000 we can Selected Copy Object and data between server and then Use Default Options click checked and Select Copy Index, Copy Foreing Primary key vs vs
But this options is not found in the SQL2005 Management Studio/Database/Tasks/Export Data wizard or I can't found it.
How can I export foreing Key and primary key with SQL2005 Management Studio/Database/Tasks/Export Data wizard.
I need to insert multiple rows for input rows that meet certain conditions.
My input data is as follows.
ZIPCode, Plus 4, Range, City, State
What I need to happen is that if there is a value in the range column that is > 0 then I need to insert a row for every range item.
For instance say I have the following data.
54952, 1001, 10, Menasha, WI
What I need imported is :
54952, 1001, Menasha, WI 54952, 1002, Menasha, WI 54952, 1003, Menasha, WI 54952, 1004, Menasha, WI 54952, 1005, Menasha, WI 54952, 1006, Menasha, WI 54952, 1007, Menasha, WI 54952, 1008, Mensaha, WI 54952, 1009, Mensaha, WI 54952, 1010, Mensaha, WI 54952, 1011, Mensaha, WI
Any help in pointing me in the right direction would be great. Thanks for your help in advance.
I've been looking online and cannot find any help / resources with this so I brought it here :D I'm looking for help in creating a Cursor (this will be inside a SP) that will loop through the records of a "Table" (Temporary or Retrieved) and for each row that is looped through I can use it's values to do inserts against a few other tables. Any resources / help would be great! I work best by example.
I am working on an application to analyse down time on a production line system. The system has about 40 rows inserted per minute. The inserts are coming from about 10 different stations.
I need to a analyse the downtime between each insert from each station. The plan is to copy the data to another database on a different server so as not to affect the live system that is being updated by the production line.
However the initial requirement was to do this at night while the production line was down but now they want the data to be updated every 3 hours which means performing this huge query while the production line is bombarding the DB with inserts.
I am wondering what is the best way of doing this. Is there any way I can limit the abount of processor this proceedure will take.
I have a table into which the inserts will be done by multiple users at the same time;
The table has a primary key, non clustered, on unique id; each insert will have unique id, which is a seqence generated number, so they are always different for different transactions.
Is this possible to set the settings of sql server in such a way, that these inserts are done at the same time i.e. during insert by one user that table is not locked so that other inserts can take place at the same time as well?
I need to create a table that only allows records to be inserted or deleted. Once the record has been created it can only be deleted. Is there anyway to configure a table in this manner?
Table Definition
USE [DB_AUTOMATED_PACKAGING_SYSTEM] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TBL_PCL_LENS_DATA]( [SerialNumber] [varchar](50) NOT NULL, [ProcessedDate] [datetime] NOT NULL, [Filename] [varchar](50) NOT NULL, [CartonLabelImage] [image] NOT NULL, [ExpirationDateLabelImage] [image] NOT NULL, [LabelSetLabelImage] [image] NOT NULL, [ReplyCardLabelImage] [image] NOT NULL, [TextFile] [ntext] NOT NULL, CONSTRAINT [PK_TBL_PCL_LENS_DATA] PRIMARY KEY CLUSTERED ( [SerialNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Hi there, I'm having a problem where the data taken from the textboxes is being inserted into a table twice. I looked online and someone on another forum was experiencing the same problem. His solution was making the "submitdsabledcontrols" attribute of the form false. That hasn't fixed the issue for me. Below is my code. I'm still learning all this so this is just a test page, hence the wacky naming conventions. When the button click calls Button1_Click, all it runs is "SqlDataSource1.Insert()" Any ideas? <%@ Page EnableEventValidation="false" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Default3.aspx.vb" Inherits="Default3" title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="mainContent" Runat="Server"> <form id="form1" submitdisabledcontrols=false> <br /> <asp:Label ID="Label1" runat="server">Name</asp:Label> <asp:TextBox ID="NameTextBox" runat="server"></asp:TextBox> <br /> <br /> <asp:Label ID="Label2" runat="server" Text="Label">Number</asp:Label> <asp:TextBox ID="NumberTextBox" runat="server"></asp:TextBox> <br /> <br /> <asp:Label ID="Label3" runat="server" Text="Label">Salary</asp:Label> <asp:TextBox ID="SalaryTextBox" runat="server"></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_click" /> </form> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pubsConnectionString1 %>" DeleteCommand="DELETE FROM [Table1] WHERE [TableID] = @original_TableID" InsertCommand="INSERT INTO [Table1] ([Name], [Number], [Salary]) VALUES (@Name, @Number, @Salary)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Table1]" UpdateCommand="UPDATE [Table1] SET [Name] = @Name, [Number] = @Number, [Salary] = @Salary WHERE [TableID] = @original_TableID"> <DeleteParameters> <asp:Parameter Name="original_TableID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Number" Type="String" /> <asp:Parameter Name="Salary" Type="Decimal" /> <asp:Parameter Name="original_TableID" Type="Int32" /> </UpdateParameters>
I have a VERY simple program, it contains a datagrid, textbox and button I want it so when I insert something into the textbox and press the button it puts it into the database that is connected to the datagrid and displays it I have the following code and I cannot get it to work: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server"> Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) SqlDataSource1.InsertParameters.Add("@Name", TextBox1.Text) End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head> <body><form id="Form1" action="Default.aspx" runat="server"> <table> <tr> <td> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" /> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString1 %>"DeleteCommand="DELETE FROM [Table] WHERE [ID] = @ID" InsertCommand="INSERT INTO [Table] ([Name]) VALUES (@Name)" ProviderName="<%$ ConnectionStrings:DatabaseConnectionString1.ProviderName %>"SelectCommand="SELECT [ID], [Name] FROM [Table]" UpdateCommand="UPDATE [Table] SET [Name] = @Name WHERE [ID] = @ID"> <InsertParameters> <asp:Parameter Name="Name" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> </td> </tr> <tr> <td align="center"> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> </td> <td> <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click1" /> </td></tr> </table></form> </body> </html>
I have a situation here. Please advice me on this.
I have a master table and a child table. They have a PK and FK relationship. The master table has an identity column with auto increment set to true. This map as a FK in the child table. My questions are:
Can I have a single form to insert a new record into master and child table at the same time?This has to be accomplished without stored procedures. Can it be done?Is it possible to do this with a single insert query? If yes, can it be done with sql data source or dataset or tableadapters’?Please point me towards appropriate link for doing so.
I am using vwde2008 and created db and table. i want to create a stored procedure that will insert values. I am bit lost on how write this. this is my table info table = BusinessInfo Columns = BusinessID, BusinessName, BusinessAddress how can i create a stored procedure ?
I am looking for pros and cons for the following scenarios:
When a table contains a unique key constraint is it viable to always do an insert and immediately check the @@ERROR value and if @@ERROR states a duplicate key exception then perform an update statement?
Another possible solution would be to always check if the key exists and then do the insert / update based upon that result. This method will always require two steps.
m inserting some data in big-sized field and small-sized fields, data of varchar type, int's, dateTime, and others... i am using something called a stored procedure to add data into a table.. now when i execute the stored procedure my data gets truncated (although i made the sizes for my fields ridiculously big like 1000 just in case) for example if i want to enter Jasmine it only inserts 'J' in the field
I am sure there's something wrong in the stored procedure, and I am guessing it's a problem of using single vs. double quotes and stuff like that within my insert statement... the following is my stored procedure:
CREATE procedure addLabor @lName varchar, @fName varchar, @mName varchar, @title varchar, @craft varchar, @lastFour varchar, @SSN varchar, @dateOfHire varchar, @currentProj varchar, @status tinyInt, @project_id int, @updateBy varchar, @updateDate varchar, @address varchar, @email varchar, @phone varchar, @zip varchar, @myfeedBack varchar, @ethnicity varchar, @userID int as BEGIN SET NOCOUNT OFF DECLARE @newid INT insert into laborPersonal ( lName, fName, mName, title, craft, lastFour, SSN, dateOfHire, currentProj, status, project_id, updateBy,
When i use 2 single quotes it insert the following string: "@lName" not the actual value of the variable @lName my exec statement is this: EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman', '1234', 'keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==', '6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/2005 2:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345', '123-12-1234', 'African American', '3'
Hello all,I just started a new job this week and they complain about the length oftime it takes to load data into their data warehouse,which they do once a month.From what I can gather, they rebuild the indexes before the insert with an80% Fillfactor, then insert the data (with theindexes enabled), then rebuild the indexes with a 100% Fillfactor.Most of my RDBMS experience is with a different product. We would havedisabled the indexes and Foreign Keys, loaded the data, thenre-enabled them, moving any records that violated the constraints into anappropriate audit table to be checked after.Can someone share with me what the accepted "best practices" are for loadingdata efficiently into a data warehouse?Any thoughts would be deeply appreciated.Steve