Inserting Records From Result Set Into A Table
Nov 7, 2014
I am comparing names of people from a table without id field to a table that has those people along with their ids.
I have a select statements using different field combinations that fetches ids of these people and presents the result set like:
table1id,table1firstname,table2firstname,table1lastname,table2lastname
E.g.:
1 john john smith smith
1 john j smith smith
1 john jon smith smit
I want to be able to insert all the records from the result set into another table, like in the eg, but only excluding those ones that match all the above fields. How would I do this...
View 1 Replies
ADVERTISEMENT
Sep 13, 2006
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies
View Related
May 21, 2008
Hey guys I'm trying figure out if there is a way to insert a result from a stored procedure into a table without going through ASP.NET. This is just an experiment and for learning purposes since i did manage to work it in .NET.
For example.
CREATE PROCEDURE dbo.inserttoLog
@Modem_ID VARCHAR(7)
As
Select dbo.PRODUCT.MODEM_ID + ' ' + dbo.PRODUCT.COLOUR + dbo.PRODUCT.SPEED AS Part#
FROM PRODUCT
WHERE ([Part#] LIKE '%' + @Modem_ID + '%')
I want to take the concatenated result of PART# and insert it into a log table. Is there a way of doing that?
View 12 Replies
View Related
Nov 2, 2015
INSERT
INTO [Table2Distinct]
([CLAIM_NUMBER]
,[ACCIDENT_DATE]
[code]....
I used the above query and it still inserts all the duplicate records. What is wrong with my statement?
View 5 Replies
View Related
Apr 1, 2004
Hi..
I really need a help.
Is there any way to insert multiple records into a table in one go?
I have a table named Fruit.
It contains FruitId,OwnerId,Colour
The list of colour is got from another table name FruitColour.
FruitColour Consists of 2 column, FruitName and Colour.
Is it possible to insert multiple records into Fruit table with one query if only the colour is changed.
Sample case
I have an Apple.
Fruit id=2
OwnerId=2
Colour -- > Select Colour From FruitColour where FruitName='Apple' (Will return multiple records)
I tried to insert using this query:
Insert into Fruit(FruitId,OwnerId,Colour) Values (2,2,Select Colour from FruitColour where FruitName='Apple').
Gives me this error
Subqueries are not allowed in this context. Only scalar expressions are allowed.
I need to do this because actually I am inserting multiple fruit at one time, and each have multiple colour. If I need to insert the colour one by one for each fruit it will takes a very long time.
Any suggestion are welcomed.
Thank you in advanced.
View 3 Replies
View Related
Mar 19, 2008
Folks:
I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why?
SET NOCOUNT ON
DECLARE @ImportId INT
SET @ImportId = 5151
DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
===================================================================================
Same Query when trying to insert the records in a temp table it takes hours.
===================================================================================
SET NOCOUNT ON
DECLARE @ImportId INT
SET @ImportId = 5151
DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)
create table #TResults
([ID] [INT] IDENTITY(1,1) NOT NULL,
DealName VARCHAR(200),
CUSIP VARCHAR(100),
Vintage INT,
PoolType VARCHAR(100),
PaidOff BIT)
INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
SELECT * FROM #TResults
============================================
Thanks !
View 7 Replies
View Related
Feb 16, 2006
Godwin writes "Hello,
Heres my question..
I have 2 tables.2 paticular columns exist in both the tables.
I want to be able to select those 2 columns on the 1st table and insert them on to the same 2 columns on the 2nd table.
Now,this 2nd table has another 3 columns that exist in another table.I would like to take those 3 column values from that 3rd table and insert it into the 2nd table by modifying those existing records in the 2nd table.In the 3rd table,there will be around 5 records...I want to copy the existing records 5 times in the 2nd table and insert the 3rd tables rows inside the 2nd table in that respective column for 5 rows.
I hope you understand what I mean...Im sorry for really confusing..
Please help me
Thanks
Godwin"
View 1 Replies
View Related
Jun 12, 2014
I want to update records in 1 table with the result of a select statement.
The table is called 'MPR_Portfolio_Transactions' and contains the following fields:
[PTR_SEQUENCE]
,[PTR_DATE]
,[PTR_SYMBOL]
,[PTR_QUANTITY]
,[PTR_ACUM]
And the select statement is like this:
SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE
This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'
the key is PTR_SEQUENCE
View 3 Replies
View Related
Mar 6, 2008
Hi..
I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc..
IF Exists(
SELECT
*
FROM
PlanEligibility
WHERE
PlanId = @PlanId
) BEGIN
UPDATE
PlanEligibility
SET
LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,
EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End,
EntryDates = @EntryDates,
EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM,
LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END,
OtherEmployeeExclusions = @OtherEmployeeExclusions
WHERE
PlanId = @PlanId
END
ELSE BEGIN
INSERT INTO PlanEligibility
(
PlanId,
LengthOfService,
EligibilityAge,
EntryDates,
EligiDifferentRequirementsMatch,
LengthOfServiceMatch,
EligibilityAgeMatch,
OtherEmployeeExclusions
)
VALUES
(
@PlanId,
Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,--@rsLengthOfService,
CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge,
@EntryDates,
Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here
CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch,
@OtherEmployeeExclusions
)
END
Any help will be appreciated..
Regards,
Karen
View 6 Replies
View Related
Jun 18, 2014
I have two tables called ECASE and PROJECT
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
I need to prevent the deadlock .
View 3 Replies
View Related
Aug 15, 2007
I created very simple table with 3 columns and one is varchar(max) datatype
When i insert records thru VC++ ADO code i am getting this error
Exception Description Multiple-step OLE DB operation generated errors. Check e
ach OLE DB status value, if available. No work was done. and Error Number:: -2147217887
ODBC Driver: SQL Native Client
SQL server 2005
Table
CREATE TABLE [dbo].[RAVI_TEMP](
[ID] [int] NULL,
[Name] [varchar](max) NULL,
[CITY] [varchar](50) NULL
)
VC++ code
#include "stdafx.h"
#include <string>
#include <strstream>
#include <iomanip>
int main(int argc, char* argv[])
{
try
{
HRESULT hr = CoInitialize(NULL);
_RecordsetPtr pExtRst = NULL;
_bstr_t bstrtDSN, bstrtSQL;
bstrtDSN = L"DSN=espinfo;UID=opsuser;PWD=opsuser;";
bstrtSQL = L"SELECT * FROM RAVI_TEMP";
_variant_t vartValueID,vartValueNAME,vartValueCITY;
_bstr_t bstrtValueID,bstrtValueNAME,bstrtValueCITY;
pExtRst.CreateInstance(__uuidof(Recordset));
hr = pExtRst->Open(bstrtSQL, bstrtDSN, adOpenDynamic, adLockOptimistic, adCmdText);
hr = pExtRst->AddNew();
bstrtValueID = L"1";
vartValueID = bstrtValueID.copy();
bstrtValueNAME = L"RAVIBABUBANDARU";
vartValueNAME = bstrtValueNAME.copy();
bstrtValueCITY = L"Santa Clara";
vartValueCITY = bstrtValueCITY.copy();
pExtRst->GetFields()->GetItem(L"ID")->Value = vartValueID;
pExtRst->GetFields()->GetItem(L"NAME")->Value = vartValueNAME;
pExtRst->GetFields()->GetItem(L"CITY")->Value = vartValueCITY;
pExtRst->Update();
pExtRst->Close();
}
catch(_com_error e)
{
printf("Exception Description %s and Error Number:: %d",(LPTSTR)e.Description(),e.Error());
return e.Error();
}
return 0;
CoUninitialize();
}
if i use regular SQL ODBC driver, no error but its truncating the data
Adv Thanks for your help
View 1 Replies
View Related
Apr 21, 2005
Hello Friends
My problem is
Suppose Query is - Select * from tbl_Employee
TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST
I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this.
Serial Name Post
1 XYZ QER
2 SDF OPO
3 WER IPO
If any body knows please post the solution its urgent.
View 11 Replies
View Related
Dec 11, 2007
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
Oct 10, 2007
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
View 3 Replies
View Related
Jan 17, 2007
How to insert 100 record at a time by explicit inserting of identity column i.e.., by setting identity column to false
View 4 Replies
View Related
Feb 2, 2006
Hi,
I'm using VWD 2005 Express with SQL Server 2005 Express. I've created a page "create.aspx" which will be used to insert records into a table within the database.
I've used the DetailsView control together with a SqlDataSource control on the create.aspx page.
When I run the create.aspx page it retrieves the first record in the table of the database becuase the SqlDataSource is obviously performing a"Select * from Table" command.
I don't want this to occur. I want an empty page with no records. I know I can set a WHERE clause to a value that will never return any results but I don't beleieve this is an elegant way of doing things becuase I am 'hitting the database' for no reason other than to get the table structure poplutaed into the DetailsView control.
Questions:
1. Am I using the right controls? (in particular the SqlDataSource control)
2. Is there a better way of doing this?
Your advice is most appreciated. Thank you
View 7 Replies
View Related
May 29, 2006
pls i have this issue on inserting records into SQL SERVER Express. this is the code so far :-
imports system.data
imports system.data.sqlclient
Protected Sub btn1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn1.Click
Dim strConn As String = ConfigurationManager.ConnectionStrings("Connect1").ConnectionString
Dim ObjConn As New SqlConnection(strConn)
Dim strComm As String = "insert Products(ProductName,UnitPrice) values(@ProductName,@UnitPrice)"
Dim ObjComm As New SqlCommand(strComm, ObjConn)
ObjComm.Parameters.AddWithValue("@ProductName", txtprodname.Text)
ObjComm.Parameters.AddWithValue("@UnitPrice", SqlDbType.Money).Value = txtprice.Text
ObjConn.Open()
ObjComm.ExecuteNonQuery()
ObjConn.Close()
End Sub
End Class
The issue i have is that anytime i click the button to insert a new record, i get two records added to the database automatically
View 6 Replies
View Related
Jul 11, 2006
hi im a beginner trying to build an application that needs the user to enter some data into a table named Delievary. This table is related to another table named Products. The Products table has the following columns :- ProductsId - PK
ProductName
ProductPrice
Delievary table has the following columns
:- DelivaryId - PK
QuantityRecieved
ProductId - FK
i know to select i have to use inner joins but to insert is what i dont really know about. Please help me out
View 5 Replies
View Related
Jul 12, 2006
Ok i hav three tables namely customers, orders and products.
This are there structures Customers-: CustomerId-PK
FirstName
LastName
Address
Orders -: OrderId -PK
QuantityOrderd
OrderDate
CustomerId -FK
ProductId -FK
Products -: ProductId -PK
ProductName
UnitPrice
my question is that i need to relate them so that i can get info on a customer order
Thanks
View 1 Replies
View Related
Oct 26, 2006
Before I start driving myself nuts, I'd like to make sure my approach is correct.I want to create a simple job posting board.I have a text boxes for company name, email, job title, and job description, and a submit button.I created a table in my database called "JobPostings", with columns called "CoName", "CoEmail", "JobTitle", and "JobDesc"When someone fills out the fields and clicks submit, it will insert the new records.So,1. Is this the correct approach so far?2. What is the best way to display the job listings? A grid view?3. At submit time, how can I include that day's date?4. How can I get the records in the database to delete after 90 days?Thanks.
View 1 Replies
View Related
Feb 23, 2007
Hi, I am a rookie at sql and asp.net. I have another post is the asp.net section http://forums.asp.net/thread/1589094.aspx. Maybe I posted it in the wrong section.
I am collecting multiple rows from a gridview. I validated/confirmed I am capturing the correct values. How ever, I am having major problems passing these to an sql database. The problems that I know of is declaring my parameters correctly and then adding the values through a for each statement. I added the post over 30 hours ago with only me as the replier trying to refining or clarifying the problem. Part 1 is the code that works, part 2 is my problem, I have tried may different ways to resolve this but no luck.
Regards!
Protected Sub Botton1_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click**************** PART1 **************************Dim gvIDs As String = ""Dim ddIDs As String = ""Dim chkBox As Boolean = FalseDim chkBox1 As Boolean = False'Navigate through each row in the GridView for checkbox items ddIDs = DropDownList1.SelectedValue.ToStringFor Each gv As GridViewRow In GridView1.RowsDim addChkBxItem As CheckBox = CType(gv.FindControl("delCheckBox"), CheckBox)Dim addChkBx1Item As CheckBox = CType(gv.FindControl("CheckBox1"), CheckBox) If addChkBxItem.Checked Then chkBox = True gvIDs = CType(gv.FindControl("TestID"), Label).Text.ToString Response.Write(ddIDs + " " + gvIDs + " ") If addChkBx1Item.Checked Then chkBox1 = True Response.Write("Defaut Item") End If Response.Write("<br />")End IfNext******************** Part 2 *****************************************************Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)If chkBox ThenTry Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)" Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn) cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString) cmd.Parameters.AddWithValue("@test1ID", CType(gv.FindControl("TestID"), Label).Text.ToString) cmd.Parameters.AddWithValue("@TestDefault, CType(gv.FindControl("CheckBox1"), CheckBox)) cn.Open()
For Each ..... Problems here also
Next
cmd.ExecuteNonQuery()Catch err As SqlException Response.Write(err.Message.ToString)Finally cn.Close()End TryEnd If
View 7 Replies
View Related
Jun 28, 2007
I have the Temporary table:ItemDetailID (int)FieldID (int)FieldTypeID (int)ReferenceName (Varchar(250))[Value] (varChar(MAX))in one instance Value might equal: "1, 2, 3, 4"This only happens when FieldTypeID = 5.So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the Table FieldListValues(ItemDetailID, [value])I have created a function to split the [Value] into a table of INTs Any Advice?
View 7 Replies
View Related
Nov 8, 2004
Hi,
I need to insert records in two tables, one is main table and another is child table. From my aspx page I need to pass info. for one records in main table, insert that record into main table, get the is of the inserted table.
Then insert 15 records in the child table.
Everything must be in a transaction, either everything works or everything fails. Should I do it with aspx or should I pass arrays to a stored procedure?
Thanks!
View 7 Replies
View Related
Feb 11, 2000
Hi!
Are there any parameters that could faster the insert statement?
Maybe some cache or buffer?
Michal
View 1 Replies
View Related
Apr 24, 2007
Hello,
I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.
How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?
Does my request make sense? Please let me know if you have questions.
Thank you for your help!
CSDunn
View 3 Replies
View Related
Jan 12, 2015
I'm faced with the task of creating a script that will do a series of inserts. The insert statements will look the same except for one value, which will differ for each insert.
For simplicity's sake, here is a table with some inserts:
CREATE TABLE
#MyTable (
MyPk INT IDENTITY,
MyValue VARCHAR(24)
)
[Code] ....
Rather than having to modify the string for each individual update statement, I'm hoping there is a simple way to create a variable (perhaps an array) which holds an indefinite number of values, then have a loop which automatically does an insert for each specified value in the array. Is something like this possible? Or is there a preferred industry standard for this sort of thing that I should use instead?
View 2 Replies
View Related
Feb 16, 2006
I'm getting a foreign key error as well as Indentity Insert error when I'm trying to run a stored proc that inserts values from another table..help!!
View 4 Replies
View Related
Jun 27, 2006
I'm having a problem finding step by step instructions in the few books I have on how to upload to your new table and database once those are set up.
I've got some data in an excel file, and I try to run the DTS wizard to create a package to upload the data from excel, but I can't seem to straiten out my data type problems.
How do I get my excel data types to match my table data types? It shouldn't be this hard to upload something so rudimentary. =(
View 5 Replies
View Related
Jul 10, 2007
hello everyone
I have a question about new record insertion. I have a SQL Database that I have cofigured as a publisher for a mobile database. I want to be able using the PDA to insert new Customers and synchronize the CE database with the SQL DB. But I encounter a problem with the column field rowguid that SQL inserts as a unique indentifier during the publication. It cannot accept null values and I can't insert a value using the keyboard since the value is generated by SQL.
So my impression is that you cannot use PDA to insert new records, only to update old ones. Is that true or I miss something??
If someone knows the answer please let me know.
Thank you in advanced.
View 8 Replies
View Related
May 7, 2007
I have a query that takes blobs that have been created within the lastday out of sql server and places them on a disk drive. The query is astored proc which runs in a DTS job. The job shows failure when thereare no pdf's created in the last day, how do I correct this??Here is my stored proc code:CREATE PROCEDURE [dbo].[sp_PDFExport] ASbeginset quoted_identifier offdeclare @pk intdeclare @where_clause varchar(100)declare @file_name varchar (50)declare @debug varchar (50)Declare @cmd varchar (50)--debug/*if @Debug = 1print @cmdexec Master..xp_cmdShell @cmd */-- begin cursorDECLARE LOOKUP CURSOR FOR select pr.[id]from plan_report pr, plan_version pvwhere pv.plan_id = pr.plan_id and pv.status = '30' and pr.create_time
Quote:
View 4 Replies
View Related
Mar 23, 2006
I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...
here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
===
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = "usp_InserOrder"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int)
cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit)
cmd.Parameters("@MaterialID").Value = 3
cmd.ExecuteNonQuery()
trans.Commit()
=====
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
Please help, I am a newb and lost...as you can tell from my code...
View 4 Replies
View Related
Oct 10, 2001
Hello all,
I really need your help now, and I know I can always count on this group for tough answers to tough questions. OK, here's my dilemma. I have my trigger, which upon a record being inserted into db1.table1, inserts the same record into db2.table2 (SQL 7 db on the same server). What's happening is only a few of the fields are getting over there, but most are ending up NULL or 0. Everything besides the following records are inserting into the other database table properly:
EventName
EventStatusID
NumberofDays
NumberofStores
PreferredDate1
PleaseContactFlag
EventStoreSelection
EventClusterID
Note: The following fields have their Default Value set to (0)
SystemID
EventStatusID
Coop
NewProductFlag
TVSupportFlag
RadioSupportFlag
FSISupportFlag
RollbackPricing
PleaseContactFlag
Default Value set to (1):
KitInformationID
Here is the trigger:
CREATE TRIGGER EmoesImport ON mc_events FOR INSERT
AS
IF @@ROWCOUNT<>0
BEGIN
SET IDENTITY_INSERT mcweb2.dbo.mc_events ON
DECLARE @SystemID int
DECLARE @EventID int
DECLARE @AccountID int
DECLARE @BillingContactID int
DECLARE @EventName varchar(100)
DECLARE @EventStatusID tinyint
DECLARE @Coop bit
DECLARE @CoopSupplier varchar
DECLARE @SamplesPerDay int
DECLARE @BrochuresPerDay int
DECLARE @AverageDailyMovement int
DECLARE @SalesGoal int
DECLARE @NumberofDays int
DECLARE @NumberofHours int
DECLARE @NumberofStores int
DECLARE @WeekNumber tinyint
DECLARE @PreferredHourID tinyint
DECLARE @PreferredHourother char(20)
DECLARE @PreferredDate1 varchar(20)
DECLARE @PreferredDate2 varchar(20)
DECLARE @NewProductFlag bit
DECLARE @TVSupportFlag bit
DECLARE @RadioSupportFlag bit
DECLARE @FSISupportFlag bit
DECLARE @RollbackPricing bit
DECLARE @PleaseContactFlag bit
DECLARE @EventStoreSelection tinyint
DECLARE @EventClusterID int
DECLARE @KitInformationID tinyint
DECLARE @KitDescription varchar(1000)
DECLARE @KitOther varchar(200)
DECLARE @MCProgNum varchar(7)
DECLARE @rowguid uniqueidentifier
SELECT @SystemID = SystemID FROM INSERTED
SELECT @EventID = EventID FROM INSERTED
SELECT @AccountID = AccountID FROM INSERTED
SELECT @BillingContactID = BillingContactID FROM INSERTED
SELECT @EventName = EventName FROM INSERTED
SELECT @EventStatusID = EventStatusID FROM INSERTED
SELECT @Coop = Coop FROM INSERTED
SELECT @CoopSupplier = CoopSupplier FROM INSERTED
SELECT @SamplesPerDay = SamplesPerDay FROM INSERTED
SELECT @BrochuresPerDay = BrochuresPerDay FROM INSERTED
SELECT @AverageDailyMovement = AverageDailyMovement FROM INSERTED
SELECT @SalesGoal = SalesGoal FROM INSERTED
SELECT @NumberofDays = NumberofDays FROM INSERTED
SELECT @NumberofHours = NumberofHours FROM INSERTED
SELECT @NumberofStores = NumberofStores FROM INSERTED
SELECT @WeekNumber = WeekNumber FROM INSERTED
SELECT @PreferredHourID = PreferredHourID FROM INSERTED
SELECT @PreferredHourother = PreferredHourother FROM INSERTED
SELECT @PreferredDate1 = PreferredDate1 FROM INSERTED
SELECT @PreferredDate2 = PreferredDate2 FROM INSERTED
SELECT @NewProductFlag = NewProductFlag FROM INSERTED
SELECT @TVSupportFlag = TVSupportFlag FROM INSERTED
SELECT @RadioSupportFlag = RadioSupportFlag FROM INSERTED
SELECT @FSISupportFlag = FSISupportFlag FROM INSERTED
SELECT @RollbackPricing = RollbackPricing FROM INSERTED
SELECT @PleaseContactFlag = PleaseContactFlag FROM INSERTED
SELECT @EventStoreSelection = EventStoreSelection FROM INSERTED
SELECT @EventClusterID = EventClusterID FROM INSERTED
SELECT @KitInformationID = KitInformationID FROM INSERTED
SELECT @KitDescription = KitDescription FROM INSERTED
SELECT @KitOther = KitOther FROM INSERTED
SELECT @MCProgNum = MCProgNum FROM INSERTED
SELECT @rowguid = rowguid FROM INSERTED
INSERT INTO mcweb2.dbo.mc_events
(SystemID,
EventID,
AccountID,
BillingContactID,
EventName,
EventStatusID,
Coop,
CoopSupplier,
SamplesPerDay,
BrochuresPerDay,
AverageDailyMovement,
SalesGoal,
NumberofDays,
NumberofHours,
NumberofStores,
WeekNumber,
PreferredHourID,
PreferredHourother,
PreferredDate1,
PreferredDate2,
NewProductFlag,
TVSupportFlag,
RadioSupportFlag,
FSISupportFlag,
RollbackPricing,
PleaseContactFlag,
EventStoreSelection,
EventClusterID,
KitInformationID,
KitDescription,
KitOther,
MCProgNum,
rowguid)
VALUES
(@SystemID,
@EventID,
@AccountID,
@BillingContactID,
@EventName,
@EventStatusID,
@Coop,
@CoopSupplier,
@SamplesPerDay,
@BrochuresPerDay,
@AverageDailyMovement,
@SalesGoal,
@NumberofDays,
@NumberofHours,
@NumberofStores,
@WeekNumber,
@PreferredHourID,
@PreferredHourother,
@PreferredDate1,
@PreferredDate2,
@NewProductFlag,
@TVSupportFlag,
@RadioSupportFlag,
@FSISupportFlag,
@RollbackPricing,
@PleaseContactFlag,
@EventStoreSelection,
@EventClusterID,
@KitInformationID,
@KitDescription,
@KitOther,
@MCProgNum,
@rowguid)
SET IDENTITY_INSERT mcweb2.dbo.mc_events OFF
END
TIA,
Bruce Wexler
Programmer/Analyst
IT Department
Mass Connections
Ph: (562) 365-0200 x1091
Fx: (562) 365-0283
http://www.massconnections.com
View 1 Replies
View Related
Nov 5, 2004
Hi,
I need to insert a record in a master table and 20 records in a child table. I want to do this using stored procedure. Is it better to do it in stored procedure? Have somebody already tried this? Or is there any sample that I can use?
Thanks a lot!
View 1 Replies
View Related
