I need to create table in sql server 2005 database, I have plenty of columns. Well I know how to create them manually. but does any body know or tried before to create columns automatically, or through some batch process. I have column names already typed in TXT file (comma delimited).
all I am trying to avoid that typing work.
any advice.
Thanks
i have a table with a few hundred columns. Each SELECT statement, I list each of the columns, this is taking lots and lots of space and it is difficult to review the code due to its length...Below, I have to list out every column when I only want to use case logic on 1 column. In the next step I will have to list out every single column again
SELECT
ACCT1_NO
,ACCT1_DT
,ACCT1_RISK
,ACCT1_RISK_WEIGHT
,ACCT1_CUSTOMER_NAME
,ACCT1_CUSTOMER_ADDRESS
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
,ACCT1_CUSTOMER_CITY
,ACCT1_CUSTOMER_ZIP
--,THIS CONTINUES DOWN FOR ANOTHER 150 OR SO ACCTS.
INTO #A1
FROM STAGE.CUSTOMER_ACCTS
Is there a way I can tell SQL to take all of the columns and then list the column where I want to do my case statement. Something like the code below
(which will fail as ACCT1_CUSTOMER_ST will be listed twice.
SELECT *
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
INTO #A1
FROM STAGE.CUSTOMER_ACCTS
rewrite the below two queries (so that i can avoid duplicates) i need to send email to everyone not the dup[right][/right]licated ones)?
Create table #MyPhoneList
(
AccountID int,
EmailWork varchar(50),
EmailHome varchar(50),
EmailOther varchar(50),
[Code] ....
--> In this table AccountID is uniquee
--> email values could be null or repetetive for work / home / Other (same email can be used more than one columns for accountid)
-- a new column will be created with name as Sourceflag( the value could be work, Home, Other depend on email coming from) then removes duplicates
SELECT AccountID , Email, SourceFlag, ROW_NUMBER() OVER(PARTITION BY AccountID, Email ORDER BY Sourceflag desc) AS ROW
INTO #List
from (
SELECTAccountID
, EmailWorkAS EMAIL
, 'Work'AS SourceFlag
FROM#MyPhoneList (NoLock) eml
WHEREIsOffersToWorkEmail= 1
[code]....
Hi all!
I have been doing some testing with transactional replication. I have a table (TEST) with the following columns:
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
[stock] [int] NOT NULL CONSTRAINT [DF_Prueba1_stock] DEFAULT ((0)),
I want the table rows to have an independent stock value for each database. So,
I made a transactional publication with updatable subscriptions, to replicate all the table columns, except the stock column.
The problem is when I reset subscriptions: the table is deleted an created again on the subscriber, so all the data stored at the stock column is lost. I tried to solve this changing the "Action if name is in use" option at the publication. I choose to keep the current object without changes, but I began having problems with the generation of the identity values at the subscriber.
Any suggestions will be welcomed
Thanks in advance ;-)
Got a query taking too much time because of lack of cross columns MAX/MIN functions. Consider a similar example where a View is required to reflect distribution of Water among different towns each having four different levels of distribution reservoir tanks of different sizes:In this case the basic table has columns like:
PurchaseDate
TownName
QuantityPurchased
Tank1_Size
Tank2_Size
Tank3_Size
Tank4_Size
Now suppose I need a query to distribute QuantityPurchased in the Four additional Columns computed on the basis depending on the sizes declared in the last four fields,in the same order of preference.For example: I have to use IIFs to check: whether the quantity purchased is less than Tank_A if yes then Qty Purchased otherwise Tank_A_Size itself for Tank_A_Filled
then again IIF but this time to check:
Whether the quantity purchased less Tank_A_Filled (Which again needs to be calculated as above) is less than Tank_B if yes then Tank_A_Filled (Which again needs to be calculated as above) otherwise Tank_B_Size itself for Tank_B_Filled
Hi,
I am interested in opinions on the following,
main(product_id, short_text, price, type)
productTypes(type_id, type)
productTables(type_id, tableName)
productA(product_id, field1, field2)
productB(product_id, field1, field2)
To retrieve all products of type 'A', one must know the table name, in this case 'productA'.
Here is one method.
Create a table that contains the table name that corresponds to each product type, thus the stored procedure only needs to recieve the type_id which can be used to obtain the name of the respective table.
I am installing SQL Server 2005 on a server (Windows Server Enterprise Edition 2003 SP2) that is not domain controller and on the screen "Service Account" I checked the box "Customize for each service account" and typed a domain account (it has permission to "logon as a service"), its password and domain, and when I click the "Next" button, I am getting the error below:"SQL Server Setup could not validate the service accounts. Either the service accounts have not been provided for all of the services being installed, or the specified username or password is incorrect. For each service, specify a valid username, password, and domain, or specify a built-in system account. "
View 11 Replies View RelatedHi,
I am new at using Report Service and after successful install the SQL SERVER EXPRESS ADVANCE SP2 and configured the ReportIing Services Configuration. I try to launch the report manager by typing http://localhost/Reports I get the following error:
The report server is not responding. Verify that the report server is running and can be accessed from this computer.
I've read many forums but I couldn't resolve my problem. Any help would be greatly appreciated.
By the way, I am running IIS 7 on Window Vista Business Ed. & SQL Server Express Ed. I am not sure if this make any differences.
Hi
I have set my SQL server express server and have installed SSRS (reporting services) and Business Intelligence Studio.
When i create a new report and delpoy that report, it successfully deploys it.
when I go to the below URL's from the laptop browser, I am successfully able to see my reports
http://lemd342608/Reports$SQLExpress/Pages/Folder.aspx - Report manager
http://localhost/reportServer$SQLExpress - report server
Now when I go to a external machine besides my laptop, and type in the 'URL's I am getting 'Page cannont be displayed error'.
DO I need to configure anything in order to allow other users to access my SSRS. I have added the users in the Reporting manager also.
Thanks
I have a colm day and colm sales, I would like to have a column for each day of week i think i can use when then but not sure how
View 3 Replies View Relatedi have been trying to create new columns in a tabinvoice details using row data from another table and additional input from another form. Problem is i get no sql errors m database conection is ok usually works but doesn't write any new columns or values to the tblinvoicedetails here is the code i am using i hope someone ca tell me where i am going wrong.
<table width="100%" cellspacing="0" cellpadding="2" border="0">
<form name="form2" method="post" action="index.php" onsubmit="return v.exec()">
<?
$cid = mysql_real_escape_string($_GET['cid']);
$query = "select * from tblcustomerdetails where intCustomerID = '$cid'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$cid = $row["intCustomerID"];
?>
<tr>
<td><?print_r( $row['vcCCompanyName'])?></td><td align="left">Tel: <?print_r( $row['vcCDPhone'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCStreetNo'])?> <?print_r( $row['vcCStreet'])?></td><td align="left">Fax: <?print_r( $row['vcCFaxNo'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCCity'])?></td><td align="left">Email: <?print_r( $row['vcCEmail'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCState'])?></td><td> </td>
</tr>
<tr>
<td><?print_r( $row['vcCPostalCode'])?></td><td> </td>
</tr>
<tr>
<td><?print_r( $row['vcCCountry'])?></td><td> </td>
</tr>
</table>
<br />
<table width="100%" cellspacing="0" cellpadding="2" border="0">
<tr>
<td> </td><td><b>Code</b></td><td><b>Name</b></td><td><b>Description</b></td><td><b>Price</b></td><td colspan="2" align="center"><b>Action</b></td>
</tr>
<?
$accountsql="select * from tblpricedetails where intCustomerID=$cid order by intProductID asc";
$tempdata=$DB_site->query($accountsql);
$count =$DB_site->num_rows($tempdata);
while($rowdata=$DB_site->fetch_array($tempdata))
{
$intPriceID = $row["intPriceID"];
$intProductID = $row["intProductID"];
$vcProductCode = $row["vcProductCode"];
$vcProductName = $row["vcProductName"];
$vcProductDescription = $row["vcProductDescription"];
?>
<tr>
<td class="accountsumb">
<?=$rowdata["intProductID"]?>
</td>
<td class="accountsume">
<?=$rowdata["vcProductCode"]?>
</td>
<td class="accountsumc">
<?=$rowdata["vcProductName"]?>
</td>
<td class="accountsumc">
<?=$rowdata["vcProductDescription"]?>
</td>
<td class="accountsumc">
<?=number_format($rowdata["ftAmount"],2,".",",");?>
</td>
<td class="accountsumc" align="right">
<input type="text" size="10" name="Quantity" />
</td>
<td class="accountsumc" align="center">
<input type="text" size="10" name="total" />
</td>
</tr>
<?
}
?>
<? if($count==0)
{
?>
<tr bgcolor="#EFEFEF" class="smallblack">
<td colspan="7" align="center" bgcolor="#EFEFEF" class="redtext">This user has no product data. </td>
</tr>
<?
$insertdate = mysql_query("INSERT INTO tblinvoicedetails (intProductID,vcProductCode,vcProductName,vcProductDescription,ftAmount) VALUES(".$row['intProductID'].",".$row['vcProductCode'].",".$row['vcProductName'].",".$row['vcProductDescription'].",".$row['ftAmount'].")") or die(mysql_error());
}
?>
<tr>
<td colspan="7" align="center"><br />
<input name="submit" type="submit" class="button" value="Submit" />
</td>
</tr>
<tr>
<td colspan="7" align="center">
<input class="button" type="reset" name="reset" value="Reset" />
</td>
</tr>
</table>
</form>
Absolute beginner to SQL here...
I'm try to create an array of columns and then refer to these columns at the asp.net level.
Can't seem to find this in the SQL literature...so I'm probably looking at this all wrong.
Thanks in advance.
Larry
Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
CREATE TABLE T_FK (ID1 INT, ID2 INT)
And I want to do:
ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)
I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.
Thank you.
Have a nice day.
hi,
in the front end i have two fields one is dropdown list another is checboxlist say dropdownlist consist of title name and checkbox list consists of sub title name.on selecting title name from dropdownlist i will be getting respective title name in checkboxlist. here when i select checkboxlist(suppose if there are 6 items in checkboxlist n if i select only 3)then my databse column should generate one dropdownlist selected item and 3 checkboxlist selected item.
Eg: if dropdownlist contains( title1,title2,title3) and checkboxlist contains(chk1,chk2,chk3,chk4,chk5,chk6). if i select title1 from dropdownlist i will be geeting 6 item in checkbox list) if i select only 3 items of checkbox list. then i should be able to create column name by title1,chk1,chk2,chk3
help is appreciated
I need to create a view that counts in one of the columns.
The tricky part for me is that it should only count to three, then start on 1 again and count to three again.
Example
1 - car
2 - bike
3 - Motorbike
1 - Boat
2 - Airplane
3 - Motorboat
1 - Bicycle
Am a fresher and I got a scenario like i have to take create script of some tables of a database in sql server and I generated script to them and i have to add four new columns to the script and have to delete 2 columns from existing script for multiple tables at a time. It will take more time to do them individually.Is there any way to do this at a time and with in short period of time????
View 3 Replies View RelatedI have a table that I cannot allow a computed field to exist on (due to a 3rd party software), so I am thinking I could create a view with a computed field that is persistent, is that possible?
the syntax below will not work, I am not even sure if this is possible, but if it can work, that would be great.
I am wanting to get the sum of jetfoot1, 2 & 3 and have the total added up as "total"
create view ViewSumReport as
select JETFOOT1,JETFOOT2,JETFOOT3,(JETFOOT1+JETFOOT2+JETFOOT3)as [total] persisted
from dbo.fielddata
GO
Hi,I have a table with the following columns:ID INTEGEDR,Name VARCHAR(32),Surname VARCHAR(32),GroupID INTEGER,SubGroupOneID INTEGER,SubGroupTwoID INTEGERHow can I create a rule/default/check which update SubGroupOneID &SubGroupTwoID columns when GroupID for example is equal 15 onMSSQL2000.It is imposible to make changes on client, so I need to checkinserted/updated value of GroupID column and automaticly updateSubGroupOneID & SubGroupTwoID columns.Sincerely,Rustam Bogubaev
View 4 Replies View RelatedHi,
how to dynamically create columns for a table
Hi!
I have a select question that look like this:
DECLARE @ID uniqueidentifier;
SET @ID = '40bd3052-60f4-414a-99df-ca882c128493';
SELECT
rp.ID AS ReportPackId
,rp.SupplierPartyIdentifier AS SupplierPartyIdentifier
,rpap.AdditionalPartyIdentifier AS AdditionalPartyIdentifier
FROM
ReportPack rp
INNER JOIN ReportPackAdditionalParty rpap
ON rpap.fk_ReportPack = rp.ID
WHERE rp.ID = @ID
The result a get when running the select question is:
ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId2
My problem is that the result I want is the following:
ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
AdditionalPartyIdentifier
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
addPartyId2
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?
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.
View 3 Replies View Related
Hi,
I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.
So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths in those tables..
any help is appreciated..
Regards
Karen
I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.
Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.
Thanks in advance
John.
Hi everyone,
I need to create temporary table in one of the SP.The problem is that number of columns in table will vary depanding on input in SP.
How can i create table with variable number of columns?
Thanks,
Alex
Hi there,
i have a requirement that a temporary table contains dynamic columns depending on where condition.
my actual table is like
Key
Value
X1
x
X3
x
X5
x
Y1
y
Y2
y
when user select x, the input variable passed to stored proc and the result is shown like
column names
X1 X3 X5 as column headers.
the select query is from temporary table.
these out put is based on the user selection. so the temporary table created with columns dynamically.
please help me out.
please let me know if you didn't understand.
thanks
Praveen.
I found something related to row coloring at :: [URL] ....
I went through all the properties for column but cannot achieve my goal. SO in my case .. the no of rows are fixed .. but columns vary .. so i want the alternate columns to be of different color (say white and silver)
I have a simple script where I want to pull GLAcct, GLDesc and Amounts by Period. I want my results to look like attached snip.
I tried playing around with the dates; however, I'm receiving errors. Just to note that when I ran for 07/01/14 - 07/31/14 with the transaction date in where clause I was able to retrieve the correct results. Now I want to expand to get a view set up for the whole year....automation!
select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
sum(gl_ledger.amount_n) as Net
from gl_account
[Code] ....
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies View RelatedI have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market
How can I do this?
hai, iam new to ssrs please help me,
my report having 10 columns, i can explain my need with example that is, assume like this customer id is first column of my table customer id is 101 it having 3 departments a1,b1,c1,perticular department that is a1 having emp1,b1 having emp2,c1 having emp 3.
i want output like this when clicking + customer id driildwon it display 3 departments taht is a1,b1,c1, when +a1 drill down clicking i need to dispaly emp1, corresponding b1 to emp2 , c1 to emp 3.
above explanation is only one column of the table, like that iam also displaying this driiling procedure for remaining different columns.
and i need to display customer information by weekly,daily,monthly,yearly at bottom of the report
please give which logic used in creating format like above drilldown report which having multiple drilldowns for all columns in a table
if any body give procedure for creating fromat for drilldown report which having multiple drilldowns for all columns in a table is appriciate.
thanks
jacks
I'm having a tad bit of trouble getting output from an asynchronous component that I've written and am looking for some insight.
This component takes in a name string passed from upstream and parses the name components into standardized output fields. I'm using an asynchronous component because if the name string contains two names ("Fred & Wilma Flintstone") I'm outputting one row for Fred and one for Wilma. I've gotten it to run and with debugging have observed what appeared to me to be proper execution, but zero rows are flowing out of it.
In my ProvideComponentProperties method, I add the three fields and there associated metadata to the OutputColumnCollection. Is this method where this should occur? It's before the PrimeOutput method, so I didn't know if I should be creating the output columns in ProcessInput (i.e., after the output buffer is provided by PrimeOutput.)
In ProcessInput, I'm using AddRow for each input row and another if it contains a second name, setting the value for each index using the buffer's SetString method, to no avail. I can observe it to this point, but then don't know what's in that output buffer (if I'm using the wrong buffer index value, etc)
Thanks.
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?