Manually Creating Membership Tables

May 26, 2007

Hi,  I have limited control on the server I use.  I can not create data bases but can add tables and edit the data base that has been created for me.  So what I would like to do is be able to use a tool such as aspnet_regsql through SQL Server Express to add the tables that Membership would automatically create.  Any help in pointing me in the right direction would be great

 

 

Thanks

howlinhuskie

View 6 Replies


ADVERTISEMENT

Creating A New SQL User For Aspnetdb Membership Db

Jan 1, 2008

Hello - I am using the generic aspnetdb membership database (aspnet_regsql.exe) which I am currently connecting to as a sysadmin user from my website (via web.config). Before going live with my first website I now want to create a sql user for the website to login with, with the minimum permissions required to run the website - the reason for this is to make the db more secure. 
I have created a new sql user and am now looking at what acces they should have. I am OK giving them the required access to the NEW views, sprocs and tables I have created to link in with the generic aspnetdb database etc... however how do I know what access they require to all the BUILT-IN tables, sprocs, views etc. Also, when I am viewing Securables it also brings up all the MASTER DB sprocs, etc.. - will the user need access to these? Should I add the new user to a database role -  which one? (currently I have not given the new user a database role) This is the first time I will have created a sql user for an asp.net website so any help greatly appreciated.
Cheers
Marco  

View 1 Replies View Related

Renaming Membership Provider Tables

Nov 15, 2007

Hi, this might be a newbie question as I don't know too much about databases.My web host only lets me have 1 database, so I am trying to rename all the tables with a prefix, then import them to the server. I'm using MsSql-2005, VWD, Management Studio Express.My error when I run the .sql script is:Msg 208, Level 16, State 1, Procedure vw_aspnet_MembershipUsers, Line 3Invalid object name 'dbo.aspnet_Membership'.Msg 208, Level 16, State 1, Procedure vw_aspnet_Users, Line 3Invalid object name 'dbo.aspnet_Users'.Msg 208, Level 16, State 1, Procedure vw_aspnet_Profiles, Line 3Invalid object name 'dbo.aspnet_Profile'....I suspect that it is because I renamed the "aspnet_Membership" table to "Company1_aspnet_Membership" (I made a database diagram, then renamed the aspnet tables in Properties). Has anyone run into this before? Does anyone know how to fix it? 

View 5 Replies View Related

Deploying A Database Witch Uses The Membership Tables

Jan 15, 2006

I have a website that uses an SQL 2005 Express database, with the added aspnet_users, aspnet_roles tables etc'  inside it.
(That is not an .mdf file, but in the databse itself)

Are there any guidelines for moving this kind of database to the deployment server, which also has SQL Express ?

Is it just a case of  detach + attach ?

View 1 Replies View Related

Accepted Practice To Query The Built-in Aspnet Membership Tables ?

Jul 17, 2007

