Export Data From Table As SQL Inserts

Jul 21, 2005

Hi,

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?

Thanks

jr.

View 1 Replies


ADVERTISEMENT

SQL Server 2008 :: Add A Trigger That Inserts Original Data From 1 Table To Another

Apr 10, 2015

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.

View 9 Replies View Related

SQL Server 2014 :: Stored Procedure That Inserts And Updates A Table With Excel Data?

May 27, 2014

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?

View 6 Replies View Related

Export Table Containing BIT Data

Sep 2, 2004

I'm trying to export a table that contains a bit field into a text file to be used for a bulk insert into another database.

when i export the data from SQL enterprise manager, the bit field is exported as the text TRUE or FALSE ???!!!

this data then cant be bulk inserted as its not a BIT anymore.

how can bit fields be properly exported as 1 or 0 ?

View 1 Replies View Related

Table Data Export To .sql File.

Nov 16, 2006

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?

View 3 Replies View Related

Export Sql Data Table To Excel

Jun 6, 2008

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
            //
      }
 
    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
            "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";
 
        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);
            }
        }
    }
 
 
}

View 4 Replies View Related

Table Data Export Into An XML File

Sep 16, 2002

Hi,

I need to export data from some tables into an XML file format.

Any help please?

Thanks
John Jayaseelan

View 4 Replies View Related

Table Data Import/export

Oct 17, 2007

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.

Please advice.

View 1 Replies View Related

How To Export Data From DB Table To Excel Sheet

Dec 5, 2005

Hi .,
  Can any one guide me in exporting data from DB table to excel sheet .
 
Thanks,
vijay
 

View 3 Replies View Related

Export Data From Table To Text File

Apr 22, 2002

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?

View 1 Replies View Related

Export Data From Excel Into An Existing SQL Table

Dec 17, 2007

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$]')

Can anyone help me on this?

Thanks

View 9 Replies View Related

Export Data From SQL Database Table To Text File

Jun 22, 2001

Hi,

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.

View 4 Replies View Related

How Can I Export/import Data On Table To/from Text File

Dec 24, 1998

I needs export data on table to text file so I can process this data
with another database engine ie. Informix.

Can anybody help me to solve this problem ?

View 2 Replies View Related

How Do I Script INSERTS From An ASP Membership Table Onto Live Server Table

Aug 7, 2007

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?

View 3 Replies View Related

Export Data From Sql Server 2005 Table To Text File

Feb 27, 2008



Hii
I want to transfer data from table to a text file.I m trying to use bcp utility and xp_cmdshell.but the export is not successful.
My query is:

EXEC master..xp_cmdshell'bcp "Select * from test..emp" queryout "c:dept.txt" -c -T -x'

and its output is:

NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 16 Average : (187.50 rows per sec.)
NULL


but there is no row copied into c:dept.txt

where is the problem??

Thanx
-Supriya

View 23 Replies View Related

Export Table Data Into A Series Of Insert Statements (or Alternative)

Jan 29, 2008

Hi all,

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.

Thanks,
Yannis

View 10 Replies View Related

Is Having A Trigger That Inserts A Row In Table 'A', When A Row In Same Table Is Inserted By ADo.Net Code?

Oct 13, 2006

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

View 2 Replies View Related

Slow INSERTs On A Table

Apr 5, 2007

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.

View 9 Replies View Related

T-SQL With Inserts In Various Table In One Transaction

Mar 29, 2008

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).

Thanks,

View 13 Replies View Related

How Can I Export Foreing Key And Primary Key With SQL2005 Management Studio/Database/Tasks/Export Data Wizard.

Jan 4, 2008

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.

Best Regards,

Athena.

View 1 Replies View Related

How To Do Multiple Inserts For One Row Of Data?

Aug 21, 2006

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.

View 2 Replies View Related

Cursors - Looping Through A Table And Do Inserts From It

Dec 5, 2006

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.

View 12 Replies View Related

Selecting From Table With Lots Of Inserts

Mar 19, 2008

Hi,

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.

Any advice appreciated,

Thanks,
Sean

View 2 Replies View Related

DB Engine :: Concurrent Inserts Into Same Table

Jun 26, 2015

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?

View 6 Replies View Related

How To: Create A Table That Only Allows Inserts And Deletes.

Feb 26, 2008


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]

GO
SET ANSI_PADDING OFF

View 5 Replies View Related

SqlDataSource1.Insert() Inserts Data Twice

Aug 22, 2006

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>&nbsp;
<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>

<InsertParameters>
<asp:ControlParameter Name="Name" Type="String" ControlID="NameTextBox" />
<asp:ControlParameter Name="Number" Type="String" ControlID="NumberTextBox" />
<asp:ControlParameter Name="Salary" Type="decimal" ControlID="SalaryTextBox" />
</InsertParameters>

</asp:SqlDataSource>
 
</asp:Content>

View 1 Replies View Related

Textbox Inserts Data Into Database

Apr 13, 2008

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>

View 2 Replies View Related

Master And Child Table Inserts At A Time.

May 15, 2007

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.





Thank you.

I am currently using SQL Server
2005 and VS 2005.

View 1 Replies View Related

Creating Stored Procedure That Inserts Value In Table

Feb 13, 2008

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 ?

View 1 Replies View Related

Inserts And Updates To A Table That Contains A Unique Key Constraint

Nov 5, 2007

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.

View 4 Replies View Related

Data Getting Truncated As I Insert I.e. Instead Of Inserting Hello It Inserts 'H'

Jul 25, 2005

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,

updateDate, address, email, phone, zip, ethnicity)
 VALUES
(@lName  , @fName , @mName , @title  , @craft , @lastFour  , @SSN ,@dateOfHire  , @currentProj ,
@status,  @project_id , @UpdateBy, @UpdateDate , @address , @email , @phone , @zip , @ethnicity )
SELECT @newid = SCOPE_IDENTITY()
insert into feedBack
(lID, feedBack, userID, project_id)
values
(@newid ,+'
+@myfeedBack + ',
+@userID  ,
@project_id )
SET NOCOUNT ON
END
GO


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'

I appreciate any help or hints
thanks to all

View 1 Replies View Related

Any Way To Ignore Data On Inserts That Violate A Rule?

Apr 18, 2012

I am currently importing tick data for a stock. Let's say my table structure is like this:

CREATE TABLE tick
(
tickId bigint identity(1,1) primary key
, tickTime datetime
, price money
)

If the stream of data I get resembles:

'4/17/12 2:00:00.000', 10.00
'4/17/12 2:00:02.000', 10.02
'4/17/12 2:00:01.000', 10.01
'4/17/12 2:00:03.000', 10.03

I want my table to look like this:

1, '4/17/12 2:00:00.000', 10.00
2, '4/17/12 2:00:02.000', 10.02
3, '4/17/12 2:00:03.000', 10.03

Essentially ignoring the out of place '4/17/12 2:00:01.000' record. What is the least expensive way to accomplish this?

View 6 Replies View Related

Bulk Inserts To Data Warehouse - Best Practices?

Jul 20, 2005

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

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved