Converting Columns In An INSERT (was SQL Query)
Jan 17, 2005
I'm trying to create an Insert query and I'm having difficulty in 2 areas:
First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.
INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP
Second, I've tried the following"conversions" in the SELECT statement, to no avail:
CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)
Any pointers much appreciated...
View 2 Replies
ADVERTISEMENT
Jun 11, 2015
Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
[Code] ....
The results from the above are as follows:
columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL
My desired results with desired headers are as follows:
PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30
View 3 Replies
View Related
Sep 20, 2004
Hi
How can one convert rows into columns (or all rows in one column as a single row, except each row in its own column), either by using a temperary table or just in a select statement?
View 2 Replies
View Related
Jun 22, 2008
hi,
i have the 4rows in one table those are book names...
book1
book2
book3
book4
i have the other table..consisting of usenames
in the output i need like this
username1 book1 book2 book3 book4
username2 book1 book2 book3 book4
View 1 Replies
View Related
Sep 25, 2007
Hi,
I need to flip around the way data is presented in a table.
I've got columns called 'datafield', and 'datavalue' in one table and I want to move the datavalue into a second table where the column name is the same as the datafield.
Below is the sql to create the first table and populate it, I've also included the SQL to create the second table. I want to move the data from teststart to testfinish. There may also be columns in test finish that aren't included in the data of teststart.
create TABLE teststart
(
ticker varchar(255) NOT NULL,
datafield varchar(255) NOT NULL,
datavalue varchar(255) NOT NULL
)
INSERT INTO teststart
select 'ZTX LN Equity', 'EXCH_CODE', 'LN' union
select 'ZTX LN Equity', 'ID_BB_COMPANY', '112367' union
select 'ZTX LN Equity', 'ID_BB_SECURITY', '1000' union
select 'ZTX LN Equity', 'ID_ISIN', 'GB0008812496' union
select 'ZTX LN Equity', 'ID_MIC_LOCAL_EXCH', 'XLON' union
select 'ZTX LN Equity', 'ID_SEDOL1', '0881249'
CREATE TABLE testfinish
(
ticker varchar(255) NOT NULL,
EXCH_CODE varchar(255),
ID_BB_COMPANY varchar(255),
ID_BB_SECURITY varchar(255),
ID_ISIN varchar(255),
ID_MIC_LOCAL_EXCH varchar(255),
ID_SEDOL1 varchar(255),
Another varchar(255)
)
I'd be grateful for any pointers, thanks.
Sean
View 2 Replies
View Related
Jul 20, 2005
have a urgent requirement. Please somebody help me.I have a table departinfo with following recordsbegin_time end_time Name Pieces10:00 10:15 PopCorn 310:15 10:30 Biscuits 510:30 10:45 PopCorn 2Now I need to run a sql query and the output should be as below :begin_time end_time PopCorn Biscuits10:00 10:15 3 010:15 10:30 0 510:30 10:45 2 0Please note that only one column i.e. PopCorn is created in spite ofhaving multiple records in the table. Similarly the records are notfixed. I mean thatthere can be n number of records and the columns should be uniquelycreated.Can somebody help me outPLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
View 7 Replies
View Related
Jul 20, 2005
I have a SP that returns the information I want but it returns it in 2separate queries.Example:Query 1Name, Number, ClassRow 1- Mike Phillips, 154AA, AAandQuery 2Time, ManualRow 1 -12:45:22,0Row 2 -13:04:56,0What I want it to look like is:Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0Here is the query I'm using:DECLARE Class cursorFOR--here we get a list of distinct classes to pass to the Class cursorselect Distinct(class_ID) from kt_member_lapwhere Race_ID = 83order by Class_ID;OPEN Class;DECLARE @RaceID intDECLARE@RacerCount intDECLARE @ClassID char(50)DECLARE @classcountDECLARE @Racer char(50)DECLARE @i intSET @RaceID = 83--this is where we loop through the classesFETCH NEXT FROM Class INTO @ClassIDWHILE (@@FETCH_STATUS <> -1)BEGINIF (@@FETCH_STATUS <> -2)DECLARE Lap cursorFORSelect DISTINCT(Member_ID) from KT_MEMBER_LAPWhere class_ID = @classID and race_id = @RaceIDOPEN Lap;--this is to begin counting from the first lapSET @i = 1;FETCH NEXT FROM Lap INTO @RacerWHILE (@@FETCH_STATUS <> -1)BEGINIF (@@FETCH_STATUS <> -2)SELECT KT_MEMBER.MEMBER_FNAME + ' ' +KT_MEMBER.MEMBER_LNAME As MemberName,CONVERT(nvarchar(3),KT_MEMBER_CLASS.MEMBER_CLASS_BIKE_NUM) + KT_CLASS.CLASS_LETTER AsBikeNumber,KT_CLASS.CLASS_DESCFROM KT_CLASS INNER JOINKT_MEMBER_CLASS ON KT_CLASS.CLASS_ID =KT_MEMBER_CLASS.CLASS_ID INNER JOINKT_MEMBER ON KT_MEMBER_CLASS.MEMBER_ID =KT_MEMBER.MEMBER_IDWHERE KT_MEMBER.MEMBER_ID = @Racer and KT_CLASS.CLASS_ID =@ClassID--SELECT @Racer, @ClassIDSelect MEMBER_LAP_TIME_REAL, member_lap_manual from KT_MEMBER_LAPWhere Member_ID = @Racer and class_ID = @classID and race_id =@RaceIDORDER BY MEMBER_LAP_TIME_REAL--here I count up for the next lapSET @i = @i + 1;FETCH NEXT FROM Lap INTO @RacerENDCLOSE Lap;DEALLOCATE Lap;FETCH NEXT FROM Class INTO @ClassIDENDCLOSE Class;DEALLOCATE Class;Any help would be appreciated.
View 1 Replies
View Related
Mar 10, 2008
Hi,
I have written a query that I need to get data which are to be displayed in the SQL 2005 reports.
I am getting the result as
Data Count
A 2
B 4
C 5
I need to get the data in a single row and the data in the 'Data' column should become like a column, like
A B C
2 4 5
Can anyone help me with this query?
View 5 Replies
View Related
Oct 11, 2013
I have a table called DISTRIBUTION_SL in which there will multiple records for each request as shown below in the data.I would like to convert the recepient email into columns and then join with other tables so that the entire request information is seen in one row rathar than multiple rows
Table
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
CREATE TABLE [dbo].[DISTRIBUTION_SL](
[requestid] [numeric](18, 0) NOT NULL,
[rptdis] [char](1) NOT NULL,
[recipnm] [varchar](50) NULL,
[emailadr] [varchar](75) NULL,
[code]....
View 6 Replies
View Related
Jul 20, 2005
I do a:SELECT * FROM xxxAnd Get:Date Place SumA M 1A O 3 A P 2B O 5B M 4B P 2And I want it to look like:Date M O PA 1 3 2B 4 5 2Can you think of an EASY way to do this?I can do it with a cursor that constructs a SQL statement, which I EXEC, but the 8000 character limit may prove to be a limiting factor.sp_execsql is somewhat messy for the nature of this issue.Any input is appreciated.Thanks in advance.
View 2 Replies
View Related
Mar 28, 2008
SELECT
h1.ftraccode,
CASE WHEN FTRAADDRED='A' then h1.ftrascode end as 'From Sec',
CASE WHEN FTRAADDRED='r' then h1.ftrascode end as 'To Sec',
case when ftraaddred ='A' then h1.ftradesc end as 'From Description',
case when ftraaddred ='r' then h1.ftradesc end as 'to Description'
from bHISfile h1
where h1.ftradesc like 'sw%'
order by 1
----------------------------------------------------------------
clintcode |from_sec | to_sec| from_desc | to_desc
---------------------------------------------------------------
ABADJ16421 |NULL | MMTEI |NULL |SWITCH TO OAPIF
ABADJ16421 |OAPIF | NULL |SWITCH FROM MMTEI |NULL
2(row)
Expected output like this
----------------------------------------------------------------
clintcode |from_sec | to_sec| from_desc | to_desc
---------------------------------------------------------------
ABADJ16421 |OAPIF | MMTEI |SWITCH FROM MMTEI |SWITCH TO OAPIF
1(row)
View 3 Replies
View Related
Oct 12, 2007
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
View 5 Replies
View Related
Nov 11, 2007
Hi,
I'm using SSRS to generate reports. i have many columns data to be displayed.while converting the data into Excel and PDF the data, header, and footer are not displaying proper format. what are all the properties to be set for that.
Could any one help in this regard.
Thanks for your help..
View 1 Replies
View Related
Apr 24, 2008
I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF')
--insurancetype is the new column.
View 9 Replies
View Related
Sep 19, 2006
This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).Employees table:empNameJane DoeAlton BrownJohn DoeTrainingCourse table:courseNameWeldingBrain SurgeryScuba DivingResults table:empName: courseName: completeDate:Jane Doe Welding 2/2/2002Jane Doe Brain Surgery 3/7/2005Alton Brown Scuba Diving 9/23/2004Alton Brown Welding 11/4/2004John Doe Brain Surgery 6/14/2003End result of select statement: Welding Brain Surgery Scuba DivingJane Doe 2/2/2002 3/7/2005 Alton Brown 11/4/2004 9/23/2004John Doe 6/14/2003 Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.
View 8 Replies
View Related
Aug 3, 2007
Hello All,
I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.
Consider that we have Product Category, Product and its monthly sales information retrieved as follows:
CategoryID
ProductID
ProductName
Month
UnitPrice
QtySold
SalesAmount
1
1
Panel
Jan
5
10
50
1
1
Panel
Feb
5
15
75
1
1
Panel
Mar
5
20
100
1
2
Frame
Jan
10
30
300
1
2
Frame
Feb
10
25
250
1
2
Frame
Mar
10
20
200
1
3
Glass
Jan
20
10
200
1
3
Glass
Feb
20
20
400
1
3
Glass
Mar
20
30
600
I would like it to be converted into following result set:
CategoryID
ProductID
ProductName
UnitPrice
QtySold_Jan
SalesAmt_Jan
QtySold_Feb
SalesAmt_Feb
QtySold_Mar
SalesAmt_Mar
1
1
Panel
5
10
50
15
75
20
100
1
2
Frame
10
30
300
25
250
20
200
1
3
Glass
20
10
200
20
400
30
600
I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.
Thanks.
View 1 Replies
View Related
Dec 7, 2004
Hi,
I need to take a value from a textbox and insert it into a field in my database which takes decimals. My problem, no matter what I try I cannot convert the value so that the database will accept it. This all happens when the submit button is hit on my webpage. Here is the cmdSubmit_click sub code:
Dim surveyNum As Decimal = Decimal.Parse(txtSurveyNum.Text, Globalization.NumberStyles.Number)
myCmd.CommandText = "INSERT INTO survey(ID) VALUES('" & surveyNum & "')"
myCmd.Parameters.Add("surveyNum", SqlDbType.Decimal)
myCmd.Parameters("surveyNum").Value = System.Convert.ToDecimal(txtSurveyNum.Text)
myConn.Open()
Try
myCmd.ExecuteNonQuery()
lblMessage.Text = "Record successfully updated"
Catch
lblMessage.Text = "Query error: " & Err.Description
End Try
myConn.Close()
Thnx in advance, any help would be greatly appreciated.
View 1 Replies
View Related
Sep 18, 2005
Trying to convert the following SELECT statement into a INSERT statement and having trouble. No doubt this will be a piece of cake to someone. To eventually get this to a trigger stage would be nice, but for the moment I'd settle for just plain SQL. Using MS SQL 2000. The database name is reporting. The table name is CallLog. I'm trying to convert seperate date (RecvdDate) and time (RecvdTime) columns into a single DateTime column. I've scoured a lot of web pages but I'm still lost.
==============
use reporting
go
SELECT RecvdDate + RecvdTime FROM [dbo].[CallLog]
===============
Any help much appreciated.
View 7 Replies
View Related
Mar 30, 2006
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies
View Related
Sep 10, 2007
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups
Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:
How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Thanks for all your help!
View 1 Replies
View Related
Dec 28, 2007
Hi,I'm new at asp .net and am having a problem. On a linkbutton click event, I want to insert into my db a row of data which includes two parameters. Param1 is the id of the logged in user, and Param2 is <%#DataBinder.Eval(Container.DataItem, 'UserId')%> which is the username of a user given through a datalist.When I execute the query: "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)"I get the error: Conversion failed when converting from a character string to uniqueidentifier.I am trying to insert these into my table aspnet_friendship into the columns userId and buddyId which are both uniqueidentifier fields. I tested the query using the same values (@Param1, @Param1) and (@Param2, @Param2) and found out that the problem is with Param2 because when I use Param1 the insert works perfectly. The value passed by Param2 is supposed to be the id of a user which I get from the datalist which selects * from aspnet_Users, so that <%#DataBinder.Eval(Container.DataItem, 'UserId')%> should also be an Id like Param1 is. Since both params are in the form of .toString(), I don't understand why one works but the other doesn't.As you can see in the code below, both of these parameters are dimmed as strings, so I don't understand why Param2 doesn't work. Can anyone please help? Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim MyConnection As SqlConnection MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") Dim MyCommand2 As SqlCommand Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() Dim add As String add = "<%#DataBinder.Eval(Container.DataItem, 'UserId')%>".ToString() Session("username") = User.Identity.Name Dim InsertCmd As String = "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)" MyCommand2 = New SqlCommand(InsertCmd, MyConnection) MyCommand2.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand2.Parameters.AddWithValue("@Param2", add) MyCommand2.Connection.Open() MyCommand2.ExecuteNonQuery() MyCommand2.Connection.Close() End Sub Thank you.
View 3 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
Jul 20, 2005
i have a query which goes like this:-select a.col1,b.col2,c.col2from tab1 a,tab2 b,tab3 cwherea.col1 *= b.col1 anda.col2 *= c.col2 andb.col3 = c.col3how do I write this query in SQL 92?also can we replace *= with =* by interchanging the participating tables as in"a.col1 *= b.col1" is this same as "b.col1 =* a.col1"
View 2 Replies
View Related
Sep 28, 2006
Hi,
I'm just wondering what's the best approach in Data Flow to convert the following input file format:
Date, Code1, Value1, Code2, Value2
1-Jan-2006, abc1, 20.00, xyz3, 35.00
2-Jan-2006, abc1, 30.00, xyz5, 6.30
into the following output format (to be loaded into a SQL DB):
Date, Code, Value
1-Jan-2006, abc1, 20.00
1-Jan-2006, xyz3, 35.00
2-Jan-2006, abc1, 30.00
2-Jan-2006, xyz5, 6.30
I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.
View 3 Replies
View Related
Dec 19, 2007
Hi,
I have a set of csv files and a set of Format Specification files for each of the csv files. I need to convert the csv files into another format of csv files as specified in the Format Specification files. All the columns of the input csv files do not have a mapping with the columns of the output csv files. How can I achieve this using SSIS ? This is an urgent requirement. Please reply asap. Thanks.
View 1 Replies
View Related
Dec 6, 2007
Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks
View 5 Replies
View Related
Jan 23, 2015
I getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.
Create view vwMAW_KnowledgeDB AS
Select * from don0001
left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null
quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2
Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.
View 1 Replies
View Related
Oct 15, 2007
SELECT distinct whse_zone_id, bay_id
From prod_geneology WHERE whse_zone_id in ('SH','CU','SG')
START WITH unit_id = 'BL7B230811'
CONNECT BY PRIOR parent_id = unit_id
And in this case, 'BL7B230811' could be any 10 character string
Any help would be grateful.
Thanks
View 1 Replies
View Related
Apr 17, 2007
I have two rows of data:
Customer as string, Date as int
These values are predefined so I cannot use autoindex. I have to make sure that when I insert a row of data that it does not exist already int the database. there can be duplicate customers and duplicate dates but not duplicate customer/date combinations. What would the sql query be?
if (select count(1) from customers where customer = @customer and date = @date) =< 0
insert into table1(customer, date) values (@customer, @date)
end if
View 2 Replies
View Related
Mar 1, 2006
I have to insert data from about 30 tables into a single table (Users),to do so i used a cursor and a bit of dynamic sql, this should workfine if the tables have to do the select from had the same number ofcolumns, how ever they don't.I only need the first 5 columns from each the table, in the case wherethey have more than the 5 i need for my 'Users' table i get this error:'An explicit value for the identity column in table 'Users' can only bespecified when a column list is used and IDENTITY_INSERT is ON.'.Is there a way to select only the first five columns from a table, ifnot is there another solution for my problem?My Sql query is the following:DROP TABLE UsersCreate Table Users(ID INT identity PRIMARY KEY,TIPO VARCHAR(255),NOME VARCHAR(255),USERNAME VARCHAR(255),EMAIL VARCHAR(255),GROUPID VARCHAR(255))DECLARE @Table VARCHAR(255)DECLARE @Sql VARCHAR(8000)DECLARE sysCursor CURSORFORSELECT name FROM sysobjects where xtype='U'OPEN sysCursorFETCH NEXT FROM SysCursor into @Tablewhile @@FETCH_STATUS<>-1BEGIN/** INSERE VALORES NA TABELA DE TESTE*/SET @SQL = 'INSERT INTO UsersSELECT * FROM ['+ @Table +']'EXECUTE (@SQL)SET @SQL = 'UPDATE Users SET GROUPID=' + @Table +'WHERE GROUPID IS NULL'EXECUTE (@SQL)print @tableFETCH NEXT FROM SysCursor INTO @TableENDCLOSE sysCursor/** APAGA VALORES INVÁLIDOS DA TABELA DE TESTE*/DELETE FROM Users WHERE TIPO IS NULLDELETE FROM Users WHERE NOME='Nome'DEALLOCATE sysCursorI hope you can give me hand, thank you in advance.
View 8 Replies
View Related
May 14, 2008
Hi all,
I'm trying to write a database table that is ONLY accessiable to ONE login / DB user and restrict access to what that user can do with the table.
At the moment I have granted SELECT & INSERT permissions but some of the fields in the table should also be restricted, but for some reason SQL doesn't allow a deny on INSERT for columns.
It seems strange that you can deny an UPDATE permission on a column but not INSERT, so users can create new lines of junk into fields you dont want them to, but cant change that junk afterward??
Anyone have any ideas how I can prevent this user from altering these fields at all?
Thanks in advance
View 1 Replies
View Related
Jul 26, 2006
So I want to insert 40 values into a table, starting at a particular column. Like this:
INSERT INTO MyTable (1) VALUES (...my forty values...)
Pretending that 0 (zero) indexes the first column, and 1 indexes the second column, the purpose is to skip the (first) column that contained an identity value (since normally you can't insert into an identity column anyway).
The only way I currently know how to solve this problem, is to use highly verbose syntax, like this:
INSERT INTO MyTable (...my forty column names...) VALUES (...my forty values...)
But yuck, who wants to explicitly mention all forty column names, ONLY BECAUSE I'm trying to avoid inserting a value into the first column which contains the identity?
View 1 Replies
View Related
Aug 24, 2004
I am extremely new with .asp. We have the .net framework 1.1 installed on our server and the following query works in Crystal Reports (older version). We would like to have this converted to a .asp page with two inputs. One for the date range and one for the state. Can someone please tell me how to go about this. I do have downloaded as test. Visual Studio 2003.net and Web Matrix. I really am looking for (if there is any) a way to pretty quickly convert query to web pages so users can use them without a huge expense. IF someone already has something like this done. That would be great. The following query is for Visual Enterprise (Manufacturing software). Thanks
SELECT
SHIPPER."CUST_ORDER_ID", SHIPPER."SHIPPED_DATE", SHIPPER."INVOICE_ID",
SHIPPER_LINE."USER_SHIPPED_QTY", SHIPPER_LINE."SHIPPED_QTY", SHIPPER_LINE."UNIT_PRICE",
RECEIVABLE_CURR."CURRENCY_ID", RECEIVABLE_CURR."SELL_RATE",
CUST_ORDER_LINE."PART_ID", CUST_ORDER_LINE."ORDER_QTY", CUST_ORDER_LINE."USER_ORDER_QTY",
INVENTORY_TRANS."TYPE", INVENTORY_TRANS."ACT_MATERIAL_COST", INVENTORY_TRANS."ACT_LABOR_COST", INVENTORY_TRANS."ACT_BURDEN_COST", INVENTORY_TRANS."ACT_SERVICE_COST",
CUSTOMER_ORDER."CUSTOMER_ID", CUSTOMER_ORDER."SHIP_TO_ADDR_NO", CUSTOMER_ORDER."CURRENCY_ID",
PART."DESCRIPTION", PART."STOCK_UM",
CUSTOMER."STATE",
CUST_ADDRESS."ADDR_NO", CUST_ADDRESS."NAME", CUST_ADDRESS."STATE"
FROM
{ oj ((((((("VMFG"."dbo"."SHIPPER" SHIPPER INNER JOIN "VMFG"."dbo"."RECEIVABLE_CURR" RECEIVABLE_CURR ON SHIPPER."INVOICE_ID" = RECEIVABLE_CURR."INVOICE_ID") INNER JOIN "VMFG"."dbo"."SHIPPER_LINE" SHIPPER_LINE ON SHIPPER."PACKLIST_ID" = SHIPPER_LINE."PACKLIST_ID" AND SHIPPER."CUST_ORDER_ID" = SHIPPER_LINE."CUST_ORDER_ID") INNER JOIN "VMFG"."dbo"."INVENTORY_TRANS" INVENTORY_TRANS ON SHIPPER_LINE."TRANSACTION_ID" = INVENTORY_TRANS."TRANSACTION_ID") INNER JOIN "VMFG"."dbo"."CUSTOMER_ORDER" CUSTOMER_ORDER ON SHIPPER_LINE."CUST_ORDER_ID" = CUSTOMER_ORDER."ID") INNER JOIN "VMFG"."dbo"."CUST_ORDER_LINE" CUST_ORDER_LINE ON SHIPPER_LINE."CUST_ORDER_ID" = CUST_ORDER_LINE."CUST_ORDER_ID" AND SHIPPER_LINE."CUST_ORDER_LINE_NO" = CUST_ORDER_LINE."LINE_NO") INNER JOIN "VMFG"."dbo"."CUSTOMER" CUSTOMER ON CUSTOMER_ORDER."CUSTOMER_ID" = CUSTOMER."ID") INNER JOIN "VMFG"."dbo"."PART" PART ON CUST_ORDER_LINE."PART_ID" = PART."ID") LEFT OUTER JOIN "VMFG"."dbo"."CUST_ADDRESS" CUST_ADDRESS ON CUSTOMER_ORDER."CUSTOMER_ID" = CUST_ADDRESS."CUSTOMER_ID" AND CUSTOMER_ORDER."SHIP_TO_ADDR_NO" = CUST_ADDRESS."ADDR_NO"}
WHERE
SHIPPER."SHIPPED_DATE" >= {ts '2002-08-01 00:00:00.00'} AND
SHIPPER."SHIPPED_DATE" < {ts '2004-08-25 00:00:00.00'}
ORDER BY
SHIPPER."SHIPPED_DATE" ASC
View 3 Replies
View Related