I'm a relative newbie to ASP.NET development (web development in general) so please forgive the ignorance. ;-)
Ok, I'm using VWD to design and test my application and I'm using the standard membership provider system.  I've read in various places not to muck around with the aspnet_* tables as it can cause havoc hehe.  Therefore, I've created my own Member table to accommodate more fields that the aspnet_Users table does not have (since I don't want to mess around with that table) such as address, city, town, zip, birth date, etc.  Obviously, I have a field in Member that corresponds to the userId primary key in aspnet_Users.  Now, there are times when I need to display the username of a person's profile on a page.
SELECT aspnet_Users.UserName<br>FROM aspnet_Users, Member<br>WHERE querystringid=Member.memberId AND Member.userId = aspnet_Users.UserId
Now, this seems fine and dandy when there are few entries in the aspnet_Users table, but what if I tried that query when aspnet_Users grew to a hypothetical one million entries??  Does anyone know if those fields in aspnet_Users are properly indexed?  I can't seem to determine one way or another with VWD's database explorer.  I don't want to run the risk of a full table scan.  But if those fields aren't indexed, I don't know if I should modify that table in any way.
What to do?
TIA

View 4 Replies View Related

Using Membership UserId Guids With SqlParameters (Membership UserId Guid)

May 7, 2008

In my site, when a user registers, I need to create rows in additional tables besides aspnet_Users. So, I need to be able to pass the generated userId guid to subsequent SqlCommands. I'm having a terrible time with this. What's the correct way to set up a SqlParameter so that it will accept a guid? I keep getting this error: "Conversion failed when converting from a character string to uniqueidentifier."
I've tried creating the parameter both with and without a SqlDbType.
cmd.Parameters.AddWithValue(paramName, guid);
and
SqlParameter p = new SqlParameter(paramName);p.SqlDbType = SqlDbType.Guid;cmd.Parameters.Add(p);
and I get the same error either way.
Driving me nuts! Any help appreciated.

View 1 Replies View Related

Creating Tables

May 6, 2002

I am new at the development stages of my DBA career and I am stumped on how to approach this problem, any help would be great.

Problem: Our web developer is designing a Software survey that ranks the difficulty of tasks within a software application like MS Word. Each of these tasks have a rank tied to them.

Here are the tables that he created.
Table 1
EmpID Text
123456 A1, A2, A3


Table 2
ValueID Text Rank
A1 Create Table 1
A2 Insert Query 5

In the text field of table 1 he inserts every task or valueid that the employee is able to do.

He needs to be able to select Empid, text(from table 2), and Rank. Based on need. An example would be 'Which employees can Insert a query'. And he would need to be able to look in the text string on Table 1 and find every A2 in listed then get empid and rank. I know that it can be done with full text searching, however I guess what I want to know is if there is another way to create tables that would jus make a join necessary where the value id can be tied to the empid for each task the employee is able to do?

I hope that this makes sense!!

View 1 Replies View Related

Creating Tables

Nov 29, 2005

Chris writes "How do I create a copy of a table (not temporary).

In Oracle I do .

Create table newtable as select * from oldtable


This command copies data and column data types.


I tried entering

SELECT * INTO db2.dbo.newtable FROM db1.dbo.oldtable

in the query manager and it appears to create the new table, but it only seems to be a temporary table because when I leave query manager and check my data base it isn't there.


I have to convert a table that has about 150 columns and about 2 million rows. I rather not have to create the data types for 150 columns."

View 6 Replies View Related

Creating Tables On The Fly

Oct 29, 2005

I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 backend. I currently have a selections table in the front end file which theusers use to make selections of records. The table has two fields -- primarykey (which matches primary key of main, SQL Server table), and a booleanfield. The table is linked to the main table in a heterogeneous inner join.I'm looking to move the table to the back end, while still giving eachmachine a unique set of selections. Using one large table with machine nameas part of the primary key actually slows things down. So I'm consideringusing a series of tables, where each machine has its own table in the backend for selections. The machine name would be incorporated in the particularselections table name, and the front end link would be modified on the flywhen the database is opened to point to that machine's back end selectionstable.This would require having about 50-100 individual selections tables in theback end database. Also, if a machine doesn't have a table when the databaseis opened on that machine, then that table would be created on the fly,populated, and pointed to via the ODBC link.Anyone see any problems with this approach, specifically creating the tableon the fly and then immediately using it, as well as having that many littletables running around? Thanks for any input!Neil

View 31 Replies View Related

Creating Tables On The Fly

May 1, 2008

I'm transferring data from DB2 to SQL Server - the data gets wiped every night and reloaded. To keep up with any changes in the tables and to reduce the amount of maintenance on keep field & table definition updated, it would be nice to be able to create the tables in SQL Server in the SSIS package, based on the DB2 definition.

I've noticed it can be done in design mode when creating the OLE DB Destination (clicking on the 'New' button), but I'm not sure how I would automate this...

any suggestions?

Thx

View 4 Replies View Related

Creating Tables

Apr 10, 2006

I am trying to go through an arraylist and create some database tables for each entry in the array. what I have is

Dim ques As String
For Each ques In questions
query = "create Table " + ques + " (plantid nvarchar(100), Answer nvarchar(100))"
cmd = New SqlCeCommand(query, con)
cmd.ExecuteNonQuery()

Next

I am wanting to use the item in the arraylist as the name of the db. I am getting an error saying

There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = 1 ]



Can anyone see what I am doing wrong.

View 5 Replies View Related

Creating New Tables In Aspnetdb.mdf

Apr 27, 2007

