Pasting Multiple Rows
Jul 26, 2007
Hi all,
I'm trying to import around 4000 rows of data into SQL from an Excel spreadsheet. I thought I might be able to simply copy and paste into SQL Server (Tables > Right click on table > Open Table), but when I try this only one value is pasted in. There's only around eight columns and I've been trying to do each seperately but to no avail.
Can anyone suggest a means importing this data (as a whole, or in , either through some simple copy and paste function, or through some code?
TIA,
SamuelT
View 2 Replies
ADVERTISEMENT
May 7, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
ID Pat_ID
1 A
2 A
3 A
4 A
5 A
6 B
7 B
8 B
9 C
10 D
11 D
I want output for the above as:
Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null
Please help me. Thanks!
View 6 Replies
View Related
Aug 22, 2007
Hi
We have some reports that are published as PDF files. The layout of the report is fairly free, using lists within lists.
Some of our users want to cut and paste some of the data. When they do so, the order and layout of the fields does not resemble the layout of the report. Any way around this.
Note that when the report is published as mhtml or excel, cutting and pasting from these formats is much better.
thanks
Peter
View 1 Replies
View Related
May 20, 2008
I'm using Microsoft SQL 2005.
I am attempting to store text in a column defined as varchar(2048). The text contains line feed and carriage return characters. After examining the database column the text is truncated after the first set of line feed and carriage return characters.
Is there a way to force SQL Server to store these characters?
Much Thanks, Robert
View 4 Replies
View Related
Dec 25, 2005
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.
View 1 Replies
View Related
Nov 28, 2007
How do I copy a column of data from excel and paste into an sql table?
View 1 Replies
View Related
Mar 13, 2014
Previous to 2012, grid results from SSMS queries pasted into Excel beautifully and were easy to format.in 2008, The results from this query pasted into a spreadsheet with the query contained in a single Excel cell:
(sample - not the whole script)
select top 300
Avg_CPU_Time
,Total_Physical_Reads
,convert(datetime,Last_execution_time) as Timestamp
,Stored_Procedure
,Query_text
from dbadmin.dbo.History_CPU_IO_ByQueryAndSP
This was wonderful. I could deliver to developers wonderfully tidy reports on query resource usage with timestamps along with which stored procedure the queries came from.
Can't do that in SSMS 2012. Try it. It's a disaster. The Query_text, when pasted, spreads across multiple Excel columns, including the ones designated for other data. The result is totally unreadable.how to make the query_text stay in it's own cell? I've tried converting query_text to varchar and ntext. Same results.
View 9 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Aug 17, 2015
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies
View Related
Aug 5, 2014
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
View 1 Replies
View Related
Apr 23, 2008
Hello All,
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
Any help is much appreciated!
View 3 Replies
View Related
Jun 23, 2015
I have a complex model with lots of tables and measures. It's got to the point where pasting measures in the measure grid is getting very slow with a few seconds flashing "finishing". The calculation mode is set to manual and I'm not switching between the workbook and the PP window. How to speed this up?
View 2 Replies
View Related
Mar 2, 2015
I have the following results:
ID, Office1
1, Testing
1, Hello World
What i am trying to do is to get this result:
ID, Office1, Office2
1, Testing, Hello World
how i can accomplish this task.
View 3 Replies
View Related
Sep 3, 2014
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies
View Related
Feb 12, 2015
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I need results for each parent like this
ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]
View 1 Replies
View Related
Aug 14, 2015
I have the following database structure
Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4
How can I show Each Depth column as seperate row
AAA 1
AAA 2
AAA 1 as follows
View 3 Replies
View Related
Sep 26, 2007
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
View 1 Replies
View Related
Jan 10, 2008
I can't paste text (directly into table row via enterprise manager) into a varchar (5000) field, truncates after about 960 char. Length of string trying to paste is about 1400 characters including spaces. No special characters (one apostrophe). Error happens intermittently.
SQL server v2000
View 4 Replies
View Related
Oct 15, 2009
is there a way to update multiple rows in one update query in tsql? what I wanted to do is for example I got a table containing
code : desc
1 : a
2 : b
3 : c
4 : d
1 : e
3 : f
I wanted to update it to
code : desc
1 : x
2 : b
3 : y
4 : d
1 : x
3 : y
how to do it?
View 5 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
May 4, 2001
Hi, Gurus,
I am trying to populate a table with repeating groups in multiple columns by using information from two other tables. The sample tables and records are like:
Table A
CSID
1
2
3
4
Table B
CP_IDCO_CODE
12C1
12C2
12C3
12C4
13C5
13C6
13C7
13C8
14C9
14C10
14C11
14C12
Table C (The empty table I want to populate like the following)
CSID CP_ID_1 CO_CODE_1 CP_ID_2 CO_CODE_2 CP_ID_3 CO_CODE_3
112C1 13 C514 C9
212C2 13 C614 C10
312C3 13 C714 C11
412C4 13 C814 C12
What is the best way to do it? Thanks in advance.
Sam
View 2 Replies
View Related
Sep 7, 2007
Hi,
I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work.
Heres what i want to do:
This is the how the table is:
EmpID Designation
678
CFA
679
CFA
680
CFA
685
CFP
685
CIMA
685
IMCA
I want it to display as:
EmpID Designation1 Designation2 Designation3
678 CFA
679 CFA
680 CFA
685 CFP CIMA IMCA
could anyone provide some help on this?
Thanks
View 1 Replies
View Related
May 13, 2008
I have a Oracle table with columns like:
zone, plot, collection_start_date, collection_end_date, submit_date etc.
Each zone has multiple plots. Data collection is monthly but the start date can vary, depending on some factors.
For each zone and plot, there are multiple entries for the same month.
Here's what I need to do -
1) I need to comeup with one row for each zone, plot, month combination.
2) THe row should have the zone, plot, TO_CHAR(collection_start_date, 'MM') MONTH, TO_CHAR(collection_start_date, 'YYYY') YEAR, submit date, The difference between the submit_date and collection_start_date
The problem is multiple rows for the same month for a zone, plot and the collection_start_date varies for each row and i need to get the row based on this varied date field.
Any idea?
Thanks.
View 6 Replies
View Related
Jan 17, 2007
Hello I am wondering if this is possible.I have a two tables one contains Plan information and another thatcontains product information about the planex:Plan tablePlanID Plan_name1 a2 bProduct TableProductID PlanID Comments1 1 com12 1 com23 1 com3What I am looking to do if possible would be the followingPlan Product1 Comments1 Product2 Comments21 1 com1 2 com2I am wondering down what path I should explore cause I am new to this.I am using sql 2005
View 2 Replies
View Related
Mar 11, 2008
i have a table
View 1 Replies
View Related
Mar 17, 2008
How would I update a table where id = a list of ids?Do I need to parse the string idList? I am being passed a comma seperated string of int values from a flex application.example: 1,4,7,8 Any help much appreciatedBarry 1 [WebMethod]
2 public int updateFirstName(String toUpdate, String idList)
3 {
4 SqlConnection con = new SqlConnection(connString);
5
6 try
7 {
8 con.Open();
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = con;
11 cmd.CommandText = "UPDATE tb_staff SET firstName = @firstName, WHERE id = @listOfIDs";
12
13
14
15 SqlParameter firstName = new SqlParameter("@firstName", toUpdate);
16 SqlParameter listOfIDs= new SqlParameter("@listOfIDs", idList);
17
18
19
20 cmd.Parameters.Add(firstName);
21 cmd.Parameters.Add(listOfIDs);
22
23 int i = cmd.ExecuteNonQuery();
24 con.Close();
25 return 1;
26
27 }
28 catch
29 {
30 return 0;
31 }
32 }
33
34
View 3 Replies
View Related
May 13, 2004
Any idea how to fix my code. I am getting this error message below....
Server: Msg 512, Level 16, State 1, Procedure TrigRetReqRecIDP1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Set @intRowCount = (select count(*) from RequestRecords where REID = @REID)
While (select @REID from RequestRecords) != @intRowCount
Begin
select @intRRID = (select REID from RequestRecords where REID=@REID and RRStatus = 'PE')
Exec TrigAssignImpTaskNewP1 @intRRID, @REID
End
View 2 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
May 5, 2003
I inherited a database with Street, CrossStreet1 and CrossStreet2. I wish to read the row once and output 3 rows.
I know I can run 3 separate query's but why read through 1 million rows 3 times, if you can read through it once.
I can do this in SAS using the output statement.
Hoping someone knows how to do this in SQL.
:confused:
View 6 Replies
View Related
Feb 13, 2008
Hi, I have the following code:
SELECT dbo.t_admin.aUsername, dbo.t_admin.aPassword, dbo.t_adminSection.asSID
FROM dbo.t_admin INNER JOIN
dbo.t_adminSection ON dbo.t_admin.aID = dbo.t_adminSection.asAID
It brings back
james * pass * 1
james * pass * 2
james * pass * 3
However, I want it to bring back
james * pass * 1,2,3
How do I go about doing that?
Thanks
View 1 Replies
View Related
Feb 13, 2007
I have data that looks like this:
ID Value
1 Descr1
1 Descr2
1 Descr3
where Descr could range from 1 to 100 for each ID
The result set I need is:
Descr1,Descr2,Desc3...etc.
Does someone have a query to do this?
Thank you
View 8 Replies
View Related
