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


ADVERTISEMENT

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

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

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)

So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)

In Table1 There might be 10 Groupid records all of which are different.

View 9 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

SQL Server 2014 :: Select Records Start With %

Jul 28, 2015

I need to select records in column that start with '% ' -- %space e.g. column A = % mytest

I tried select column name with like operator but it select records who start with % and ignore space

select columnA from table where columna like '% %';

View 9 Replies View Related

Select Multiple Records Into A Table

Jul 20, 2005

I am building an invoicing database. I have no problems searching fordue dates and generating the invoice header. The problem is generatingthe invoice detail.My customers may have more than one item that needs to go into theinvoice detail table.For example:customer #123 has 2 items that need to be placed into the detailtable.Rate 1 email accountRate 2 hosting accountI have to get both of these records into the detail table.When using the conventional method, I get something alongthe lines of" insert failed. more than one record was returned"-------INSERT INTO detailSELECT (SELECT max([id])FROM iheader),CustomerRates.custid,rates.Price, rates.nameFROM CustomerRates INNER JOIN Rates ON CustomerRates.Rateid = rates.IDWHERE NextBill > GETDATE()-------I have even considered a cursor to loop through the records but I cantmake it run properly. I am not crazy about the performance of cursorsanyway.Any aideas would be greatly apreciated.

View 1 Replies View Related

SQL Server 2014 :: Split Time Span Into Multiple Records?

Oct 30, 2015

I have a table that includes the fields below:

ID - INT
Machine - TINYINT
StartTime - DATETIME
EndTime - DATETIME

What I am trying to do is figure out how much time is used for production per day. The problem is, there are production runs that run over midnight and possible multiple days without ending. For example, if I have the following data:

ID - 1
Machine - 2
StartTime - 2015-09-01 22:00:00.000
EndTime - 2015-09-03 22:00:00.000

So what I am looking for is taking the above record and turning it into 3 records like below:

ID Machine StartTime EndTime
1 2 2015-09-01 22:00:00.000 2015-09-01 23:59:59.999
1 2 2015-09-02 00:00:00.000 2015-09-02 23:59:59.999
1 2 2015-09-03 00:00:00.000 2015-09-03 22:00:00.000

View 5 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

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

Need Help W/ SELECT From One Table, One Field, Multiple Unique Records

Apr 22, 2006

I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:

Anna Smith
John Doe

etc. I did not see IDs listed for any of the names in the table when I looked.

There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.

Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:

strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"

This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.

Is there a way to modify this statement to pull only the records that I want?

Thanks for any help you can give.

AJ

View 3 Replies View Related

SQL Server 2012 :: Using Select Query To Get 2014-08-09 11:13:03 From Original Date Records?

Aug 18, 2014

I have date field in table and data contain 2014-08-09 11:13:03.340

when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03

Why the 03 is trimming in smalldatefield.

how do I use select query to get 2014-08-09 11:13:03 from original Date records

View 1 Replies View Related

SQL Server Admin 2014 :: Few Record Loss In Table Primary Key Where Same Records Exists In Foreign Key Table?

Jun 21, 2015

Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.

View 3 Replies View Related

Select From Multiple Tables, Insert In Temp Table

Feb 18, 2004

What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?


Any ideas?

Thanks in advance.

View 6 Replies View Related

SQL Server 2014 :: Error On Unique Index - Duplicate Key When Insert / Select

Aug 4, 2014

I have an issue where I am getting an error on an unique index.

I know why I am getting the error but not sure how to get around it.

The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.

But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.

It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.

How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.

CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')

[Code] .....

View 1 Replies View Related

SQL Server Admin 2014 :: Insert A Row To A Table Based On Table Values?

Jun 10, 2015

Here is my table:

My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?

View 0 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 :: Bulk Insert Data Into Table

Jul 29, 2014

I need to load the following data into a SQL table. This is how the vendor is able to provide it to us.

CRCorp Daily Report,,,,,,
,,,,,,
Facility,Location,Purchase Order #,Vendor,Inventory #,Date Ordered,Extended Cost
09-Mowtown 495 CRST,09-402A Women's Imaging,327937,"BARD PERIPHERAL VASCULAR, INC.",113989,7/25/2014,650
09-Mowtown 495 CRST,09-402A Women's Imaging,327936,"WB MASON CO., INC.",112664,7/25/2014,8.64
01-Mowtown 499 CRST,01-302B Oncology,327947,McKesson General Medical,n/a,7/25/2014,129.02

[Code] ....

I have attempted to bulk insert it into this table with no luck.

CREATE TABLE POMaster
(Facility VARCHAR(75),
Location VARCHAR(75),
PONum INT,
VendorNm INT,
INVENTORYNUM VARCHAR(25),
orderDte DATE,
ExtendedPrice NUMERIC(10,2)
)
GO

