Inserting Multiple Records Using A @start And @count With One INSERT (and No Loop)

Aug 22, 2007

Is there a way to insert multiple records into a database table when you're just given "count" of the number of rows you want? I want to do this in ONE insert statment, so I don't want a solution that loops round doing 100 inserts - that would be too inefficient.

For example, suppose I want to create 100 card records starting it card number '1234000012340000'. Something like this ...

declare @card_start dec(16)
set @card_start = '1234000012340000'
declare @card_count int
set @card_count = 100

drop table card_table

create table card_table (
card_number dec(16),
activated char default 'N'
)

insert into card_table
select
... ???? ....

But WITHOUT using a while-loop (or any other kind of loop). I'm looking for fast and efficient code! Thanks.

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: Inserting Dummy Records Using Loop Statement

Jul 17, 2015

I have the following attributes in this Table A.

1) Location_ID (int)
2) Serial_Number (nvarchar(Max))
3) KeyID (nvarchar(max)
4) Reference_Address (nvarchar(max)
5) SourceTime (datetime)
6) SourceValue (nvarchar)

I am trying to create 1000000 dummy records in this this table A.How do i go about do it? I would like my data to be something like this

LOCATION_ID
1

Serial Number
SN-01

KeyID
E1210

Reference_Address
83

SourceTime
2015-05-21 00:00:00 000

SourceValue
6200

View 7 Replies View Related

Inserting Multiple Rows In Loop With A Sql Stored Procedures

Jun 4, 2008

I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
 
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
 sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
sqlServerCommand.CommandType = CommandType.StoredProcedure
sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows
sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
 
sqlServerCommand.ExecuteNonQuery()Next
 
sqlServerConn.Close()
objConn.Close()
End Sub
 
 
Thanks for eveones input in advance.

View 4 Replies View Related

Inserting Multiple Rows In Loop With A Sql Stored Procedures

Jun 4, 2008

I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
 
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
 sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
sqlServerCommand.CommandType = CommandType.StoredProcedure
sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows
sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
 
sqlServerCommand.ExecuteNonQuery()Next
 
sqlServerConn.Close()
objConn.Close()
End Sub
 
 
Thanks for eveones input in advance.

View 10 Replies View Related

Insert Records Loop For Dates?

Aug 27, 2012

I have a table with employee references and a startdate.

I want to insert into a new table an entry for each employee for each date since their startdate to today.

Eg

EMPTABLE

empref,startdate

0001,01.01.2012
0002,02.02.2012

What I require is

NEWTABLE

empref, Date
0001,01.01.2012
0001,01.02.2012
0001,01.03.2012
......
0001,08.27.2012
0002,02.02.2012
0002,02.03.2012
......
0002,08.27.2012

View 5 Replies View Related

Inserting Multiple Records Using For Each

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

Help With Inserting Multiple Records Using A CSV Value.

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

Inserting Multiple Records At Once

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

Inserting Multiple Records To A Table

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

Inserting Multiple Records In Different Tables

Apr 10, 2007

i wants to insert fields of one form in more than one table using stored procedure with insert query,but i gets error regarding foreign key

View 3 Replies View Related

Transact SQL :: Single Records - Loop To Only Insert Sum Total Of Each Day

Apr 22, 2015

I have the following query:

BEGIN TRAN

Declare @StartDt date = '2015-03-15'
Declare @EndDt date = DATEADD(M, 1, @StartDt)
declare @Days int = DATEDIFF(d, @StartDt, @EndDt)
declare @TBLSales as table(SaleDate date, Value money)
DECLARE @Today date
declare @TBLSalesCounts as table( StatusDesc varchar(100), Value money)

[Code] ....

I end up with the following result :

How would I alter my while loop to only insert the sum total of each day, instead of creating duplicates for each day.

E.g.
2015-04-22
1150.00
2015-04-21
 785.00
2015-04-20
 750.00

View 3 Replies View Related

Multiple Session IDs Inserting Duplicate Records

May 6, 2015

Have a table that is used during a conversion. It is hit pretty heavily with inserts by mulitple session ids. For performance reasons I cannot add a unique constraint on the column that is getting duplicates (it is an encrypted cell in varbinary). 

I do not want duplicates in this Encrypted Column. So before each insert the insert programs reads the table and verifies that the Encrypted Column value does not already exist. But unfortunately several duplicates are falling through the cracks. What are my options for not allowing duplicates?

View 3 Replies View Related

T-SQL (SS2K8) :: Inserting Multiple Records Each Containing Null Value For One Of Fields

Apr 11, 2014

I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:

Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,

[Code] ....

View 5 Replies View Related

T-SQL (SS2K8) :: Inserting Multiple Records From A Single Variable?

Apr 24, 2014