I have made a new table called 'customer' which i wish to tie into the userId of the db. I have used a db diagram to do this (there are keys on each side of the link and userId is the FK) . When i put the membership/profile view onto the form alongside the new customer table nothing displays in the customer table when i run the app., i dont even see the titles - any ideas (i'm new to this sql malarky btw - so its probably something unbelievably straightforward). Any help appreciated.
Thanks

View 2 Replies View Related

Creating New Tables For New Users

Oct 16, 2007

hi all,
 
How can i create a new data table automatically for new users signing into the website.......what i mean here is that i have a predefined database table and i want that users signing in can have that table for them unique , so that they can fill data for themselves and that data will be visible to all just like forums  

View 1 Replies View Related

Creating A Third Table From Two Tables

Apr 7, 2008

Hi All,I will like to create a table from two tables this (explained below) way using a stored procedure. Basically i want the stored procedure to return me the third table but i dont know how to do it.Table ATopicID      Topic ----------------------------- 1               Sometopic 12               Some topic 23               Some topic 34                Some topic 4 Table BRateID     Rate     TopicID---------------------------------------1            5         12            5         13            5         24            4         25            4         36             3         5 Table CTopicID     Topic             Rate--------------------------------------------1            Some topic1        52            Some topic 2      5   3             Some topic 3      5Basically i have a table that stores topics associated with a particular subject. Those topics are stored in table A above. My users can just read the messages associated with the topic and rate that message. The rating is then stored in table B.Now i will like my users to search for topics with a particular word or sentence in table A and the topics that are returned have a rating they specify. My procedure takes the word or sentence to search in table A and and the rating which will be used in table B. I will like to construct another table that has this newly searched results and return it using a stored procedure or anything simple. I dont know how to do this because i have very little knowledge in stored procedures or sql..... Any help(Code) will be greatly appreciated... 

View 11 Replies View Related

Creating The Database Tables

Feb 18, 2004

Hi,

I need to create a table and a few fields in the SQL database programmatically in asp.net.

I know the tabes name, the server which it resides on, username/password. Would anyone have a small sample of this code?

Thanks

rad

View 9 Replies View Related

Creating A FK On Existing Tables

Sep 22, 2006

hello
I am working with an existing database and there is no Foreign key between 2 tables
how can i create a FK after , when the tables are allready full ?

product :

product_id
report_id
name

report :

report_id
dateR


i want to create a FK on product.report_id, and ON DELETE CASCADE


thank you

View 2 Replies View Related

Creating Access Tables With SQL

Feb 25, 2004

I'm currently writing a web application in Coldfusion which uses a Access 2000 db. I can create tables in SQL ok but am having problems with the Autonumber type. Any ideas?

View 8 Replies View Related

Creating Database And Tables

Feb 6, 2008

I am very new to SQL Server 2005 so please excuse my mandune queastion.

First what I accomplished was I created several tables within SQL Server 2000 using the Database and Table Wizards. I also created all the relationships the same way. Next, I had SQL Server 2000 and had the tool reverse engineer the SQL for this new database with all the relationships.

My next step was to run this newly created SQL script on my local box with SQL Server 2005 (SMSS). What I did was create the database first and then assign the users to the new database. From here, I moved to running the sql script. While it was not 100% perfect, it did create all the tables and relationships with no problems.

Now, I am trying to accomplish the same process one a development instance of SQL Server 2005, but the script fails. The data base error says the new database created does not exist. When we go back and look at the database within SMSS, it is GONE!

I am at a lost at how to replicate the database I have on my local box. Any suggestions would be greatly appreciated.

Russ

View 2 Replies View Related

Creating Tables Within My Application.

Jul 20, 2005

I am having the following problem and any help would be GREATLYappreciated:In an application I am developing, at some points we create a newtable. When I create this table on another users box, I can not accessit from my box. In sql server I am dbo, but the table created by myapplication when run on a different box has an owner of : "FCxxxx". Ihave sent permissions on this thing to public, but I am still gettingan error when I try to query this thing from my application. And I cannot get query analyzer to recognize this thing. I can see it inEnterprise manager. I would think there is a way to handle this sortof thing. If anyone out there has done anything like this I would bemuch obliged for any ideas. Thanks.Sincerely,Ed HawkesJoin Bytes!

View 1 Replies View Related

Creating Tables With SQL Scripts

Dec 19, 2006

In sql server 2003 I would create tables with generated sql scripts.
This way I could easily create duplicate dev environments.

Now in SQL 2005 Dev edition I try to run my scripts to create tables.
I create a db [MSS].
They run successfully but there is no table created.

(example script)USE [MSS]
GO
/****** Object: Table [dbo].[KataNames] Script Date: 08/17/2006 13:28:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KataNames](
[Kata] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SequenceId] [int] IDENTITY(1,1) NOT NULL,
[Description] [char](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Style] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


Any clue what else I have to do to let these scripts actually create tables or why if they run ok, where is my table ?

Thanks

Kim Allbritain

View 9 Replies View Related

Creating Multilple Tables At Once

Jul 27, 2006

I had a SQL db that i copied all the tables into excel and it generated the quereies in order to create those tables....Is there anyway i could excute all those table creations at once or would it be easier to just write an application that does that for me????

View 5 Replies View Related

Creating Link Between Two Tables

Aug 15, 2015

I have data model as below....I have created a unique constraint as below;

CREATE UNIQUE NONCLUSTERED INDEX [UNQ_StaffIDEventID] ON [dbo].[tblStaffBookings]
(
[StaffID] ASC,
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I now tried to create a link between two tables tblStaffBookings and tblEventStaffDisciplinary by dragging from tblEventStaffDisciplinary.StaffID to tblStaff Bookings.StaffID and it gives me error as below;What do I need to do to link tblEventStaffDisciplinary.StaffID to tblStaffBookings.StaffID?

View 15 Replies View Related

Creating Multiple Tables?

Jul 31, 2006

Hello,I need to create around 1500 similar tables.Does anyone know how to create them all at once instead of one-by-one?thanks

View 8 Replies View Related

Creating Triggers On System Tables!!

Feb 27, 2002

Does anyone know if it's possible to create a trigger on the sysdatabases table in the master database? I keep getting permission denied which I'm not sure is right.

View 2 Replies View Related

Creating Triggers On System Tables

Mar 1, 2002

Hi,

Does anyone know of a way I can create a trigger on a system table (say sysdatabases in master). I know this is not supported but presumably there's a way it can be done by referencing it's equivilant in Information_schema somewhere.

I'm trying to write a script that will automatically set up a backup schedule for a database that has just been created. I was hoping the trigger would query the sysdatabases table for new database name entries, log necessary info in an audit table and then call a backup script to set up the schedule. Any ideas??

Thanks

Rob

View 1 Replies View Related

Dynamically Altering/creating Tables

Jul 22, 1998

Folks,
Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL
statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?!
Something like
alter table add
column+@i
The alter table statement is within a Cursor and ideally @i should increment each time and alter
the table to put in a new column.
If I went 3 times thru the loop, I should have
column1
column2
column3

added to the new table.

Any pointers would be greatly appreciated. Thanks much in advance.

View 1 Replies View Related

Creating Temp Tables During Login

Nov 17, 2004

Frenz,

I need to create tables that are session specific. i.e When I login to a database i have to create a Table that can be accessed across all the procedures and triggers. When I log out the Tables should be droped. I understand that Local Temporary (#) tables can be created in annonymus block, but I need the tables to be created during login.

Your response is highly appreciated.

-Cheeku

View 2 Replies View Related

Creating Table From 3 Different Individual Tables

Jan 28, 2013

In SQL Server, I need to create a table from 3 different individual tables. I am new to the SQL Scene, so i want to know the best way to go about this. Each table has different fields, so that is making it difficult (at least for me). How about creating 1 table, that is composed of 3 other tables?

I know this is bad practice, but our District Manager wants to see production for his 'team' and whoever set this up back in the beginning gave each individual employee a different table, with custom fields (Why?!?!?!?!) so now I am trying to merge those 3 into 1, but want to get some insight on the best way to do this since there are different fields in each table and the merged (or master) table needs to have all of those fields.

View 11 Replies View Related

SQL Server 2008 :: Creating Tables From ERD

Mar 3, 2015

I need to write create table statements for the er diagram that I attached. I am new to sql and I have trouble integrating foreign keys with these bigger er diagrams.

These are the tables I need to create:
Create Table Author(...)
Create Table Writes(...)
Create Table Book(...)
Create Table Copy(...)
Create Table Loan(...)
Create Table Customer(...)

View 1 Replies View Related

Creating A View By Using Multi Tables

Jun 12, 2007

Here I need to create a view by using following criteria, there is 3 tables which are Tbl.adminCategory, tbl_adminuser, tbl_outbox respectively. I am working on 2000SQL server

I am treing to create view as following but getting some error.

SELECT tbl_adminuser.adminUserName, tbl_AdminCategory.Name,
COUNT(tbl_outbox.msgUserID),
FROM tbl_adminuser INNER JOIN
tbl_AdminCategory ON
tbl_adminuser.adminUserID = tbl_AdminCategory.CatID INNER JOIN
tbl_outbox ON tbl_AdminCategory.CatID = tbl_outbox.msgUserID
AND tbl_outbox.msgUserID <> 0
GROUP BY tbl_outbox.msgUserID
But I am getting error pls correct the view,

thanks to all

View 4 Replies View Related

Creating Tables Via Management Studio

Feb 3, 2008

I am hoping someone could lead me in the right direction on how to figure out why my version of SQL Server 2005 Standard Edition is not working correctly. Here is my issue.

1. I've created two users: normal and superuser and associated the user to the new database. (I don't see any problem here just wanted to state two new users were created.)
2. I've created a new database (TestDB) as sa using the default seetings.
3 Next I want to create new tables in the new database. I've right-clicked on tables directory under the new database for creating a new table inside the Object Explorer Window. I get the below error when I do



Object reference not set to an instance of an object. (SQLEditors)


------------------------------

Program Location:



at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)


at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)

at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)


I was thinking something was wrong with the database so I ran sp_helpdb <database_name> and I don't get an error.

Thank for taking the time to read my post. I appreciated your time and any suggestion you could offer.


Russ

View 1 Replies View Related

Creating View Help With Temporary Tables

Mar 11, 2008

Hi All,

In my SQL I am having temporary tables. And in Microsoft SQL Server Management Studio (Microsoft SQL Server 2005) whenever I execute sql statement its working fine & I am getting the records.

My SQL statement is using 2 databases as follows:
1.PerformanceDeficiencyNotice
2.HRDataWarehouse

Both the above databases are SQL SERVER 2000(80) with a compatibility level of 80.

The problem is when I am trying to create a new view with my sql statement and when I am saying “Verify SQL Syntax�, I am getting an error as “Invalid Object Name ‘#pdninfo’.

And when I am saying “execute SQL�, I am getting an error as “Unable to parse query text� but when I am continuing with the error, the sql statement is running and I am getting the data.

And now when I am trying to save the view I am getting the error as below
“Incorrect syntax near the keyword ‘INTO’�.
Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.

Please suggest how to solve this problem. Any help is greatly appreciated.

Thank You

MY SQL Statement is as follows:


SELECT
pdn.transactionid,
pdn.employeenbr,
pdn.lastname,
pdn.firstname,
pdn.processlevel,
pl.facilityname as processlevelname,
pdn.department,
pdn.jobcode,
pdn.title,
pdn.supemployeenbr,
pdn.managername,
pdn.timeframe as pdn_timeframe,
pdn.actualeffectivedate as pdn_startdate,
/*actualeffectivedate is the start date for the pdn. starteddate is when info starts being put in the system*/
/*the pdn end date has to be calculated for the pdn based on the timeframe and actualeffectivedate*/
case when pdn.actualeffectivedate <> convert(datetime,'01/01/1900',110) then
case pdn.timeframe
when '30' then dateadd(month,1,pdn.actualeffectivedate)
when '60' then dateadd(month,2,pdn.actualeffectivedate)
when '90' then dateadd(month,3,pdn.actualeffectivedate)
else null
end
end as pdn_enddate,
pdn.status as pdn_status,
status.description as pdn_statusdesc,
pdn.managersignoff as pdn_managersignoff,
pdn.managersignoffdate as pdn_managersignoffdate,
pdn.associatesignoff as pdn_associatesignoff,
pdn.associatesignoffdate as pdn_associatesignoffdate,
pdn.witnessname as pdn_witnessname,
/*the start date for the extension has to be calculated by subtracting 30 days from the evaluationdate*/
/*where the evaluationtype = 'X' (Extension Final).*/
/*there is only one timeframe of 30 days for an extension and only one extension is allowed per pdn for an associate*/
case
when (eval.evaluationtype = 'X' and eval.status not in ('C','D','N')) then dateadd(month,-1,eval.evaluationdate)
else null
end as ext_startdate,
eval.evaluationdate as eval_evaluationdate,/*end date of the evaluation or extension*/
eval.evaluationtype as eval_evaluationtype,
evaltype.description as eval_evaltypedesc,
eval.status as eval_status,
status2.description as eval_statusdesc,
eval.effectivedate as eval_effectivedate,
eval.managersignoff as eval_managersignoff,
eval.managersignoffdate as eval_managersignoffdate,
eval.associatesignoff as eval_associatesignoff,
eval.associatesignoffdate as eval_associatesignoffdate,
eval.witnessname as eval_witnessname
into #pdninfo
FROM [PerformanceDeficiencyNotice].[dbo].[PDNMain] pdn
left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationsMain] eval
on pdn.transactionid = eval.transactionid
left outer join [HRDataWarehouse].[dbo].[ProcessLevel] pl
on pdn.processlevel = pl.processlevel
left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status
on pdn.status = status.status and status.type = 'PDN'
left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status2
on eval.status = status2.status and status2.type = 'EVAL'
left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationTypes] evaltype
on eval.evaluationtype = evaltype.type
/*select active pdns from PDNMain (status: 'A' = Approved, 'S' = Submitted)*/
WHERE pdn.status in ('A','S')
/*select extensions from EvaluationsMain (evaluation type: 'X' = Extension Final; status: <> 'C' - Completed,*/
/*'D' - In Progress, or 'N' - Not started)*/
OR (eval.evaluationtype = 'X' and eval.status not in ('C','D','N'))