It does not like the double quotes. How to make this format work? Do I need a format file?

View 2 Replies View Related

SQL Server 2014 :: Table Locking While Insert Statement

Sep 18, 2015

I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.

View 5 Replies View Related

SQL Server 2014 :: Find Multiple Entries In Two Fields One Table?

Sep 12, 2014

I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:

SELECT isbn, title, publishername
FROM book
WHERE title in (SELECT title
FROM book
GROUP BY title
HAVING count(title)>2 or count(publishername)>2)
order by title;

This is a snap shot of the output:

ISBN Title Publishername
0-1311804-3-6 C Prentice Hall
* 0-0788132-1-2 C OSBORNE MCGRAW-HILL
* 0-0788153-8-X C OSBORNE MCGRAW-HILL
* 0-9435183-3-4 C Database Development MIS
* 1-5582806-2-6 C Database Development MIS

[Code] ....

What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?

View 3 Replies View Related

SQL Server 2014 :: Large Table With Multiple Search Columns

Jun 23, 2015

I've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...

In this case the best solution is to create an index on each column individually or at least the most used?

View 7 Replies View Related

SQL Server 2014 :: Insert 500 Million Rows Into In-memory Table

Jul 29, 2014

I am doing a performance testing for In-memory option is sql server 2014. As a part I want to insert 500 million rows of records into a in-memory enabled test table I have created.

I need a sample script to insert 500 million records into a table ....

View 9 Replies View Related

SQL Server Admin 2014 :: Master Table With Insert Trigger

Feb 4, 2015

I have a master table with after insert trigger on it.. When record is inserted into master table, the trigger fires and is captured in the backoffice table. In case the trigger fails, my record is neither in the master table nor in the back office table..

Is there anyway to capture the record either in the master table or in a separate table.

View 6 Replies View Related

SQL Server 2014 :: Select Data From Table With A Particular Column Priority

Aug 1, 2015

DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)

[Code]....

i want to select the data priority wise
the o/p should look like below

first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'

View 1 Replies View Related

SQL Server 2014 :: Insert Stored Procedure Output To Table And Add Datetimestamp

Jun 22, 2015

I have a need to insert stored procedure output a table and in addition to that add a datetimestamp column.. For example, Below is the process to get sp_who output into Table_Test table. But I want to add one additional column in Table_test table with datetimestamp when the procedure was executed.

insert into Table_Test execute sp_who

View 2 Replies View Related

SQL Server 2014 :: How To Update Values Based On Column Into Multiple Columns In Another Table

Jul 31, 2015

I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.

CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE

One way to achieve this is to write two update statements. After update, the output you see is my desired output

UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output

my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.

View 5 Replies View Related

SQL Server Admin 2014 :: Crystal Report Cannot Select Into Temp Table

Jul 30, 2015

I know select * into ##tmp1 should work but I get a database connection error.

View 9 Replies View Related

Integration Services :: Loading Data From Multiple Excel Sheets To Server 2014 Table

Aug 5, 2015

I have one excel sheet contains 50 sub sheets with different names on it. Is it possible can i load all sheets into SQL using SSIS?

View 2 Replies View Related

SQL Server 2014 :: Trigger On A View If Any Insert / Update Occurs On Base Table Level

Apr 21, 2015

I have a situation where I have Table A, Table B.

View C is created by joining table A and table B.

I have written a instead of trigger D on view C.

I do not insert/update/delete on the view directly.

For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.

And I am unable to see this trigger work on the view if any insert/update occurs on base table level.

Trigger is working only if any operation is done directly on the view.

View 2 Replies View Related

SQL Server 2012 :: Parse Two Delimited Table Columns Into Multiple Records

Oct 22, 2014

I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.

CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)

[Code] .....

The results I am looking to achieve are:

PLanIDGroupIDSubGroupIDAddOnCodeExternalCode
101000005LAA1M231_1
101000005OCA2M231_2
101000005UCA3M231_3
101000005XCA4M231_4
201000005LAA1M231_1
201000005OCA2M231_2
201000005UCA3M231_3
201000005XCA4M231_4

Is there an SQL statement that can be used to accomplish this?

View 1 Replies View Related

SQL Server 2012 :: Find Subset Of Records From A Table - Multiple Except Statements

May 13, 2015

I created a CTE which finds a subset of records from a table

I then ran a SELECT statement against the same table as

SELECT * FROM TABLE
EXCEPT (SELECT * FROM CTE)

Is it possible to add another EXCEPT statement after the CTE EXCEPT statement to cover a condition not incorporated in the CTE definition?

View 9 Replies View Related







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