I have two tables. Table 1 has column "job", table 2 has column "job" and column "item". In table table 2 there are multiple "items" for each "job"

I would like to insert all of the "items" into table 1, based on a join table1.job = table2.job

View 7 Replies View Related

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID  key1  key2  key3  key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

T-SQL (SS2K8) :: How To Split One Record Into Multiple Records In Query Based On Start And End Date

Aug 27, 2014

I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

A random record in my Absences table shows (as an example):

resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)

I would like to have 4 lines in my query:

resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4

Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.

What I now get is:

resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...

... instead of the correct hours per date (8, 8, 8, 4).

A very simplified extract of my code is:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.

View 1 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

Inserting Multiple Rows With A Single INSERT INTO

Jul 23, 2005

Hi,I have an application running on a wireless device and being wireless Iwant it to use bandwidth as efficiently as possible. Therefore, I wantthe SQL statement that it uploads to the SQL Server to be as efficientas possible. In one instance, I give it four records to upload, whichcurrently I have as four seperate SQL statements seperated by a ";".However, all the INSERT INTO... information is the same each time, theonly that changes is the VALUES portion of each command. Also, I haveto have the name of each column to receive the data (believe it or not,these columns are only a small subset of the columns in the table).Here is my current SQL statement:INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18, '610T142', 'K8',520);INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30, '14841', 'B9', 344);Since the SQL statement INSERT INTO portion remains the same everytime, it would be good if I could have the INSERT INTO portion onlyonce and then any number of VALUES sections, something like this:INSERT INTO tblInvTransLog (intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18,'610T142', 'K8', 520)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30,'14841', 'B9', 344);But this is not a valid SQL statement. But perhaps someone with a morecomprehensive knowledge of SQL knows of way. Maybe there is a way tostore a string at the header of the command then use the string name ineach seperate command(??)

View 2 Replies View Related

Transact SQL :: Optimize Count Distinct For Multiple Groups Of Records?

May 21, 2015

I have a CTE returning a recordset which contains a column SRC.  SRC is a number which I use later to get counts and sums for the records in a distinct list. 

declare@startdate date = '2014-04-01'
declare@enddate date = '2014-05-01'
; with SM as
(
SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row,
u.SRC,

[Code] ....

-- If Referral start date is between our requested dates

ref.Referral_Start_Date between @startdate and @enddate

OR

-- Include referrals which started before our requested date, but are still active during our date range.

(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL ))
)
INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id
AND s.Service_Delivery_Unit_Id = 200
)
SELECT
count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,

[code]....

View 5 Replies View Related

SQL Server 2014 :: Transaction Rollback When Multiple Threads Are Inserting Records Into Table Because Of Trigger

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

Insert Multiple Records Into 2 Tables

Aug 29, 2006

I have searched in length and cant seem to find a specific answer.I have a tmptable to hold user "shoppingcart" ( internal supplies)What i want is to take when the user clicks order to take that table pull the records with that user and populate the order and order details tablesThe order table has a PK of orderID and the orderdetails has a FK of orderIDI know how to insert to the main table, but dont know how to populate the details at the same timeI have this.Insert into supplyordersselect requestor from tmpordercart where requestor = &name so how do i also take from the tmpordercart the itemno and quanity and put them into the orderdetails so that it links back to order table?

View 1 Replies View Related

How To Insert Multiple Records Into Table

Oct 11, 2007

insert into table1 (colname) values (value1)
can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table?
 We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take
value1, value2, value3 ....
from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks

View 5 Replies View Related

INSERT Records In Multiple Tables

Apr 2, 2004

I need to update two tables. I have created a view and am using the code in the attached file to insert into the two tables.

The page loads without errors, but I get this message that the view is not updatable because the modification affects multiple base tables.

I thought this was the purpose of views?

Does anyone have any suggestions? I am using Dreamweaver MX and SQL Server.

Thanks!
N

View 11 Replies View Related

Insert Multiple Records Using A Dropdownbox Or Select

Jan 29, 2008

Hi Can anyone help with inserting Multiple records in to db using a select command or dropdownbox or listbox
If I have a dropdownbox with 10 records in using a select query   DRopdownbox data = select username, memberId, age from Memberslist where  age = 30
I also have a textbox on the form called messageDetail
 Insert username, age and messagedetails into Messages for each item in dropdownbox
 

View 3 Replies View Related

Insert Multiple Records - First Record Is Insterted Twice: Why?

May 5, 2008

Hi and thanks for any advice.Right now I have a for loop that  inserts multiple records.  The first record is inserted into the database and I am not sure why.  Here is the code I am using -   Dim intPhotoKeyID As Integer
Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc "
InsertCmd += ") values " & _
"(@OriginalName, @GalleryID, @PhotoDesc " & _
") "
InsertCmd += "; SELECT CAST(scope_identity() AS int);"

Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString)
Dim MyCommand = New SqlCommand(InsertCmd, DBConnection)
MyCommand.Connection.Open()
MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150))
MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int))
MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text))
myCount = myCount & i 'Is zero for first two records
MyCommand.Parameters("@OriginalName").Value = "Orig" & i
MyCommand.Parameters("@GalleryID").Value = 5