/*get last performance rating and last (maximum) performance review date from PerformanceReviewHistory*/
/*Note: A PerformanceReviewHistory record gets created within a couple of days after an associate is hired.*/
/* The rating and updatedate are null initially. Aggregate functions (i.e. MAX) ignore null values.*/
/* You must check for "updatedate IS NOT NULL" as shown below or the record will be dropped.*/
SELECT distinct(#pdninfo.employeenbr), perfreview.rating, perfreview.updatedate
into #perfreview
FROM #pdninfo, [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview
WHERE #pdninfo.employeenbr = perfreview.employeenbr
AND perfreview.updatedate =
(SELECT max(updatedate)
FROM [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview2
WHERE perfreview2.employeenbr = perfreview.employeenbr
AND updatedate IS NOT NULL)

/*select active pdns ('orig' = original)*/
SELECT 'orig' as orig_or_ext,
#pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate,
/*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/
emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi,
(SELECT emp2.lastname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname,
(SELECT emp2.firstname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname,
(SELECT emp2.mi
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi
FROM #pdninfo
left outer join #perfreview
on #pdninfo.employeenbr = #perfreview.employeenbr
left outer join [HRDataWarehouse].[dbo].[Employee] emp
on #pdninfo.employeenbr = emp.employeenbr
WHERE #pdninfo.pdn_status in ('A','S')

union

/*select extensions ('ext' = extension)*/
SELECT
'ext' as orig_or_ext,
#pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate,
/*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/
emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi,
(SELECT emp2.lastname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname,
(SELECT emp2.firstname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname,
(SELECT emp2.mi
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi
FROM #pdninfo
left outer join #perfreview
on #pdninfo.employeenbr = #perfreview.employeenbr
left outer join [HRDataWarehouse].[dbo].[Employee] emp
on #pdninfo.employeenbr = emp.employeenbr
WHERE #pdninfo.eval_evaluationtype = 'X' and #pdninfo.eval_status not in ('C','D','N')

drop table #pdninfo
drop table #perfreview

View 5 Replies View Related

Creating Insert Scripts From Tables

Mar 29, 2007

Hi,



A seemingly simple question: How do I create an Insert script from the data of a selected number of tables in a given database?



(The Insert script has to be part of a deployment phase)



Esben

View 3 Replies View Related







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