Inserting The Diff Rows Into A Table
Sep 24, 2007
Hi,
I have two tables as follows
Table TempTab
{
CatId varchar(20),
lastupdate Datetime
}
Table MainTab
{
CatId varchar(20),
lastupdate Datetime
}
and the data in those tables are as follows
Table TempTab
{
CatId LastUpdate
------------------------------
Cat1 D1
Cat2 D2
Cat3 D3
Cat4 D4
}
Table MainTab
{
CatId LastUpdate
------------------------------
Cat1 D1
Cat3 D3
Cat5 D5
}
I need a query to insert the differences into the MinTab, i mean to say the fincal MainTab should look like as follows
Table MainTab
{
CatId LastUpdate
------------------------------
Cat1 D1
Cat2 D2
Cat3 D3
Cat4 D4
Cat5 D5
}
can any one please let me know the query
Thanks alot
~Mohan
View 3 Replies
ADVERTISEMENT
Jul 24, 2015
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 3 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Oct 22, 2012
I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.
View 6 Replies
View Related
Jan 19, 2008
Code Block
Hi,
I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:
Table Name: metrics_ladder
id security_id metric_id value
1 3 80 125.45
2 3 81 548.45
3 3 82 145.14
4 3 83 123.32
6 4 80 453.75
7 4 81 234.23
8 4 82 675.42
.
.
.
Table Name: metric_details
id metric_id metric_type_id metric_name
1 80 2 Fiscal Enterprise Value Historic Year 1
2 81 2 Fiscal Enterprise Value Current Fiscal Year
3 82 2 Fiscal Enterprise value Forward Fiscal year 1
4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2
5 101 3 Calendar Enterprise value Historic Year 1
6 102 3 Calendar Enterprise Value Current Fiscal Year
5 103 3 Calendar Enterprise value Forward Year 1
6 104 3 Calendar Enterprise Value Forward Year 2
Table Name: metric_type_details
id metric_type_id metric_type_name
1 1 Raw
2 2 Fiscal
3 3 Calendar
4 4 Calculated
The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:
id security_id metric_id value
1 3 101 <calculated value>
2 3 102 <calculated value>
3 3 103 <calculated value>
4 3 104 <calculated value>
Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.
Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,
metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally
metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).
As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:
security_id 80 81 82 83 101 102
----------- -- -- -- -- -- --
3 125.45 548.45 145.14 123.32 <calculated value> <calculated value>
4 ...
.
.
.
which is then unpivoted.
The SQL that achieves this is more or less as follows:
*********
START SQL
*********
declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...)
-- Dummy year variable to make it easier to use MONTH() function
-- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc...
DECLARE @DUMMY_YEAR VARCHAR(4)
SET @DUMMY_YEAR = 1900;
with temp(security_id, metric_id, value)
as
(
select ml.security_id, ml.metric_id, ml.value
from metrics_ladder ml
where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...)
-- only consider securities with fiscal year end not equal to december
and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec')
)
insert into @calendar_averages
select temppivot.security_id
-- Net Income
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103]
,NULL as [104]
-- Share Holders Equity
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107]
,NULL as [108]
-- Capex
-- Sales
-- Accounts payable
etc...
..
..
from temp
pivot
(
sum(value)
for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)
) as temppivot
inner join company_details cd on temppivot.security_id = cd.security_id
*********
END SQL
*********
The result then needs to be unpivoted and stored in metrics_ladder.
And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.
Many thanks (if you've read this far!)
M.
View 6 Replies
View Related
May 21, 2008
Hi all,
I am trying to insert some new rows into an existing SQL table. The table name is Agt_table, and I want to add some data for some new agents into existing columns:
Agent name, agent code, phone number, fax number
Example -
I want to add the following record to my existing table Agt_table
Agent name: ABC Company
Agent code: 012345
Phone #: 555-555-5555
Fax#: 555-555-5555
How would I write the sql statement to do that?
Thanks so much!!!
View 12 Replies
View Related
Jun 20, 2007
Hi,
Is it possible to insert multiple rows in a table using one INSERT statment. If yes, how can I do that ? I tried doing this using the substitution method.
Using Substitution Method - This is how,I proceeded.
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
SELECT ‘&EMPN’, ‘&ENAM’, ‘&JO’, ‘&MG’, ‘&HI’, ‘&SA’, ‘&COMM’, ‘&DEPTN’ UNION ALL
SELECT ‘&EMPN’, ‘&ENAM’, ‘&JO’, ‘&MG’, ‘&HI’, ‘&SA’, ‘&COMM’, ‘&DEPTN’
Let me know if this is correct.
Thanks,
Vandana
View 2 Replies
View Related
Aug 10, 2006
Hi,
I have a DataTable in memory and I want to write a C# code to dump the data into a SQL database. Is there a faster way of dumping millions of rows into a SQL table besides running INSERT INTO row by row?
Thank you,
Jina
View 3 Replies
View Related
Dec 29, 2004
I am using the below SQL to insert a table. The problem is after I run this, I run another script to populate the table (see below). The population script will work if I run it as INSERT INTO ... SELECT TOP 99.9999999 PERCENT ..., but if I put 100 PERCENT, or just use no percent limiter I get the following error: Msg 8624, Internal SQL Server error.
It is weird b/c once something is inserted in the table, i can run the populate script without any problems. Any idea as to why this is happening?
Thanks,
Dave
TABLE GENERATION SCRIPT
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbCelebroAds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbCelebroAds]
GO
CREATE TABLE [dbo].[tbCelebroAds] (
[AdID] [int] IDENTITY (1, 1) NOT NULL ,
[BranchCode] [int] NOT NULL ,
[PropertyID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AdScheduleCode] [int] NOT NULL ,
[RecAtCentral] [int] NULL ,
[AdRan] [int] NOT NULL ,
[AdStatusID] [int] NOT NULL ,
[PatID] [int] NULL ,
[RunDate] [datetime] NOT NULL ,
[CreationCost] [float] NOT NULL ,
[BillCost] [float] NOT NULL ,
[AddedDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
TABLE POPULATION SCRIPT
INSERT INTO tbCelebroAds (BranchCode, PropertyID, AdScheduleCode, PatID, AdStatusID, AdRan, RunDate, CreationCost, BillCost, AddedDate)
SELECT [TOP 100 PERCENT or no percent limiter doesn't work, TOP 99.9999999 PERCENT does] BranchCode, PropertyID, AdScheduleCode, cp.PatID, 6, 0, CAST(PubDate AS DATETIME), CAST(ISNULL(pat.Cost,0) AS DECIMAL(10,2)), 0, GetDate()
FROM tbCelebroView cv
LEFT JOIN tbCelebroPubs cp ON cv.PublicationName = cp.PublicationName AND cv.AdSectionName = cp.AdSectionName
LEFT JOIN tbPubToAdType pat ON cp.PatID = pat.PatID
WHERE CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) NOT IN
(SELECT CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) FROM tbCelebroAds)
View 4 Replies
View Related
Nov 29, 2006
Hello all,my first post here...hope it goes well. I'm currently working onstored procedure where I translated some reporting language into T-SQLThe logic:I have a group of tables containing important values for calculation.I run various sum calculations on various fields in order to retrievecost calculations ...etc.1) There is a select statement which gathers all the "records" whichneed calculations.ex: select distinct Office from Offices where OfficeDesignation ='WE' or OfficeDesignation = 'BE...etc.As a result I get a list of lets say 5 offices which need to becalculated!2) A calculation select statement is then run on a loop for each ofthe returned 5 offices (@OfficeName cursor used here!) found above.Anexample can be like this(* note that @WriteOff is a variable storing the result):"select @WriteOff = sum(linecost * (-1))From Invtrans , InventoryWhere ( transtype in ('blah', 'blah' , 'blah' ) )and ( storeloc = @OfficeName )and ( Invtrans.linecost <= 0 )and ( Inventory.location = Invtrans.storeloc )and ( Inventory.itemnum = Invtrans.itemnum )"...etcThis sample statement returns a value and is passed to the variable@WriteOff (for each of the 5 offices mentioned in step 1). This is donearound 9 times for each loop! (9 calculations)3) At the end of each loop (or each office), we do an insert statementto a table in the database.
Quote:
View 9 Replies
View Related
Jul 20, 2005
To anyone that is able to help....What I am trying to do is this. I have two tables (Orders, andOrderDetails), and my question is on the order details. I would liketo set up a stored procedure that essentially inserts in the orderstable the mail order, and then insert multiple orderdetails within thesame transaction. I also need to do this via SQL 2000. Right now ihave "x" amount of variables for all columns in my orders tables, andall Columns in my Order Details table. I.e. @OColumn1, @OColumn2,@OColumn3, @ODColumn1, @ODColumn2, etc... I would like to create astored procedure to insert into Orders, and have that call anotherstored procedure to insert all the Order details associated with thatorder. The only way I can think of doing it is for the program to passme a string of data per column for order details, and parse the stringvia T-SQL. I would like to get away from the String format, and gowith something else. If possible I would like the application tosubmit a single value per variable multiple times. If I do it this waythough it will be running the entire SP again, and again. Anysuggestions on the best way to solve this would be greatlyappreciated. If anyone can come up with a better way feel free. Myonly requirement is that it be done in SQL.Thank you
View 3 Replies
View Related
Dec 16, 2007
Below is a simplified table & dataset to illustrate a problem I'm experiencing with a more complex one.
Code Block
create table #test(
recno smallint PRIMARY KEY,
value decimal (18,2))
insert into #test values (1, 3.57)
insert into #test values (2, 5.32)
insert into #test values (3,6.29)
insert into #test values (4, 9.25)
insert into #test values (5, 0.84)
Method 1: I tried inserting rows from #test into a temp table (#table) as follows
Code Block
declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
However, this yields an error message:
Code Block
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name '##table'.
Note - you can comment out the insert into ##table line above to view the results that I'm trying to put into ##table.
Method 2: next I tried explicitly creating ##table & rerunning the loop containing the insert
Code Block
create table ##table (
recno smallint,
value decimal (18,2),
originalrecno smallint)
declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
This worked - it inserted the data from the select statements in the loop into ##table.
Question - why won't method 1 work?
View 6 Replies
View Related
Feb 5, 2007
Hi,
Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can erase all the records in my excel file before i export the new data from SQL table?
What i want is to delete the rows in the destination file before inserting new records.
Thanks a lot.
View 7 Replies
View Related
Feb 12, 1999
I need to write a 'select' statement to fetch data from different tables, which are located on different servers.
Can any one help in writing this 'select' statement with out moving the tables on to same server.
Thanks in Advance.
Murali Raparla.
View 2 Replies
View Related
May 1, 2008
Hello I have a Source database and a Target database.
I want to join one table from the source to the other table in the target.
Please can some one write a sql query for this.
i gues its something like
select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination
Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.
View 4 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
Apr 20, 2004
I have 17 tables with names as Breaktable1, Breaktable2,.... till
Breaktable17
i make a query like this
set nocount on
Declare @sEventtable varchar(20)
Declare @sstartzoneid varchar(20)
Declare @ssttopzoneid varchar(20)
Select @sstartzoneid ='1'
Select @ssttopzoneid = '17'
select @sstartzoneid = convert(smallint,@sstartzoneid)
if @sstartzoneid<> 0
DECLARE Tablecursor FOR <-------> ( error here )
select status, sum(cost)
FROM "breaktable " + @sstartzoneid
where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003')
group by status
CLOSE tableCursor
DEALLOCATE tableCursor
can somebody help ????
View 14 Replies
View Related
Sep 25, 2014
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052
Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
View 3 Replies
View Related
Dec 10, 2007
Hi Madhu,
my table does not have primary key so i created a seperate index on each of the table.
I used the recommended tablediff utility and it works successfully. But its only show the difference of records in each table and does not copy rows from source to destination and destination to source table. I was expecting database1.dbo.table1 contains same records as in database1.dbo.table2.
C:Program FilesMicrosoft SQL Server90COM>tablediff /sourceserver kashif-pcs
qlexpress /sourcedatabase AB /sourcetable table1 /destinationserver kashif-pcsq
lexpress /destinationdatabase CD /destinationtable table2
Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:
/sourceserver kashif-pcsqlexpress
/sourcedatabase AB
/sourcetable table1
/destinationserver kashif-pcsqlexpress
/destinationdatabase CD
/destinationtable table2
Table [AB].[dbo].[table1] on kashif-pcsqlexpress and Table [CD].[dbo].[table2]
on kashif-pcsqlexpress have 5 differences.
Err Sno
Src. Only 101
Src. Only 102
Dest. Only 103
Dest. Only 104
Dest. Only 105
The requested operation took 0.466767 seconds.
Can you write a short script for my problem, just like comparison of database1.dbo.table1 compares in database2.dbo.table2 and which ever records not present it should copy those and vice-versa.
It means Database1.dbo.table1 contains 5 records
Database2.dbo.table2 contains 5 records
Regards
Kashif Chotu
View 3 Replies
View Related
Mar 22, 2007
Hello,
I've been able to startup a transactional replication between 2 database for some tables that only have different table names. Now there are still some tables that not only have different names but also different column names, can this be done in the wizzard ??
Thx
View 3 Replies
View Related
Dec 14, 2007
I have an item table (#Codes) which contains itemIDs, some of which are always used in a subsequent data table (#Data) to hold information about a case+scearnio. These item rows have a standarditem value = 1. Rows in the item table which may be included in the subsequent data table (#Data), but not for every single case+scearnio, are considered nonstandard rows and therefore have a standarditem value = 0. I have been trying to write some code that automatically inserts rows from #Codes which have standarditem = 1 into #Data for each of the caseID+scenarioIDs in #Cases but have not figured it out yet. See "desired result" below to see exactly the output I'm trying to achieve.
Also, is there a way to insert the standarditem = 1 rows from #Codes into #Data when new caseID+scenarioID rows are added to #Cases? Ultimately, I'd like to either create a button for the user to click in the Access interface that inserts these rows when clicked OR when the user enters a new caseID+scenarioID in #Cases the rows just automatically appear for the new case row. This way, the rows will already have the caseID, scenarioID & itemID fields already populated and all the user will have to do is enter the item value and be able to manually add standarditem = 0 rows and their values if needed.
Code Block
create table #Codes (itemID nvarchar(2), standarditem int)
insert into #Codes values (1,1)
insert into #Codes values (2,0)
insert into #Codes values (3,1)
insert into #Codes values (4,1)
insert into #Codes values (5,0)
create table #Cases (caseID nvarchar(5), scenarioID nvarchar(15), createdate datetime)
insert into #Cases values (823, 1, '20071210')
insert into #Cases values (823, 2, '20071211')
insert into #Cases values (824, 1, '20071213')
create table #Data (caseID nvarchar(5), scenarioID nvarchar(15), itemID nvarchar(2), itemvalue decimal (18,0))
data in #Codes:
itemID standarditem
1 1
2 0
3 1
4 1
5 0
data in #Cases:
caseID scenarioID createdate
823 1 2007-12-10 00:00:00.000
823 2 2007-12-11 00:00:00.000
824 1 2007-12-13 00:00:00.000
desired result in #Data:
caseID scenarioID itemID itemvalue
823 1 1 <null>
823 1 3 <null>
823 1 4 <null>
823 2 1 <null>
823 2 3 <null>
823 2 4 <null>
824 1 1 <null>
824 1 3 <null>
824 1 4 <null>
Thanks in advance for your help.
View 1 Replies
View Related
Nov 14, 2007
Hi,
I have Table A . we already have 80 columns . we have to add 65 more columns.
we are populating this table from oracle .and we need to populate those 65 columns again from the same table.
Is it a better idea to add those new 65 columns to the same table or new table.
If we go for the same table then loading time will be double, If I go for new table and If i am able to run both the packages which loads table data from same oracle server to difffrent sql tables then we should be good. But if we run in to temp space issues on oracle server . Then i have to load the two tables separately which consumes the same time as earlier one.
I was thinking if there is a way in SSIS where I can pull data from same oracle table in to two diff sql tables at same time?
View 1 Replies
View Related
May 26, 2004
hi everybody,
i have an application that generate a lot rows from 1 mellion to 2 mellions rows
i wana insert this record in MS SQL server in a fast way
i am currentlly loop through this records while it is loaded in dataset
building a command text that generate insert query for each row
and run it against SQL server
but it takes a lot of time to be finished
is there r a way to bulk insert this data?
thanks 4 ur help.
Bolos
View 7 Replies
View Related
Jul 30, 2004
Does anybody know how to insert multiple rows into a table with one query string(i'm using sqlServer)?
Thanx,
David
View 1 Replies
View Related
Jul 14, 2005
Hi, would like to know if there are any links or sample code to learn how to Insert multiple rows with 1 sql statement.Also, can the inserted values' source be from a table in another database table or from a dataset?I am actually trying to insert about 117 rows of data.Table 1======UID Primary Key TeamCode a code value representing different teams Week will equal to 2Points nullable valuee.g.Table 1======UID TeamCode Week Points1 A1 1 1002 A2 1 99trying to insert into table 1Table 1========UID TeamCode Week Points1 A1 1 1002 A2 1 993 A1 2 null4 A2 2 nulletc...As you can see, UID is primary key, TeamCode may repeat according to week value Week is a constant Points will be nullHow can I do that with a single Insert Command? Thank you for your help. :)
View 5 Replies
View Related
Jul 16, 2002
Does anybody have a sample SQL script that will select table A and compare it to table B. If a row exists in both table A and table B, it will update the columns in table B with the columns in table A. If the row does not exist in table B, it will insert a row in table B using the row in table A. Is this possible?
Thanks Mitch
View 2 Replies
View Related
Oct 5, 2007
Why does this code tell me that I inserted 2 rows when I really only inserted one? I am using SQL server 2005 Express. I can open up the table and there is only one record in it. Dim InsertSQL As String = "INSERT INTO dbCG_Disposition ( BouleID, UserName, CG_PFLocation ) VALUES ( @BouleID, @UserName, @CG_PFLocation )"Dim Status As Label = lblStatus
Dim ConnectionString As String = WebConfigurationManager.ConnectionStrings("HTALNBulk").ConnectionString
Dim con As New SqlConnection(ConnectionString)
Dim cmd As New SqlCommand(InsertSQL, con)
cmd.Parameters.AddWithValue("BouleID", BouleID)
cmd.Parameters.AddWithValue("UserName", UserID)
cmd.Parameters.AddWithValue("CG_PFLocation", CG_PFLocation)
Dim added As Integer = 0
Try
con.Open()
added = cmd.ExecuteNonQuery()
Status.Text &= added.ToString() & " records inserted into CG Process Flow Inventory, Located in Boule_Storage."
Catch ex As Exception
Status.Text &= "Error adding to inventory. "
Status.Text &= ex.Message.ToString()
Finally
con.Close()
End Try Anyone have any ideas? Thanks
View 5 Replies
View Related
Nov 24, 1999
Hi,
I need to insert a very large number of rows into a table (in SQL Server 7.0) using ADO.
Could you please tell me i there is a way for FAST insert, something similar to BCP ... or any other way of
inserting large number of rows efficiently
Thanks
View 2 Replies
View Related
Jul 19, 2004
Hi,
I am trying to insert 9 rows into an table at the same time. My situation is this...
I have a survey page. There are 9 parts with each part ment for an individual person. Each part has 8 questions, and each part has the same 8 questions
The questions are answered using one of the answers in a drop down box.
So when the surveyer clicks submit, all the 9 parts should be entered into the table.
If this is confusing, I have the form up on the Internet at...
http://www.lavenderlane.ie/wage_survey_test.asp
I can insert one part no problem, (when I reduce the form to only 1 part) but i need to insert all of the 9 parts simultaneously. I reckon its some sort of for loop but if u could help me out i would appreciate it!
Thanks a lot
View 12 Replies
View Related
Sep 3, 2013
Say for instance I got 2 tables
Subject Table and a Student Table
The Subject Table consist of the following attributes:
Subject_ID [PK], Subject_Name, Course_ID and Course_Name
The Student Table consists of the following attributes:
Subject ID [FK], Students_Name, Students_bday, Students_age, Students_height and Students_weight
How can I use the INSERT function when I would like to add a row with the following details:
Course_Name : Biotechnology
Students_Name : Fred
Students_bday : 01/JAN/1990
Stundets_age : 54
how to use the INSERT function for multiple tables.
View 4 Replies
View Related
Sep 18, 2007
As a beginner i am having trouble with this.
i have two different tables , both have a name column, nvarchar datatype.
I would like to select from table B all the rows which contain a name which is not in table A.
Then insert these rows, into table A
tried a few different ways & just keep getting strange errors that refer to courier font ??
SQL Team Your my Hero !
View 11 Replies
View Related
Jun 15, 2006
I am ultra new to this so thanks in advance for any help.
I was trying to create a connection to a database that I created in SQL Express. I am essentially trying to submit three attributes to the existing database from a table that consists of three textboxes and a submit button. I would like all of the code to be in the head of the page (because that is the standard here) so I wanted to know what the connection string should be in Visual Basic 2005 Express to establish a connection on the same machine. I'm not sure about the connection string, but I am also not sure about a lot of the code. Also, the Using clauses seem to give me an error (where should it go?). This is what I have in the head of the page (visual C# by the way). Also, I got this from http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx :
Using System.Data;Using System.Data.SqlClient;protected void Button1_Click(object sender, EventArgs e){SqlConnection conn = new SqlConnection("Data Source=(local);Database=CustomersDB;Trusted Connection=true");conn.Open();string insertstring = @" insert into Catagories (CustomerID, CustomerName, CustomerEmail) values (" + textbox1.value + ", " + textbox2.value + " + ", textbox3.value");SqlCommand cmd = new SqlCommand(insertstring, conn);Sql.ExecuteNonQuery();conn.close();}
If there is absolutely any insite into the problems with my code, I really appreciate it.
View 12 Replies
View Related
Aug 11, 2006
I apologize since this seems to be a fundamental question, but I did try to search and there seems to be something wrong - after clicking on the "search" button, the page just will not update... I even tried "advanced search" but no luck there, either.
Anyway, my question is about writing multiple data rows to tables in a SQL Server database. Currently I'm using embedded SQL from a VC++ 2005 .Net program to write data to a SQL 2005 Express server (either on the same PC or on another, via TCP). As data becomes available, I issue INSERT statements, one by one. One row is about a dozen columns, typically 8 bytes each, for each of the few tables in the database. In most cases, I have several rows of data available at the same time. Those rows come at the rate of around 200-1000 per second for the different tables. With that setup, my SQL server is not able to keep up - the data ends up getting buffered in the program, waiting for the server, the server process uses just about all the CPU cycles it can get, etc.
I'm reading the "SQL in a nutshell" book from O'Reilly but it's mostly language oriented, it doesn't say much about performance improvements. It would seem natural that one could improve the performance. For example, when I do a SELECT query, the data comes very quickly, which makes me think that it is the overhead of making those individual calls with small amount of data. I would think that I should be able to request that multiple rows are written at the same time with a single SQL statement, and that this would improve the performance. However, I have no idea how to do that.
I did try one thing - enlisting the sequence of INSERTs into a single transaction, thinking it will all get buffered and only get written to the server after the Commit command is issued. I did that but it doesn't seem to help. What should I try to alleviate this problem? One thing to consider: although I use SQL Server for testing, I am trying to keep compatibility with other databases, e.g., ODBC to any available data source, including MS Access over Jet. I would love it if the solution to this were compatible (i.e., not involve any Transact SQL or other vendor-specific tricks).
I thank you in advance for your assistance!
Kamen
View 14 Replies
View Related