MyCommand.Parameters("@PhotoDesc").Value = MyCommand.CommandText & myCount

Try
intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar())
MyCommand.Connection.Close()
Catch Exp As Exception
Response.Write(Exp)
'ResultsLabel.Text = Exp.ToString()
End Try
DBConnection.Close()  Below is the whole procedure which either uploads an image or directory of images and resizes them. Then gets the meta data from the image. Then creates an entry into the database for each image. If I am dealing with 4 images then 4 images are uploaded to the new gallery folder. But 5 entries are added to the database. Thanks again for any help, Jennifer  Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)


If ListDirectories.SelectedValue.Length > 0 Or fileUpEx.HasFile Then
Dim i As Integer
Dim selectedDirectory As String = ListDirectories.SelectedValue
Dim photoDescription As String
Dim photoAuthor As String
Dim photoTitle As String = ""
Dim photoName As String
Dim myFiles As String()
Dim fileCount As Integer

If multiUpload.Visible = True Then
myFiles = Directory.GetFiles(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/")
fileCount = myFiles.Length - 1
Else
fileCount = 0
End If

'Get last photo order from Photo table
Dim PhotoMaxOrder As Integer = 0
Dim DS As DataSet ' DataSet object
Dim SQL As String = "SELECT MAX(PhotoOrder) FROM Photos WHERE GalleryID=" & Request("ID").Trim
Dim connString As String = ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString
Dim sqlDA = New SqlDataAdapter(SQL, connString)
DS = New DataSet
sqlDA.Fill(DS, "Photos")
If Not DS.Tables("Photos").Rows(0).Item(0) Is System.DBNull.Value Then
If DS.Tables("Photos").Rows.Count > 0 Then
PhotoMaxOrder = Convert.ToInt32(DS.Tables("Photos").Rows(0).Item(0)) + 1
End If
Else
PhotoMaxOrder = 1
End If

For i = 0 To fileCount

If multiUpload.Visible = True Then
photoName = Right(myFiles(i), InStr(StrReverse(myFiles(i)), "/") - 1)
Else
photoName = fileUpEx.PostedFile.FileName
End If

If InStr(photoName, ".jpg") > 0 Then
Dim MyPhoto As Bitmap

If multiUpload.Visible = True Then
MyPhoto = New Bitmap(myFiles(i))
Else
MyPhoto = Bitmap.FromStream(fileUpEx.PostedFile.InputStream)
End If
'testFile = myFiles(i)
Try 'Get photo description
Dim Make As PropertyItem = MyPhoto.GetPropertyItem("270")
Dim ascii As Encoding = Encoding.ASCII
photoDescription = ascii.GetString(Make.Value, 0, Make.Len - 1)
Catch ex As Exception
photoDescription = ""
End Try

Try 'Get photo author
Dim Make As PropertyItem = MyPhoto.GetPropertyItem("315")
Dim ascii As Encoding = Encoding.ASCII
photoAuthor = ascii.GetString(Make.Value, 0, Make.Len - 1)
Catch ex As Exception
photoAuthor = ""
End Try

Dim photoXmpData As String = GetXmpXmlDocFromImageStream(MyPhoto)

If Not photoXmpData = "" Then
photoTitle = GetXmpXmlNode(photoXmpData)
End If

'insert photo record into photo table






Dim intPhotoKeyID As Integer
Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc "
InsertCmd += ") values " & _
"(@OriginalName, @GalleryID, @PhotoDesc " & _
") "
InsertCmd += "; SELECT CAST(scope_identity() AS int);"

Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString)
Dim MyCommand = New SqlCommand(InsertCmd, DBConnection)
MyCommand.Connection.Open()
MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150))
MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int))
MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text))
myCount = myCount & i 'Is zero for first two records
MyCommand.Parameters("@OriginalName").Value = "Orig" & i
MyCommand.Parameters("@GalleryID").Value = 5

MyCommand.Parameters("@PhotoDesc").Value = MyCommand.CommandText & myCount

Try
intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar())
MyCommand.Connection.Close()
Catch Exp As Exception
Response.Write(Exp)
'ResultsLabel.Text = Exp.ToString()
End Try
DBConnection.Close()

