Create An Insert Statement For A Row Containing The Word With.
Nov 13, 2007
I know the word 'With' is a SQL Reserved; I created a SQL Script Generator that creates insert statements to copy data from one SQL Server database to another. If a row contains the word 'with' or 'With' the insert statement fails. Is there a way around this? The script is executed in the Query Analyzer window of SQL Server.
The record below is an example of a row that fails.
Insert Into [Contact] ([Active],[AddressID],[Birthday],[Birthmonth],[ContactType],[CreatedBy],[CreatedDate],[Description],[Employed],[FirstName],[HasResume],[LastName],[MiddleName],[ModifiedBy],[ModifiedDate],[Personality],[RowVersion])
Values ('False', 0, 0, 0, 1, 0, '', 'Wants to get back with Van Halen', 'False', 'David', 'False', 'Roth', 'Lee', '', '', '', '')
The error message shown is:
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Thank you,
Corby Nichols
Flying Elephant Software
View 7 Replies
ADVERTISEMENT
Jan 12, 2007
Im having a slight problem with my Like statement, I'm just building a simple search/match routine. I have the following SQL statement SELECT TOP(4) tags FROM post WHERE atags LIKE '%test%'that statement is ok, it will return many results with test in it but if I add more then one search word like LIKE '%+test test2+%' or LIKE '%+test%test2+%' All I seem to get is results that only have the exact two tags not any others that may have one of them.How can I get it to display all records that have an instance of any of those words. Any help would be much appreciated.
View 14 Replies
View Related
May 9, 2007
Hi everybody,
I'm looking for a tool or template that creates a good looking document from the rdl-files.
I need it for internal documentation.
Can you help me?
Gerd
View 1 Replies
View Related
Nov 23, 2007
Hello,
I'm looking for the best solution for creating database tables based on
a Word Document with a table. I've attached part of the table from the word doc. This document is base on a template for audit reporting.
What I assume is I would need first to start of by creating two tables.
One table would consist of the fields of the horizontal upper most data e.g.
conformance, noncomformance etc. The other table would consist of the vertical fields of the word doc table, e.g. editorial, Auditee Name etc. If my assumption is correct, what primary key and foreign key would I need to have?
Any help would be much appreciated.
Thanks
Karl
View 3 Replies
View Related
Jul 17, 2013
How do you include a word with an apostrophe in an SQL in statement without getting an error msg? For example this syntax:
where provname1 IN ('Children's')
With that syntax above, I would get an error message.
View 4 Replies
View Related
Sep 30, 2006
Can any tell me how to simpley insert a MS word document into an SQL Server database with a field type of image.
View 3 Replies
View Related
Jun 12, 2007
I am using VS2005 to construct a website. I have a sql database with 3 datasets. One of the table adapters is called Proudcts and has the following fields. ProductItem, Description, Price, ProductID, PackSizeI want to be able to see a summary of the products (there are thousands of them) using one or more words (or partial words) in one text box to search in only 3 fields (ProductItem, Description, ProductID, ). This needs to show only records which contain the search criteria in a gridview?This is such a basic requirement for a website, and can be found on many sites, but I haven't found how to do it.Thanks, Bri
View 7 Replies
View Related
Jan 16, 2015
We compulsorily use NOLOCK in our scripts on reporting DB and development generally forget putting NOLOCKS or schema names with tables.
Creating a script to search for a missing word or a phrase in SQL Server script.
Our format is
<schema>.<table> <alias here> WITH NOLOCK <or alias here>
or
<schema>.<table> <alias here> NOLOCK <or alias here>
or
<schema>.<table> <alias here> (NOLOCK) <or alias here>
but generally developers write only <table> <alias>
View 4 Replies
View Related
Aug 22, 2007
Last year I created an online newsletter for my company using word (doc, not html) and sent it through send to: with Outlook to our staff. It worked great - however the width was always smaller than the viewable area. Not having a measurement of pixels or resolution to determine width in my layout - how do I accurately adjust my width in a word doc to adequately size to the viewable screen of my audience?
Any advise is appreciated.
Thanks,
CB
View 1 Replies
View Related
Aug 6, 2015
I am required to find or check that a specific word exists in string or not.
Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.
1. 'St is valid;' Â Â -> true
2. 'DOB is valid;ST is invalid;' Â Â -> true
3. 'DOB is valid; ST is invalid;'   -> true
4. 'DOB is valid;invalid ST;'   -> true
5. 'DOB is valid; invalid ST;'   -> true
6. 'DOB is valid; invalid STate;'Â Â Â -> false
Means the exact ST should be search. It is  not free text search.
T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.
View 10 Replies
View Related
Mar 14, 2006
Hi,I'm having an issue where users are copying and pasting text from word documents. The formatting of certain special characters such as mswrods curly ' is fine until the string is inserted in to the db (sql2000 varchar field). I'm using asp.net 2.Any ideas on this one?Ta Dan
View 3 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Feb 24, 2008
i have report with parameter and he can have a null in parameter ther is null word can i change it to another word like all or any thing else
View 4 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Apr 21, 2008
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
View 7 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Jan 14, 2008
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
View 5 Replies
View Related
Mar 30, 2005
hi!
I'm just wondering does anyone know how to create an sql command that can search word by word?
what i mean is like I have a product with name 'harry potter broom'.
I want an sql command where if i type only 'harry broom' this 'harry potter broom' product will show up.
Does anyone have any idea?
Here's my sql comand: (I'm using asp.net vb script do develop this system)
-------------------------------------------------------------------------
query = "select distinct * from product where " & _
"(pname like '%" & keyword & "%' or " & _
"pdesc like '%" & keyword & "%' ) and " & _
"(price >= " & price1 & " and price <= " & price2 & _
") and status <> 'out of stock' order by price asc"
-------------------------------------------------------------------------
Thank you.
View 4 Replies
View Related
May 26, 2006
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View 10 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Jul 20, 2005
I'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)
View 3 Replies
View Related
Sep 24, 2007
Is the word "Name" a reserved word in SQL? look at line 10 of my stored procedure. When I use the word "Name"it is highlited in blue by SQL Server?
Note I only list part of the stored proc
1 CREATE PROCEDURE [dbo].[GetXMLPeopleNames] 23 (4 @Status nvarchar(3)5)6 AS7 SELECT8 PersonId,9 PersonDescription,10 Name,11 UpdateDate,12 UpdateAppUser13 FROM14 Customer WHERE Customer.PersonDescription=@Status15 ORDER BY
View 2 Replies
View Related
Oct 27, 2005
I have a database that is far from being rational! I need to query the tables to get a hierarchy resultset. Here is the structure:levelOne levelOne_A levelTwo levelThree levelFourmy problem is that table levelOne_A and table levelTwo have data that are considered to fall underneath levelOne. However the data is far different from each other so I can not able merge both into one.so the it should look like this:levelOne levelOne_A AND levelTwo levelThree levelFourAny ideas how I can get my hierarchy structure?Thanks for your help!
View 1 Replies
View Related
Jan 20, 2008
Hello, I am trying to autogenerate a new guid in my sql statement. Here is a sample of my code:string comments = "CREATE TABLE comments (domaininfoid UNIQUEIDENTIFIER PRIMARY KEY NOT NULL," +
"comment TEXT NOT NULL)";
How can I make sql auto generate a guid?
View 2 Replies
View Related
Mar 25, 2008
im creating a custom sql statement where my code starts like tt.. its a double query and how do i link the 2nd part to the first part (select * from PO where 1=1)?<script runat="server"> protected void CheckBox1_CheckedChanged(object sender, EventArgs e) { strquery += " and PO between " + textbox1.text + " and " + textbox2.text; } protected void CheckBox2_CheckedChanged(object sender, EventArgs e) { strquery += " and Dlvdate between " + textbox3.text + " and " + textbox4.text; }</script> im a serious newbie with C#
View 3 Replies
View Related
Oct 17, 2013
I'm using SQL Server 2012 for my databases.
Now I'm trying to write a search module. Therefore I have to create a view, with all datas to make it easy for searching. Unfortunately I have a table, which creates a tree (let's say, it's a navigation tree). For the view, I like to have the navigation as a path:
/Main/FirstSub/SecondSub
My navigation tbl looks like this:
Code:
IDParentIDText
10Main
20otherMain
[Code]....
View 4 Replies
View Related
Jun 10, 2007
Hi,
I am new to SQL Server and need your help.
I have a table (old_table) with about 500 columns and each column of type varchar. I have to generate a table (new_table) which has all the columns of the old table but with different data types.
Is there a way I can use the syscolumns to generate a “create table� statement?
Thanks.
View 4 Replies
View Related
Mar 27, 2008
Hi.
I was wondering if I could have a query like this:
CREATE USER 'Firstname Lastname' FOR LOGIN 'DOMAINuser' WITH DEFAULT_SCHEMA=[dbo]
The combination of firstname and lastname does not seem to work.
I have been using:
sp_grantdbaccess 'DOMAINuser', 'Firstname Lastname'
but i cannot specify DEFAULT_SCHEMA with that sp.
Any suggestions?
View 1 Replies
View Related
May 28, 2014
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
;
View 1 Replies
View Related
Apr 13, 2006
I have a following table :
ID ------- Answear ------ Vote
1 ------- 1|2|3|4 ------- 3|5|3|2
2 ------- 1|2|3|4 ------- 2|5|3|1
3 ------- 1|2|3|4 ------- 2|5|7|2
So, I need to create : ,
ID ----- Answear ------ Vote ----- Total ----- Percent
1 ----- 1|2|3|4 ------ 3|5|3|2------ 13-----23.08|38.46|23.08|15.38
2 ----- 1|2|3|4 ------ 2|7|8|1------ 18-----.....
3 ----- 1|2|3|4 ------ 2|5|7|2------ 16-----.....
Any one help me ?
Thanh you very much.
PS :(3/13)*100|(5/13)*100|(3/13)*100|(2/13)*100 after calculate : 23.08 % |38.46 %|23.08 %|15.38 %
View 6 Replies
View Related
Jul 23, 2005
In general, What is the best approach in creating a dynamic updatestored procedure, that can handle recieving varying input paramters andupdate the approporiate columns.
View 6 Replies
View Related
Jul 20, 2005
Is it possible to have part of a table name used in a CREATE statementcontained in a variable? Here's what I'd like to do, althoughobviously the syntax of this isn't quite right or I wouldn't be hereasking:DECLARE @TblPrefix char (3)SET @TblPrefix = 'tst'CREATE TABLE @TblPrefix + TestTable (col1 int)The point there is to have a table named tstTestTableThe reason I need to do this is that my ISP will only give me onedatabase to work with and I'd like to have two copies of theapplication I'm developing running at the same time. So I'd like torun the sql script that creates the tables with TblPrefix set to "dev"and then run it again with TblPrefix set to "liv"thankseric
View 3 Replies
View Related
Jan 30, 2008
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?
View 5 Replies
View Related
