I am having a users table which contains "Mobile" column as well. I want a query to set the country code value by default into the column name so that the column should be updated with the mobile number along with the default country code.
View 10 Replieshello friends
my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn)
'you should use sproc instead
cmd.Parameters.AddWithValue("@UserId", textbox1.text)
'your value
Try
conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery()
conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString()))
Catch sex As SqlExceptionThrow sex
Finally
If conn.State <> Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource()
SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text
SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)"
SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0
Try
rowsaffected = SglDataSource2.Insert()Catch ex As Exception
Server.Transfer("yardim.aspx")
Finally
SglDataSource2 = Nothing
End Try
If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx")
ElseServer.Transfer("urunsat.aspx")
End If
cheers
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
Hello,
I'm new to the SQL world and want to know if this is the site for a newbie or would you recommend another site more my speed. Thanks for the input.
Clint
US Navy - fueled, armed, and go for launch.
I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF')
--insurancetype is the new column.
I have the country iso table does anyone have the data for tying contenents to the countries?
You can do anything at www.zombo.com
Hi,
I have created some statistics for my site based on the ip of the visitors. For that reason I have created two tables
1. One table with a column which hold the ip number of the visitor
Table: AccessLog
Column: IP
2. One table with the ip range and the countries
Table: Countries
Columns: ip_to, ip_from, country_name
I would like to create a query which will bring the country name of the visitor IP which IP will be like "ip_to <= ip <= ip_from"
Thanks
Denis
www.tabletennis.gr
I dont know the exact position of '/',',','--' or any special character.I have to fetch first name of the country.
create table abc
(country nvarchar(200))
go
INSERT INTO abc VALUES ('Tennessee/Virginia,USA')
GO
INSERT INTO abc VALUES ('Kansas,USA')
[code]...
Hi!How I can know the "Country version" of SQL Server using VB.NET(perhaps with the SQLDMO)?I mean: if the SQL Server is in "English version" or in "Italianversion" or ...Thank you so much!!Bye
View 1 Replies View RelatedI have some stored procedure and there is a cursor inside it. I added some new columns to the table and those columns I included in the cursor declaration and fetch statement. In the cursor declaration I forgot to add comma (,) in between the new columns. So SQL Server it considers as a alias name for that column so syntactically it is correct. But logically in the cursor declaration having less number of columns than the columns in the fetch statement. So it should throw an error. But the procedure is getting compiled without raising any error. But if I execute the procedure that time it is throwing the error.
For example, below I have given the sample procedure. In this procedure, in the cursor declaration I removed the comma (,) between DOB and DOJ. If I compile this procedure it is getting compiled. But when execute that time only it is throwing the error. So I am interested in if any option is available to know the error in the compilation time itself.
ALTER PROCEDURE Test
AS
BEGIN
BEGIN TRY
DECLARE @empId INT,
@fname VARCHAR(50),
@dob DATE,
@doj DATE
[code]....
My company needs to move a 30 Gig SQL Server across the country as soonas possble on July 1. Turns out moving the full db across the networktakes a few hours.I'd like to move a full copy of db a week ahead of time, and then justmove either a differential backup(s) or transaction log with with theweek's new data on July 1.Can anyone suggest the best strategy for doing this? Currently we'redoing a differential backup each hour on the db, and dumping the txnlog each night.Many thanks,Burt
View 1 Replies View RelatedQuick questiom, I need to Import a table with City and State Information
What are your recomendations? Should I create a Unique Key for them?
CityID, CityName, StateID?
StateID, StateName?
Or can I just store them in a table with one column?
CityName, State
Thanks for your help/
I have built a sample table, query, and results for this question. I am using SQL server.
declare @TableX Table
(
Date Date not null,
ID int not null,
Tick varchar(6) not null
)
INSERT @tableX
[Code] ....
Expected results:
DATE(No column name)count
2013-09-02LON1
2013-09-03LON1
2013-09-04LON1
2013-09-05LON1
2013-09-06LON1
2013-09-02USA2
2013-09-03USA2
2013-09-04USA2
2013-09-05USA2
2013-09-06USA2
I want to select unique country - date pairs. It is not even necessary to have the count of each one, just the list of unique country/dates.
My query here uses 'group by' to accomplish this task, but there may be a way to do this with a self join. I believe using a self join would make the query faster.
1) Is this possible to do with a self join?
This Question is pertaining to AND and OR operators.
If we want to retrieve data form a country combined with 3 to 4 cities how do we handle this?
Say for ex:- i want to retrieve all data from Customers table where country is Germany and cities are Berlin, Mannheim,Brandenburg and München.
Any help here would be greatly appreciated...
Unfortunately, data wasn't filtered prior to getting inserted into this table. Now I am stuck with cleaning it up. I have thought about writing a query to update all the values, but there are just too many variations, including spelling mistakes, so I've ruled that out as a possible solution.
I have a table which has a Country field but the values per record vary. For example US, U.S., USA, United States, UK, United Kingdom, Canada, Can, etc. I'm trying to find the percent of records per country.
Sample table data: mytable
Id Name Country
1 John US
2 James UK
3 Jane United States
4 Mary Canada
5 Jack U.S.
6 Tony United Kingdom
7 Jeff US
8 Tom Canada
9 Beth UK
10 Mark USA
I would like to show
US: 50% --> (includes any variation of US ncluding US, U.S., USA, United States)
UK: 30%
CAN: 20%
I've made several attempts myself with no luck. Thanks in advance.
I have one requirements below..
Table input
Eno       ename                 Eloc      Â
Edept
1             Sid                        Â
Pune    101,201,301,401,501,601
Output:
Eno       ename                 Eloc      Â
Edept
1             Sid                        Â
Pune    101
1             Sid                        Â
Pune    201
1             Sid                        Â
Pune    301
1             Sid                        Â
Pune    401
1             Sid                        Â
Pune    501
1             Sid                        Â
Pune    601
When someone clicks my form
i use this code to enter data in Server:
private void Submit_Click(object sender, System.EventArgs e)
{
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader rd = null;
try
{
con = new SqlConnection("server=localhost; uid=sa; pwd=123; database=TaskManagement");
cmd = new SqlCommand("INSERT INTO TASK_TYPE (name) VALUES ("+name.Text+")", con);
con.Open();
rd = cmd.ExecuteQuery();
}
catch (Exception e)
{
Response.Write("<p><font color="red">Error: ");
Response.Write(e.Message);
Response.Write("</font></p>");
}
finally
{
if(rd != null)
rd.Close();
if(con != null)
con.Close();
}
}
is that right? I am new in c# and i try to make it work!
Thanks in advance!
I am searching for the Sql Server Script of all country State and City with the Following type..
For the Country only 2 Columns are there i.e. CountryId and CountryName(CountryId is primary Key)
For the State 3 Columns are there i.e. CountryId, StateId and StateName(StateId is Primary Key and CountryId is Foreign Key)
For the City 3 Columns are there i.e. StateId ,CityId and CityName(CityId is PrimaryKey and StateId is foreign Key).
I need this type of Script with Column name.
Hi I am trying to inset data to my sql 2005 database using a webform.. the code I have is
3 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 4 5 ConnectionString="<%$ ConnectionStrings:SQL2005440975 %>" 6 7 InsertCommand="INSERT INTO [dbo.asp.net_Addresstbl] ([Salutation], [fname], [sname], [Daydb], [Monthdb], [Yeardb], [txtOrg], [txtLine1], [txtLine2], [txtLine3], [txtTown], [txtPostcode], [UserID]) 8 9 VALUES (@Salutation, @fname, @sname, @Daydb, @Monthdb, @Yeardb, @txtOrg, @txtLine1, @txtLine2, @txtLine3, @txtTown, @txtPostcode, @UserID)" 10 11 <InsertParameters>12 <asp:FormParameter FormField="Salutation" Name="Salutation" Type="String" />13 <asp:FormParameter FormField="fname" Name="fname" Type="String" />14 <asp:FormParameter FormField="sname" Name="sname" Type="String" />15 <asp:FormParameter FormField="Daydb" Name="Daydb" Type="Decimal" />16 <asp:FormParameter FormField="Monthdb" Name="Monthdb" Type="String" />17 <asp:FormParameter FormField="Yeardb" Name="Yeardb" Type="Decimal" />18 <asp:FormParameter FormField="txtOrg"Name="txtOrg" Type="String" />19 <asp:FormParameter FormField="txtLine1" Name="txtLine1" Type="String" />20 <asp:FormParameter FormField="txtLine2" Name="txtLine2" Type="String" />21 <asp:FormParameter FormField="txtLine3" Name="txtLine3" Type="String" />22 <asp:FormParameter FormField="txtTown" Name="txtTown" Type="String" />23 <asp:FormParameter FormField="txtPostcode"Name="txtPostcode" Type="String" />24 <asp:FormParameter FormField="UserID" Name="UserID" Type="Object" />25 </InsertParameters>26 </asp:SqlDataSource>27 28 <asp:DropDownList ID="Salutation" runat="server" ValidationGroup="Address">29 <asp:ListItem>Choose One</asp:ListItem>30 <asp:ListItem>Mr.</asp:ListItem>31 <asp:ListItem>Mrs.</asp:ListItem>32 <asp:ListItem>Ms.</asp:ListItem>33 <asp:ListItem>Miss.</asp:ListItem>34 <asp:ListItem>Rev.</asp:ListItem>35 <asp:ListItem>Doc.</asp:ListItem>36 <asp:ListItem>Other.</asp:ListItem>37 </asp:DropDownList>38 39 <asp:TextBox ID="fname" runat="server" CausesValidation="True"></asp:TextBox>40 41 <asp:TextBox ID="sname" runat="server" CausesValidation="True"></asp:TextBox>42 43 <asp:DropDownList ID="Daydb" runat="server" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">44 <asp:ListItem>Day</asp:ListItem>45 <asp:ListItem>01</asp:ListItem>46 <asp:ListItem>02</asp:ListItem>47 <asp:ListItem>03</asp:ListItem>48 <asp:ListItem>04</asp:ListItem>49 <asp:ListItem>05</asp:ListItem>50 <asp:ListItem>06</asp:ListItem>51 <asp:ListItem>07</asp:ListItem>52 <asp:ListItem>08</asp:ListItem>53 <asp:ListItem>09</asp:ListItem>54 <asp:ListItem>10</asp:ListItem>55 <asp:ListItem>11</asp:ListItem>56 <asp:ListItem>12</asp:ListItem>57 <asp:ListItem>13</asp:ListItem>58 <asp:ListItem>14</asp:ListItem>59 <asp:ListItem>15</asp:ListItem>60 <asp:ListItem>16</asp:ListItem>61 <asp:ListItem>17</asp:ListItem>62 <asp:ListItem>18</asp:ListItem>63 <asp:ListItem>19</asp:ListItem>64 <asp:ListItem>20</asp:ListItem>65 <asp:ListItem>21</asp:ListItem>66 <asp:ListItem>22</asp:ListItem>67 <asp:ListItem>23</asp:ListItem>68 <asp:ListItem>24</asp:ListItem>69 <asp:ListItem>25</asp:ListItem>70 <asp:ListItem>26</asp:ListItem>71 <asp:ListItem>27</asp:ListItem>72 <asp:ListItem>28</asp:ListItem>73 <asp:ListItem>29</asp:ListItem>74 <asp:ListItem>30</asp:ListItem>75 <asp:ListItem>31</asp:ListItem>76 </asp:DropDownList>77 78 <asp:DropDownList ID="Monthdb" runat="server" style="text-align: left">79 <asp:ListItem>Month</asp:ListItem>80 <asp:ListItem>January</asp:ListItem>81 <asp:ListItem>February</asp:ListItem>82 <asp:ListItem>March</asp:ListItem>83 <asp:ListItem>April</asp:ListItem>84 <asp:ListItem>May</asp:ListItem>85 <asp:ListItem>June</asp:ListItem>86 <asp:ListItem>July</asp:ListItem>87 <asp:ListItem>August</asp:ListItem>88 <asp:ListItem>September</asp:ListItem>89 <asp:ListItem>October</asp:ListItem>90 <asp:ListItem>November</asp:ListItem>91 <asp:ListItem Value="12">December</asp:ListItem>92 </asp:DropDownList>93 94 <asp:DropDownList ID="Yeardb" runat="server" style="text-align: left" 95 OnSelectedIndexChanged="Year_SelectedIndexChanged" 96 DataSourceID="YearDataSource" DataTextField="Year" DataValueField="Year">97 <asp:ListItem Selected="True">Choose Year..</asp:ListItem>98 </asp:DropDownList>99 <asp:AccessDataSource ID="YearDataSource" runat="server" 100 DataFile="~/App_Data/year.mdb" SelectCommand="SELECT [Year] FROM [Year]">101 </asp:AccessDataSource>102 103 <asp:TextBox ID="txtFind" runat="server" CausesValidation="True" ValidationGroup="address"></asp:TextBox>104 105 <asp:Button ID="btnFind" runat="server" Text="Find" 106 OnClick="btnFind_Click" ValidationGroup="address" 107 OnClientClick="lstProperties" />108 109 110 <asp:ListBox ID="lstProperties" runat="server" AutoPostBack="True" 111 OnSelectedIndexChanged="lstProperties_SelectedIndexChanged" Visible="False" 112 Width="200px"></asp:ListBox>113 114 <asp:TextBox ID="txtOrg" runat="server" OnTextChanged="txtOrg_TextChanged" 115 ReadOnly="True" ValidationGroup="address2" Visible="False"></asp:TextBox>116 117 <asp:TextBox ID="txtLine1" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>118 119 <asp:TextBox ID="txtLine2" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>120 121 <asp:TextBox ID="txtLine3" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>122 123 <asp:TextBox ID="txtTown" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>124 125 <asp:TextBox ID="txtPostcode" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>126 127 <asp:Label ID="lblAddress" runat="server" CssClass="style9"></asp:Label>128 129 <asp:Button ID="submitaddress" runat="server" Text="Add Details" 130 style="text-align: centre" CommandName="Submit" 131 ValidationGroup="address" PostBackUrl="~/MemberPages/account.aspx" />132 133 </asp:Content>
Will this code work? if not can you explain why not and offer an example that will work. In the mean time I am reading up on these insert statements.
Regards
Mal
Trying to optimise the below query, I believe it's do with the estimated rows on the unpivot using Supratimes this seems to be the only sticking point.The query below is an example replicating what I'm trying to do in live, it takes around 2 seconds to run on my pc.
Create --drop --alter
Table #Actuals
(
Period1 FLOAT
, Period2 FLOAT
, Period3 FLOAT
, Period4 FLOAT
[code]....
Not sure what happened to my post, it seems to have disappeared. Here we go again. I have a stored procedure that I would like some feedback on, as I feel it may be inefficient as coded:
@ZUserID varchar(10)
AS
SET NOCOUNT ON
DECLARE @counter int
SET @counter = 0
WHILE @counter < 10
BEGIN
SET @counter = @counter + 1
IF EXISTS(SELECT * FROM tblWork WHERE UserID = @ZUserID And LineNumber = @counter)
BEGIN
UPDATE tblWork SET
TransID = Null,
TransCd = Null,
InvoiceNo = Null,
DatePaid = Null,
Adjustment = Null,
Vendor = Null,
USExchRate = Null
WHERE
UserID = @ZUserID And LineNumber = @counter
END
ELSE
INSERT INTO tblWork
(LineNumber,TransCd,UserID)
VALUES
(@counter,'P',@ZUserID)
END
Hello,
I am creating an ASP.NET web site which inserts data in a SQL database using LINQ.
One table column will hold HTML code. I am using a simple TextBox to input the data.
I would like to create my text in my computer and then copy/paste the HTML code to my TextBox and insert in the database.
What software should I use to create my text? Microsoft Office 2007, ... ? Any suggestion would be great.
Can I do it this way?
Thanks,
Miguel
I use the following query to shred an xml and insert it into a table, but I want to have the whole html structure into the column to be able to present the formatted text in Cognos 8 BI.
WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
INSERT INTO TMP_ATGFORSLAG (AtgForslagDesc)
SELECT
AtgForslagRad.value('(/my:AtgForslagRad/my:AtgForslagDesc)[1]', 'varchar(2000)') AS AtgForslagDesc
FROM dbo.DeklAtgForslagXml
The structure of the source in the xmlfile I want to import is:
<my:AtgForslagDesc>
<html xmlns="http://www.w3.org/1999/xhtml">
<ol>
<li>Text1...</li>
<li>Text2...</li>
<li>Text3...</li>
</ol>
</html>
</my:AtgForslagDesc>
How do I shred not only the text but the whole starting <html> to finishing </html> and insert it into a table I would be very happy.
All:
I have created a stored procedure on SQL server that does an Insert else Update to a table. The SP starts be doing "IF NOT EXISTS" check at the top to determine if it should be an insert or an update.
When i run the stored procedure directly on SQL server (Query Analyzer) it works fine. It updates when I pass in an existing ID#, and does an insert when I pass in a NULL to the ID#.
When i run the exact same logic from my aspx.vb code it keeps inserting the data everytime! I have debugged the code several times and all the parameters are getting passed in as they should be? Can anyone help, or have any ideas what could be happening?
Here is the basic shell of my SP:
CREATE PROCEDURE [dbo].[spHeader_InsertUpdate]
@FID int = null OUTPUT,@FLD1 varchar(50),@FLD2 smalldatetime,@FLD3 smalldatetime,@FLD4 smalldatetime
AS
Declare @rtncode int
IF NOT EXISTS(select * from HeaderTable where FormID=@FID)
Begin begin transaction
--Insert record Insert into HeaderTable (FLD1, FLD2, FLD3, FLD4) Values (@FLD1, @FLD2, @FLD3,@FLD4) SET @FID = SCOPE_IDENTITY(); --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 1 return @rtncode end endELSE
Begin begin transaction
--Update record Update HeaderTable SET FLD2=@FLD2, FLD3=@FLD3, FLD4=@FLD4 where FormID=@FID;
--Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 2 return @rtncode end
End---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Thanks,
Blue.
I have two rows of data:
Customer as string, Date as int
These values are predefined so I cannot use autoindex. I have to make sure that when I insert a row of data that it does not exist already int the database. there can be duplicate customers and duplicate dates but not duplicate customer/date combinations. What would the sql query be?
if (select count(1) from customers where customer = @customer and date = @date) =< 0
insert into table1(customer, date) values (@customer, @date)
end if
I have to insert data from about 30 tables into a single table (Users),to do so i used a cursor and a bit of dynamic sql, this should workfine if the tables have to do the select from had the same number ofcolumns, how ever they don't.I only need the first 5 columns from each the table, in the case wherethey have more than the 5 i need for my 'Users' table i get this error:'An explicit value for the identity column in table 'Users' can only bespecified when a column list is used and IDENTITY_INSERT is ON.'.Is there a way to select only the first five columns from a table, ifnot is there another solution for my problem?My Sql query is the following:DROP TABLE UsersCreate Table Users(ID INT identity PRIMARY KEY,TIPO VARCHAR(255),NOME VARCHAR(255),USERNAME VARCHAR(255),EMAIL VARCHAR(255),GROUPID VARCHAR(255))DECLARE @Table VARCHAR(255)DECLARE @Sql VARCHAR(8000)DECLARE sysCursor CURSORFORSELECT name FROM sysobjects where xtype='U'OPEN sysCursorFETCH NEXT FROM SysCursor into @Tablewhile @@FETCH_STATUS<>-1BEGIN/** INSERE VALORES NA TABELA DE TESTE*/SET @SQL = 'INSERT INTO UsersSELECT * FROM ['+ @Table +']'EXECUTE (@SQL)SET @SQL = 'UPDATE Users SET GROUPID=' + @Table +'WHERE GROUPID IS NULL'EXECUTE (@SQL)print @tableFETCH NEXT FROM SysCursor INTO @TableENDCLOSE sysCursor/** APAGA VALORES INVÁLIDOS DA TABELA DE TESTE*/DELETE FROM Users WHERE TIPO IS NULLDELETE FROM Users WHERE NOME='Nome'DEALLOCATE sysCursorI hope you can give me hand, thank you in advance.
View 8 Replies View RelatedHi all,
I'm trying to write a database table that is ONLY accessiable to ONE login / DB user and restrict access to what that user can do with the table.
At the moment I have granted SELECT & INSERT permissions but some of the fields in the table should also be restricted, but for some reason SQL doesn't allow a deny on INSERT for columns.
It seems strange that you can deny an UPDATE permission on a column but not INSERT, so users can create new lines of junk into fields you dont want them to, but cant change that junk afterward??
Anyone have any ideas how I can prevent this user from altering these fields at all?
Thanks in advance
So I want to insert 40 values into a table, starting at a particular column. Like this:
INSERT INTO MyTable (1) VALUES (...my forty values...)
Pretending that 0 (zero) indexes the first column, and 1 indexes the second column, the purpose is to skip the (first) column that contained an identity value (since normally you can't insert into an identity column anyway).
The only way I currently know how to solve this problem, is to use highly verbose syntax, like this:
INSERT INTO MyTable (...my forty column names...) VALUES (...my forty values...)
But yuck, who wants to explicitly mention all forty column names, ONLY BECAUSE I'm trying to avoid inserting a value into the first column which contains the identity?
I am trying to do a BULK insert using BCP and a XML format file.
I have a file with 32 columns, and a table with 34 columns that i want to bulk insert
i have read here:
http://msdn.microsoft.com/en-us/library/ms179250.aspx
to try and do the bulk insert. but it doesn't seem to insert the data into the correct columns.
here is the SQL to create the table.
SQL Code:
Original
- SQL Code
CREATE TABLE [dbo].[prep_SomeTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Last_Update] [smalldatetime] null,
[ClientID] [varchar](10) NOT NULL DEFAULT (''),
[pollSystem] [varchar](10) NOT NULL DEFAULT(''),
[vin] [varchar](17) NOT NULL DEFAULT(''),
[year] [varchar](4) NOT NULL DEFAULT(''),
[make] [varchar](50) NOT NULL DEFAULT(''),
[model] [varchar](50) NOT NULL DEFAULT(''),
[trim] [varchar](50) NOT NULL DEFAULT(''),
[mileage] [varchar](10) NOT NULL DEFAULT(''),
[factColor] [varchar](100) NOT NULL DEFAULT(''),
[intFactColor] [varchar](100) NOT NULL DEFAULT(''),
[doors] [varchar](10) NOT NULL DEFAULT(''),
[newused] [varchar](10) NOT NULL DEFAULT(''),
[stockno] [varchar](10) NOT NULL DEFAULT(''),
[cpo] [varchar](10) NOT NULL DEFAULT(''),
[certType] [varchar](50) NOT NULL DEFAULT(''),
[certid] [varchar](10) NOT NULL DEFAULT(''),
[otherprice] [varchar](10) NOT NULL DEFAULT(''),
[webprice] [varchar](10) NOT NULL DEFAULT(''),
[invprice] [varchar](10) NOT NULL DEFAULT(''),
[msrp] [varchar](10) NOT NULL DEFAULT(''),
[mktClass] [varchar](50) NOT NULL DEFAULT(''),
[options1] [varchar](8000) NOT NULL DEFAULT(''),
[instock] [varchar](50) NOT NULL DEFAULT(''),
[manfModelNum] [varchar](50) NOT NULL DEFAULT(''),
[trans] [varchar](100) NOT NULL DEFAULT(''),
[enginedesc] [varchar](100) NOT NULL DEFAULT(''),
[drivetrain] [varchar](100) NOT NULL DEFAULT(''),
[overviewtext] [varchar](8000) NOT NULL DEFAULT(''),
[options2] [varchar](8000) NOT NULL DEFAULT(''),
[chromestyleid] [varchar](10) NOT NULL DEFAULT(''),
[photourls] [varchar](8000) NOT NULL DEFAULT(''),
[photosupdated] [varchar](10) NOT NULL DEFAULT(''),
CONSTRAINT [PK_prepSomeTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
CREATE TABLE [dbo].[prep_SomeTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Last_Update] [smalldatetime] NULL, [ClientID] [varchar](10) NOT NULL DEFAULT (''), [pollSystem] [varchar](10) NOT NULL DEFAULT(''), [vin] [varchar](17) NOT NULL DEFAULT(''), [year] [varchar](4) NOT NULL DEFAULT(''), [make] [varchar](50) NOT NULL DEFAULT(''), [model] [varchar](50) NOT NULL DEFAULT(''), [TRIM] [varchar](50) NOT NULL DEFAULT(''), [mileage] [varchar](10) NOT NULL DEFAULT(''), [factColor] [varchar](100) NOT NULL DEFAULT(''), [intFactColor] [varchar](100) NOT NULL DEFAULT(''), [doors] [varchar](10) NOT NULL DEFAULT(''), [newused] [varchar](10) NOT NULL DEFAULT(''), [stockno] [varchar](10) NOT NULL DEFAULT(''), [cpo] [varchar](10) NOT NULL DEFAULT(''), [certType] [varchar](50) NOT NULL DEFAULT(''), [certid] [varchar](10) NOT NULL DEFAULT(''), [otherprice] [varchar](10) NOT NULL DEFAULT(''), [webprice] [varchar](10) NOT NULL DEFAULT(''), [invprice] [varchar](10) NOT NULL DEFAULT(''), [msrp] [varchar](10) NOT NULL DEFAULT(''), [mktClass] [varchar](50) NOT NULL DEFAULT(''), [options1] [varchar](8000) NOT NULL DEFAULT(''), [instock] [varchar](50) NOT NULL DEFAULT(''), [manfModelNum] [varchar](50) NOT NULL DEFAULT(''), [trans] [varchar](100) NOT NULL DEFAULT(''), [enginedesc] [varchar](100) NOT NULL DEFAULT(''), [drivetrain] [varchar](100) NOT NULL DEFAULT(''), [overviewtext] [varchar](8000) NOT NULL DEFAULT(''), [options2] [varchar](8000) NOT NULL DEFAULT(''), [chromestyleid] [varchar](10) NOT NULL DEFAULT(''), [photourls] [varchar](8000) NOT NULL DEFAULT(''), [photosupdated] [varchar](10) NOT NULL DEFAULT(''), CONSTRAINT [PK_prepSomeTable] PRIMARY KEY CLUSTERED ( [ID] ASC )) ON [PRIMARY]
the format file:
Code:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="16" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="17" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="18" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="20" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="21" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="22" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="24" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="25" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="26" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="27" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="28" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="29" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="30" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="31" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="32" xsi:type="CharTerm" TERMINATOR="
" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="ClientID" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="pollSystem" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="vin" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="year" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="make" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="6" NAME="model" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="7" NAME="trim" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="8" NAME="mileage" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="9" NAME="factColor" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="10" NAME="intFactColor" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="11" NAME="doors" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="12" NAME="newused" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="13" NAME="stockno" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="14" NAME="cpo" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="15" NAME="certType" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="16" NAME="certid" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="17" NAME="otherprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="18" NAME="invprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="19" NAME="webprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="20" NAME="msrp" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="21" NAME="mktClass" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="22" NAME="options1" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="23" NAME="instock" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="24" NAME="manfModelNum" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="25" NAME="trans" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="26" NAME="enginedesc" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="27" NAME="drivetrain" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="28" NAME="overviewtext" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="29" NAME="options2" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="30" NAME="chromestyleid" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="31" NAME="photourls" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="32" NAME="photosupdated" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
and the SQL:
SQL Code:
Original
- SQL Code
BULK INSERT prep_SomeTable
FROM '\devd_driveftproot7415_519.dat'
WITH ( FIRSTROW = 2, FORMATFILE = '\devd_driveformat_files est.fmt' )
BULK INSERT prep_SomeTableFROM '\devd_driveftproot7415_519.dat' WITH ( FIRSTROW = 2, FORMATFILE = '\devd_driveformat_files est.fmt' )
error message:
Quote: Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
I am not sure why it is trying to insert into the Last_Update Column, which is the only smalldatetime field in the table. but in the format file, i do not have any data going into that column.
it looks like everything is set correctly in the format file
in the SQL Server Library Link, section "Using an XML Format File" 2nd example, it looks to be the same thing as I am?
not sure what I am doing wrong?
I'm trying to create an Insert query and I'm having difficulty in 2 areas:
First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.
INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP
Second, I've tried the following"conversions" in the SELECT statement, to no avail:
CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)
Any pointers much appreciated...
I am looping through all the databases for a table and trying to insert data into two columns in that table. So evrything seems right. but when i run the program it says
Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'OrganizationID', table 'DesMoines_DEV.dbo.Organization'; column does not allow nulls. INSERT fails.
I think writing a dynamic query that produces a series of insert statements is wrong... Instead a dynamic query that execute insert statements one by one may work... But I couldn't figure how to approach..
Below is the code
SET @dbLoop = CURSOR FOR
SELECT name
FROM sys.Databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND State_desc = 'ONLINE'
[Code] ....
Hello friends,
I am sorry to be posting this message in this forum. But thought if anyone does have any idea abt my problem, which i illustrate below.
I want to know what is the maximum number of columns that an INSERT statement can handle?
I have come across a situation where i need to insert 15 values in a table.
INSERT INTO myTable (col1, col2, col3,....., col15) VALUES (v1, v2, v3,...., v15).
Actual statement:
INSERT INTO BillSell (bnumber, stock, qty, price, tprice, seen, bill_id, brokerage, service_tax, brok_st, stt, brok_st_stt, total_cost, total_rate_pu, check)
VALUES ('ICICI 06-12-2004 reshma', 'RELIND', 10, 541.7375183271872, 5417.375183271873, 'no', 2402, 40.2375, 4.92507, 45.162569999999995, 6.771718979089841, 51.934288979089835, 5365.0, 536.5, 'check')
When I call this in Java program, it asks me to check the syntax of the statement as follows :
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'check) VALUES ('ICICI 06-12-2004 reshma', 'RELIND', 10, 541.7375183271872, 5417.' at line 1
Please make notice that all the data types of the table columns and the values passed in the statement match.
Now the problem is that, when from the above INSERT statement, i remove the last column (i.e. the "check" column and its corresponding value passed i.e. 'check'), the code works fine. I observe here that now there are 14 columns in the statement.
So i need the solutions for this. I cannot move further in the program......
Please help.
Trust in Technology mate .....'Not in Human Beings'
I'm looking for an efficient way to populate derived columns when Iinsert data into a table in SQL Server. In Informix and PostgreSQLthis is easily done using the "for each row..." syntax, but all I'vebeen able to come up with for SQL Server is the following:create table testtrigger(id integer unique, b integer, c integer)gocreate trigger testtrigger_ins on testtriggerfor insert asupdate testtrigger set c = (select ...some_function_of_b... fromtesttrigger t1,inserted t2where t1.id = t2.id)where id in (select id from inserted);gowhere id is testrigger's unique id field, and c is a field derived fromb.This seems terribly inefficient since each insert results in an extraselect and update. And if the table is large and unindexed (which itcould be if we are bulk loading) then I would imagine this would bevery slow.Are there any better ways of doing this?Many thanks,...Mike Dunham-Wilkie
View 2 Replies View Related