'check photo width and height
Dim NewFilePath As String = Server.MapPath("/appscode/galleries/photos/g" & Request("ID").Trim & "/") & "p" & intPhotoKeyID.ToString & ".jpg" ' & photoName
lblMessage2.Text = lblMessage2.Text & InsertCmd
If MyPhoto.Width.ToString = "200" Or MyPhoto.Height.ToString = "200" Then
'just copy the image
If multiUpload.Visible = True Then
File.Copy(myFiles(i), NewFilePath, True)
Else
fileUpEx.SaveAs(NewFilePath)
End If
Else
'resize image
Dim NewSize As System.Drawing.Size = New System.Drawing.Size(200, 200)
ResizePicture(MyPhoto, NewFilePath, NewSize) 'and save it
End If
MyPhoto.Dispose()
GC.Collect()
End If

Next
If Not myFiles Is Nothing Then
If myFiles.Length > 0 And multiUpload.Visible = True Then
'delete directory that was just processed
Directory.SetCurrentDirectory(Server.MapPath("."))
Directory.Delete(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/", True)
ListDirectories.Items.Remove(selectedDirectory)
'lblMessage2.Text = "Your file(s) have been added."
End If
Else
'lblMessage3.Text = "Your file has been added."
End If

Else
lblMessage2.Text = "Please select a folder."
End If

lblMessage3.Text = myCount
End Sub     

View 8 Replies View Related

How To Insert Multiple Records Into Sql 2000 Table At Once ?

Nov 25, 2004

hello,
I am new to Slq 2000 Database,Now I create an asp.net application with sql 2000,
in my database I have two 2 table lets' say "OrderHead" and "OrderDetail",they look like this:
OrderHead orderdetail
---order no ----orderno
---issuedate ----itemname
---supplier ----desccription
---amount -----price
----Qty
Now I created a user-defined Collection class to storage order detail data in memory
and bind to a datagrid control.
I can transfer Collection data to xml file ,my problem as below :
There have multiple records data in my xml file,and I want to send the xml file as argument to a store procedure in sql 2000

anyone can give me some advise or some sample code ?

thanks in advanced!

View 2 Replies View Related

Need To Insert Records From Multiple Access Tables Into 1

Mar 17, 2014

Using SSE 2012 64-bit.I need to insert records from multiple Access Tables into 1 Table in SSE and ensure no duplicates are inserted.This is executing, but is very slow, is there a faster way?

Code:
INSERT INTO dbTarget.dbo.tblTarget
(All fields)
SELECT
(All Fields)
FROM dbSource.dbo.tblSource
WHERE RecordID NOT IN (SELECT RecordID FROM dbTarget.dbo.tblTarget)

View 6 Replies View Related

Transact SQL :: Insert Records From Multiple Tables

Aug 30, 2015

This is a bit lengthy, but lets say we have three tables

a) tblSaleStatementCustomer
b) tblCreditors
c) tblReceiptDue

which shows records like below

Table 1 - tblSaleStatementCustomer

ID   CustomerName     VoucherType    Outbound     Inbound     CustomerType
----------------------------------------------------------------------------------------------
1     ABC                              Sales                10000              0                   Dealer
2     MNC                             Sales                  9000              0                   Dealer
3     MNC                             Sales                  4000              0                   Dealer

Table 2 -  tblCreditors

ID   Name     OpeningBalance
----------------------------------------------------------------------------------------------
1     ABC          20000  
2     MNC         15000 
3     XBM         18000
4     XYZ          12000

View 2 Replies View Related

Insert Multiple Records Into A Single Field On Another Table

Feb 15, 2012

I have a table JOBCODE which contains a list of codes.

I want to insert these values into table VIEWS as a list separated by spaces.

E.G.

Table Jobcodes looks like this

code
1
2
3
4
5
6

And I want table Views to look like this:

field1
1 2 3 4 5 6

How do I go about this?

View 4 Replies View Related

Insert Multiple Records Using One Stored Procedure Call. SQL SERVER 7

Mar 19, 2008

Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?
 
Thanks, Gary

View 9 Replies View Related

SQL Server 2014 :: Add Multiple Records To Table (insert / Select)?

Jul 31, 2014

I am trying to add multiple records to my table (insert/select).

INSERT INTO Users
( User_id ,
Name
)
SELECT ( SELECT MAX(User_id) + 1
FROM Users
) ,
Name

But I get the error:

Violation of PRIMARY KEY constraint 'PK_Users'. Cannot insert duplicate key in object 'dbo.Users'.

But I am using the max User_id + 1, so it can't be duplicate

This would insert about 20 records.

Why the error?

View 7 Replies View Related

Debug Stored Procedure That Uses Comma Delimited List To Insert Multiple Records

Jan 18, 2006

I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:
exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8'
Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)
CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150)
ASBEGIN
  DECLARE @FileID INT
 SET NOCOUNT ON
/*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate)
 Select @FileID=@@Identity
/*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000)
 SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT  ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')'  exec(@ProductFilesInsert) EndGO
 
 

View 4 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related







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