Hi,
I need to create a table which has the columns from the select statement result.
I tried in this way
drop table j9a
SELECT er.* into j9a
FROM caCase c
LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID
Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID
Left Join exexposure ee ON ee.cacaseID=c.caCaseID
LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID
LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID
WHERE c.caCallTypeID =0
AND c.Startdate between '1/1/2006' and '12/1/2006'
AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10)
AND pp.paSpeciesID=1
AND c.PublicID_adOrganization_secondary is null
set @21=(select count(*) from j9a whereRoute_Ingestion=1)
set @22=(select count(*) from j9a whereRoute_Inhalation=1)
set @23=(select count(*) from j9a whereRoute_Aspiration=1)
set @24=(select count(*) from j9a whereRoute_Ocular=1)
set @25=(select count(*) from j9a whereRoute_Dermal=1)
set @26=(select count(*) from j9a whereRoute_Bite=1)
set @27=(select count(*) from j9a whereRoute_Parenteral=1)
set @28=(select count(*) from j9a whereRoute_Otic=1)
set @29=(select count(*) from j9a whereRoute_Rectal=1)
set @30=(select count(*) from j9a whereRoute_Vaginal=1)
set @31=(select count(*) from j9a whereRoute_Other=1)
set @32=(select count(*) from j9a whereRoute_Unknown=1)
The exRoute result is like this
70Ingestion
71Inhalation/nasal
72Aspiration (with ingestion)
73Ocular
74Dermal
75Bite/sting
76Parenteral
77Other
78Unknown
524Otic
525Rectal
526Vaginal
The above giving the errors Msg 207, Level 16, State 1, Line 19
Invalid column name 'Route_Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Route_Inhalation'
How to create table j9a.j9a has the columns from select
Thanks in advance
Dear All I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
What are the differences between "select * into" and "Create TABLE". I know that select * into creates the table in the background and populates the data from the old table. What I want to know is 1.Are there any benefits in doing a certain way? 2. Are there any risks?
We have a "select * into" query in our code that created problems INTERMITTENTLY and we are replacing it with "create table" command. We are not sure if that is going to fix the problem.
and I need to find a sequence of values in column "StringVal", for example: A B.I look for a suitable SELECT, that returns (in this case) following result:
HiWe are migrating from Access to SQL server. The code is in VB and usesDAO3.6. I have successfully made the connection to the databases onthe SQL server and done operations with recordsets. I have also used aSELECT INTO command to create new tables. However CREATE TABLE andALTER TABLE commands do not work.Any ideas?The DBA says we have owner privileges on the databases and thereforeshould be able to do what we like.Many thanksSi
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?
Example:
I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.
I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.
Hi, I need to create a table which has the columns from the select statement result. I tried in this way drop table j9a SELECT er.* into j9a FROM caCase c LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID Left Join exexposure ee ON ee.cacaseID=c.caCaseID LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID WHERE c.caCallTypeID =0 AND c.Startdate between '1/1/2006' and '12/1/2006' AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10) AND pp.paSpeciesID=1 AND c.PublicID_adOrganization_secondary is null
set @21=(select count(*) from j9a whereIngestion=1) set @22=(select count(*) from j9a whereInhalation/nasal=1) set @23=(select count(*) from j9a whereAspiration=1) set @24=(select count(*) from j9a whereOcular=1) set @25=(select count(*) from j9a whereDermal=1) set @26=(select count(*) from j9a whereBite=1) set @27=(select count(*) from j9a whereParenteral=1) set @28=(select count(*) from j9a whereOtic=1) set @29=(select count(*) from j9a whereRectal=1) set @30=(select count(*) from j9a whereVaginal=1) set @31=(select count(*) from j9a whereOther=1) set @32=(select count(*) from j9a whereUnknown=1)
Create table table9(Route varchar(30),Fatal int) insert into table9 values ('Route_Ingestion',@1,@21) insert into table9 values ('Route_Inhalation',@2,@22) insert into table9 values ('Route_Aspiration',@3,@23) insert into table9 values ('Route_Ocular',@4,@24) insert into table9 values ('Route_Dermal',@5,@25) insert into table9 values ('Route_Bite',@6,@26) insert into table9 values ('Route_Parenteral',@7,@27) insert into table9 values ('Route_Otic',@8,@28) insert into table9 values ('Route_Rectal',@9,@29) insert into table9 values ('Route_Vaginal',@10,@30) insert into table9 values ('Route_Other',@11,@31) insert into table9 values ('Route_Unknown',@12,@32)
select * from table9
The exRoute result is like this 70 Ingestion 71 Inhalation 72 Aspiration 73 Ocular 74 Dermal 75 Bite/sting 76 Parenteral 77 Other 78 Unknown 524 Otic 525 Rectal 526 Vaginal
The above giving the errors Msg 207, Level 16, State 1, Line 19 Invalid column name 'Ingestion'. Msg 207, Level 16, State 1, Line 20 Invalid column name 'Inhalation'.
Hi,I want to create a temporary table and store the logdetails froma.logdetail column.select a.logdetail , b.shmacnocase when b.shmacno is null thenselectcast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) aschar(2)) as ShmCoy,cast(substring(a.logdetail,charindex('·',a.logdeta il)+1,charindex('·',a.logdetail,charindex('·',a.lo gdetail)+1)-(charindex('·',a.logdetail)+1))as char(10)) as ShmAcnointo ##tblabcendfrom shractivitylog aleft outer joinshrsharemaster bon a.logkey = b.shmrecidThis statement giving me syntax error. Please help me..Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'case'.Server: Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'end'.sample data in a.logdetailBR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00······0234578······· ····· ··········UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····Regards.
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance, Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
I have the two following tables:tblProject:ProjectID500150026001600270017002tblProject_type:ProjectTypeID ProjectIDFrom ProjectIDToA 5000 5999A 7000 7999I need to create a SELECT statement which shows all records fromtblProjects WHERE ProjectTypeID = A. In this case every project except6001 and 6002.Unfortunately I can't have the ProjectTypeID in tblProject because eachproject can belong to many project types. I know it sounds crazy butthat's the way my customers company is organized.I'm very grateful for help in the right direction!Regards,S
Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish Even after this it is not working, So provided select on schema also. used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
I am trying to create a new column 'COL_4' and, in the same step, create a case statement off of the new col. I know that the code below will not execute. I realize that I could get ride of COL_4 in my code below and concatonate but I need to keep Col_4 in the output.
SELECT COL_1 ,COL_2 ,COL_3 ,COL_4 = COL_1 + COL_2 ,COL_5 = CASE WHEN COL_1 THEN 'SOMETHING' END FROM TABLE_1 ;
I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?
