Mssql: Insert Into Syntax
Aug 21, 2005
Hello
Can anyone help me translate this from access so that it can work in mssql
(i need to get next value, but cannot use identity as if row is deleted,
another must get new next column number which would be same as deleted one)
Access;
INSERT INTO table
SELECT
(IIF(code<>Null,MAX(code)+1,1) AS code,
0 AS usercode
FROM table
I tried this in mssql but will not work:
INSERT INTO table
SELECT
CASE
WHEN code IS NULL THEN 1
ELSE MAX(code)+1
END
AS code,
0 AS usercode
FROM table
View 14 Replies
ADVERTISEMENT
Nov 28, 2007
Hi
I am new to MSSQL and have encountered the following create statement. However I am not able to determine what the constraints like DF__work_home__name__1AF3F935 DEFAULT '' NOT NULL really mean ?
I am not getting it...
where is this name__1AF3F935 coming from ??
Code:
CREATE TABLE dbo.work_home
(
wh_id numeric(18,0) IDENTITY,
pid numeric(18,0) NULL,
name varchar(50) CONSTRAINT DF__work_home__name__1AF3F935 DEFAULT '' NOT NULL,
[position] varchar(80) CONSTRAINT DF__work_home__posit__1BE81D6E DEFAULT '' NOT NULL,
computer varchar(15) CONSTRAINT DF__work_home__compu__1CDC41A7 DEFAULT '' NOT NULL,
[connection] varchar(15) CONSTRAINT DF__work_home__conne__1DD065E0 DEFAULT '' NOT NULL,
access_to_files varchar(15) CONSTRAINT DF__work_home__acces__1EC48A19 DEFAULT '' NOT NULL,
access_to_apps varchar(15) CONSTRAINT DF__work_home__acces__1FB8AE52 DEFAULT '' NOT NULL,
comment text NULL,
disabled char(1) CONSTRAINT DF__work_home__disab__49E3F248 DEFAULT 'N' NOT NULL,
CONSTRAINT pk_wh_id
PRIMARY KEY CLUSTERED (wh_id)
)
View 1 Replies
View Related
Jan 16, 2007
I have a table that has unit id, date, time, etc. I would like to select each unit id with the last date it has in the table. The result should have each unit listed once with the latest date in the table.
For Example:
unit id Date Time
00100 01/12/2007 8:00
00100 01/12/2007 8:45
00200 01/12/2007 8:50
00100 01/13/2007 13:30
00300 01/13/2007 13:45
00100 01/14/2007 11:00
00200 01/14/2007 11:30
the result should be:
00100 01/14/2007 11:00
00200 01/14/2007 11:30
00300 01/13/2007 13:45
View 5 Replies
View Related
Dec 29, 2006
Seasons greetings to everyone,A simple question. Could someone show me the syntax to produce multiple (2 or 3) result sets in a stored proc and how you access those sets from a c# program (ASP.NET)..Couldn't find a reference on Google, maybe I was asking the wrong question! Thanks for any help regardsDavej
View 3 Replies
View Related
Feb 8, 2007
Hi,I have app with file selection field. Users can shoose xml file from their local hard disk and click Save. When they click save, content of xml file should be inserted into mssql 2000.What is the most efficient way to do this using C#2005 and mssql2000?Any good article or tutorial?
View 2 Replies
View Related
Oct 30, 2007
Hello Everybody,In my web application I have to insert html from editor. I have assigned the filed as "nvarchar". But after running the insert query it throws an error "A potentially dangerous Request.Form value was detected from the client
(oEdit1="<P><SPAN style="FONT..."). " I'm using WYSWYG editor. I'm now quite bit confused about the the field and the query. If you have any idea about the problem please help me out of this problem.Thanks in option.Regards--Arindam
View 3 Replies
View Related
Oct 21, 2007
Hi all
Execute insert normal insert script Insert Into () select * from...
I need to monitor how many record inserted in target table, anyone can help
View 6 Replies
View Related
Dec 23, 2006
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If Page.IsPostBack Then
binddata()
End If
End Sub
Sub binddata()
baglanti = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|datagoldbar.mdf;Integrated Security=True;User Instance=True")
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim cmd As New SqlCommand("INSERT INTO db " & _
"(ip,sehir,mesaj,email) VALUES (@ip, " & _
"@sehir,@mesaj,@email)", baglanti)
cmd.Parameters.Add("@ip", SqlDbType.NVarChar, 50).Value = isim.Text
cmd.Parameters.Add("@sehir", SqlDbType.NChar, 50).Value = sehir.Text
cmd.Parameters.Add("@mesaj", SqlDbType.NText).Value = mesaj.Text
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 150).Value = email.Text
baglanti.Open()
cmd.ExecuteNonQuery()
binddata()
End Sub
Server Error in
Object reference not set to an instance of an object.
where is mistake there?
View 4 Replies
View Related
Mar 20, 2008
Hi,
I am in the middle of writing a console application that acquires data from dynamic odbc connections and inserts the results into a MSSQL database. I'm currently using a datareader to generate multiple calls to a stored procedure and batch execute them by means of a simple counter; this works fine.
However, I'm a little concerned that it seems to take so long to execute the sql stored procs and was wondering if anyone may know of any methods to help speed it up either on the app side or sql, or both.
I had a quick word with our dba who spoke briefly about some kind of process where by the application fires the request across to the database and carries on leaving the db to queue the request or something to that effect. He ran away before I could get any sort of sense out of him.
Any help greatly appreciated
Thanks
View 4 Replies
View Related
Oct 31, 2007
Hi im new to SSIS even i did some basice things in SSIS. Now i run in to the problem, I hawe a access file with arount one milion records and i won to transfare this records in Ms SqlServer . But befora i transfare that i nead to check if that record exsist by ID if exsist i must do update else i must do insert.
Can some one Help me how can i do it..
THX
Sorry for my bad englisht
View 3 Replies
View Related
May 14, 2008
Hello. I have the below INSERT statement in an application, which works fine. However, I can't figure out how to insert data into a new column. The new column is text3, and the text would be TextBox4.Text + / + TextBox5.Text + / + TextBox6.Text + / TextBox7.Text. Where the "/" is text and not an operator. Every syntax I try doesn't work. Any ideas would be appreciated! INSERT INTO table (updateTime, text4, text5, text6, text7, updateTime1) VALUES (getdate(), '" + this.TextBox4.Text + "', '" + this.TextBox5.Text + "', '" + this.TextBox6.Text + "', '" + this.TextBox7.Text + "', CONVERT(VARCHAR(19), GETDATE(), 120) + Space(2) )
View 5 Replies
View Related
Mar 1, 2004
In SQL 7.0 SP3 , I am receiving this message....
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near ')'.
When I try to execute this code from SQL Query Analyzer...
DECLARE @DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"
OPEN DPPNumberCursor
Fetch Next From DPPNumberCursor
INTO @dppnumbercursor
While @@Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id)
Values (@dppnumbercursor)
Bsically, I want to insert the number held in @dppnumbercursor in the APQPSub_id field.
Any help would be appreciated.
View 10 Replies
View Related
Mar 20, 2007
INSERT INTO Payment_Breakdown (Order_ID,Denomination,Card_Payment,Comms_Request,Comms_Approved) VALUES (99109760,FAILED_CARD,0,-cc826 -cu826 -mc540436503249096 -tr09 -an1900 -cd4444333322221111 -dat070319172240 -ed0801 -is5 -rfIVR991097600 -sc456 -sd0501 -x,-rc30 -tr09 -ms"BAD AMOUNT" -rfIVR991097600 -cd4444333322221111 -ed0801 -sd0501 -cnVISA -td03132329 -mc540436503249096 -st0007 -x)
The element in italics is the problem I suspect. It is a text string to be entered into the table. It is actually a variable, I have taken this from a log as it shows the value instead.
I get this error...
quote:(STMT SQL Info:SQLPrepare [ S1000] Code:-201, [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Parse Error in SQL q:F) (0xffffffff)
I am assuming the problem lies in the inverted commas (") in the italic string, is this right? How can I get around this or will I have to code them out before they become a string value in the variable?
View 2 Replies
View Related
Dec 26, 2006
I am setting up a new database using a shopping cart SW, when I create the DB
using there script, I get the following error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
The line syntax is as follows.
BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:Inetpubwwwroot estSQL-AdminLook-Weight.csv';
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
)
GO
What is wrong???
View 1 Replies
View Related
Mar 5, 2008
I have run into a problem with my instert satement that gives me a syntax error whenever i try and add an extra value to it.
This one works but
Code Snippet
string sqlString1 = "INSERT INTO QuoteHeader (QuoteHeaderID, CustomerID, EmployeeID) VALUES("
+ newOrderHeader
+ ",'" + custID
+ "','" + empID
+ "')";
add another value and I get a synax error And i'm not sure why.
Code Snippet
string sqlString1 = "INSERT INTO QuoteHeader (QuoteHeaderID, CustomerID, EmployeeID, Month) VALUES("
+ newOrderHeader
+ ",'" + custID
+ "','" + empID
+ "','" + textBox6.Text
+ "')";
Code Snippet
try
{
cmd = new OleDbCommand(sqlString1, connectCmd);
int rowsReturned = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string s = ex.Message;
DisposeResources(ref oleDbDataAdapter, ref ds, ref connectFill, ref connectCmd, ref cmd);
return;
}
View 7 Replies
View Related
Jan 4, 2008
Hello there,I'm to asp.net, so please be patient :DMy question is, how do I simply add some data to my database? - With vb.net code, not a grid view or something like that..I want to connect to my database, insert some data to a table.It shouldn't be that hard?- Hope someone will take the 5 minutes, and help me :)Regards Jeppe
View 16 Replies
View Related
Jun 14, 2007
I created a link from Oracle to SQL server.
There is a trigger with insert statment in Oracle side.
I got error message as follow when trigger is invoked:
SQL> insert into test1 values ('zerbra','brazi');
insert into test1 values ('zerbra','brazi')
*
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "GGWEB.TRIGGER2", line 4
ORA-04088: error during execution of trigger 'GGWEB.TRIGGER2'
my simple trigger is as follows:
create or replace
TRIGGER TESTRI
AFTER INSERT ON TEST1
FOR EACH ROW
BEGIN
insert into test1@sqlserver (city,state) values ('what','nine');
END;
View 6 Replies
View Related
Jan 11, 2007
Ok, the following four lines are four lines of code that I'm running, I'll post the code and then explain my issue:
sqlCommand = New SQLCommand("INSERT INTO Bulk (Bulk_Run, Bulk_Totes, Bulk_Drums, Bulk_Boxes, Bulk_Bags, Bulk_Bins, Bulk_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery() sqlCommand = New SQLCommand("INSERT INTO Presort (Presort_Run, Presort_Totes, Presort_Drums, Presort_Boxes, Presort_Bags, Presort_Bins, Presort_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery()
The two tables (Bulk & Presort) are <b>exactly</b> the same. This includes columns, primary keys, IDs, and even permissions. If I run the last two liens (the INSERT INTO Presort) then it works fine without error. But whenever I run the first two lines (the INSERT INTO Bulk) I get the following error:
Incorrect syntax near the keyword 'Bulk'.
Anyone have any ideas, thanks
View 2 Replies
View Related
Apr 12, 2007
I am so close to haveing the web page i want, but i hve a syntax error with the Public Function Insert() as Integer.
I see no reason for this
i will post the code below, any help will be appreciated
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" %>
<script runat="server">
Private Sub Submitdata(ByVal Source As Object, ByVal e As EventArgs)
SqlDataSource1.Insert()
End Sub ' Submitdata
</script>
<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
<asp:sqldatasource
id="SqlDataSource1"
runat="server"
connectionstring="<%$ ConnectionStrings:clientInfoConnectionString %>"
selectcommand="SELECT Client Name,Client Address FROM Clients"
insertcommand="INSERT INTO Clients (Client Name,Client Address) VALUES (@CName,@CAddress)">
<insertparameters>
<asp:formparameter name="CName" formfield="Namebox" />
<asp:formparameter name="CAddress" formfield="Addressbox" />
</insertparameters>
</asp:sqldatasource>
Name:<br />
<asp:TextBox ID="Namebox" runat="server" /><br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Namebox"
ErrorMessage="Please Enter A Name"></asp:RequiredFieldValidator>
<br />Address:<br />
<asp:TextBox ID="Addressbox" runat="server" /><br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Addressbox"
ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>
<br /><asp:Button ID="Submitbtn" runat="server" Text="Submit" OnClick="InsertShipper" />
<br />
</asp:Content>
Thanks
The King
View 10 Replies
View Related
Dec 16, 2005
Here is my insert statement: StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO patients_import_test "); sb.Append("(Referral_Number,Referral_Date,FullName,Patient_ien,DOB,FMP,SSN_LastFour,Race_Id,PCM,Age) "); sb.Append("VALUES(@rnum,@rdate,@fname,@patid,@birthDate,@fmp,@ssan,@race,@pcm,@age) "); sb.Append("WHERE Referral_Number NOT IN ( SELECT Referral_Number FROM patients_import_test )");I'm getting an "Incorrect syntax near the keyword 'WHERE'".If I remove the WHERE clause the INSERT statement work fine.
View 3 Replies
View Related
Sep 29, 1999
I am writing a stored procedure:
CREATE PROCEDURE rasp_FillDescrColumnNames
@TableNameD varchar(50),
@TableNameU varchar(50),
@TableUID int
--
--
AS
......
Insert into @TableNameD(ColumnName)
Select #TempColumnNames.Name
From #TempColumnNames
-----------------------------
I get "incorrect syntax near '@TableName'". (The input table does have a column called 'ColumnName'). How do you parametized an 'Insert Into' sql statement? I have tried a number of ways to no avail.
Thanks,
Judith
View 2 Replies
View Related
Dec 14, 2006
I cannot identify where the syntax error is, please help.
INSERT INTO [Attendence/Activity Log] (ID Number, Date, Activity, Duration) VALUES ('39', '12/14/06', 'Health & Nutrition', '2')
View 1 Replies
View Related
Aug 12, 2004
I have a insert statement but one of the strings contains a apostrophe. If I leave the apostrophe in an error occurs becuase it thinks that it is the end of the string. What is the proper syntax for including apostrophes in a string?
Thanks.
View 8 Replies
View Related
Sep 28, 2004
Hi,
I'm working in vb.net and want to use a stored procedure to insert all employees from one db into my db. I can insert one by one, but I would like to get them all in without looping.
How would I do this? I've tried bulk insert, but I keep getting syntax errors; I've read the books online, but don't quite understand what they mean. I don't want to use DTS, should I?
Here is what I'm doing so far:
CREATE Procedure Insert_From_Personnel
@emp_num char(10),
@Frst_Name char(10),
@Last_Name char(10),
@DivisionID char (4)
as
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
VALUES (@emp_num,@Frst_Name,@Last_Name,@DivisionID)
GO
Thanks for any help,
View 14 Replies
View Related
Apr 2, 2008
Hi,
I have a DTS package which, apart from other steps, loads a text file to the SQL Server (2000) table. The problem is that I need to do it for at least 20 text files, may be more.
As far as I have no experience in parametrizing DTS packages, I suppose it will be easier for me to do it with BULK INSERT.
What would be an equivalent BULK INSERT syntax for this load
(parameters taken from the DTS package mentioned)?
---------------------------------------
load a text file: path/txtfile.txt
(txtfile.txt on the network drive)
to an SQL Server 2000 table: db1.dbo.table1
Select File Format:
- Delimited
- File type: ANSI
- Row delimiter: Comma
- Text qualifier: Double Quote
- First row has column names: NO
Specify Column Delimiter:
- Tab
---------------------------------------
Thanks
Katarina
View 3 Replies
View Related
Sep 23, 2013
I just upsized my access backend and a few of my tables did not import data. I am trying to do a bulk insert and this is the code I am using:
"BULK INSERT [dbo].[invoices]"
Select ('VendorName, Invoice_Number, DueDate,Type_of_Invoice, Select_field, Invoice_Status, tday, discount, file_as, Invoice_amount, Date_Paid, check_number, apply_discount, amountpaid')
FROM 'A:Invoices2.csv'
;WITH
(
FIELDTERMINATOR = ',')',
ROWTERMINATOR = "n")',
GO
When I click on the execute button I get this message:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'A:Invoices2.csv'.
View 3 Replies
View Related
Feb 18, 2015
I create table with automatically generate the next ID.
create table #people
(id integer identity primary key not null,
name varchar(20),
surname varchar(30),
number Char(11)
);
and next I would like to add many lines
insert into #people (name, surname, number)
VALUES
('Anne', 'Ferguson', '123456789'),
('Eve', 'Atkinson', '234567891'),
('John', 'Smith','345678912');
and I've got:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
This is something wrong with the row ('Anne...
I use sql MS server 2008
View 1 Replies
View Related
May 30, 2006
Hi,
I have a problem as shown below
Server Error in '/ys(Do Not Remove!!!)' Application.
Syntax error in INSERT INTO statement.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
Source Error:
Line 8: <Script runat="server">
Line 9: Private Sub InsertAuthorized(ByVal Source As Object, ByVal e As EventArgs)
Line 10: SqlDataSource1.Insert()
Line 11: End Sub ' InsertAuthorized
Line 12: </Script>
Source File: C:Documents and SettingsDream_AchieverDesktopys(Do Not Remove!!!)Authorizing.aspx Line: 10
Stack Trace:
[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +88
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +392
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +410
System.Web.UI.WebControls.SqlDataSource.Insert() +13
ASP.authorizing_aspx.InsertAuthorized(Object Source, EventArgs e) in C:Documents and SettingsDream_AchieverDesktopys(Do Not Remove!!!)Authorizing.aspx:10
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919
And part of my program is as shown
<Script runat="server">
Private Sub InsertAuthorized(ByVal Source As Object, ByVal e As EventArgs)
SqlDataSource1.Insert()
End Sub ' InsertAuthorized
</Script>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DIP1ConnectionString %>" ProviderName="<%$ ConnectionStrings:DIP1ConnectionString.ProviderName %>"
InsertCommand="Insert Into Authorize (Army ID,Tag No,Vehicle ID,Vehicle Type,Prescribed Route,Start Time, End Time) VALUES (@ArmyID, @TagNo, @VehicleID, @VehicleType, @PrescribedRoute, @StartTime, @EndTime)">
<insertparameters>
<asp:formparameter name="ArmyID" formfield="ArmyID" />
<asp:formparameter name="TagNo" formfield="TagNo" />
<asp:formparameter name="VehicleID" formfield="VehicleID" />
<asp:formparameter name="VehicleType" formfield="VehicleType" />
<asp:formparameter name="PrescribedRoute" formfield="PrescribedRoute" />
<asp:formparameter name="StartTime" formfield="StartTime" />
<asp:formparameter name="EndTime" formfield="EndTime" />
</insertparameters>
</asp:SqlDataSource>
Anybody can help? thanks
View 1 Replies
View Related
Apr 7, 2008
Public DBString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Q:VoicenetRTS FolderRTS ChargesAccountscosting.mdb"
Private Sub Button13_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button13.Click
Dim connstring As New OleDbConnection(DBString)
connstring.Open()
Dim searchstring As String = "SELECT * FROM Costings1"
Dim da As New OleDbDataAdapter(searchstring, connstring)
Dim DS As New DataSet()
Dim dt As DataTable = DS.Tables("Costings1")
da.FillSchema(DS, SchemaType.Source, "Costings1")
da.Fill(DS, "Costings1")
Dim cb As New OleDbCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand
da.UpdateCommand = cb.GetUpdateCommand
Dim dr As DataRow = DS.Tables("Costings1").NewRow
dr("Key") = TextBox1.Text
dr("CODE") = TextBox2.Text
dr("Element") = TextBox3.Text
etc...
DS.Tables("Costings1").Rows.Add(dr)
da.Update(DS, "Costings1") <<<<<<<<<<<Syntax error in INSERT INTO statement.
There are no spaces in the field names.
View 9 Replies
View Related
Dec 27, 2000
I'm trying something like this:
CREATE PROCEDURE Add_Junk @Dist char, @CheckNo int =null OUTPUT AS
Set NoCount On
BEGIN TRANSACTION
INSERT INTO Junk (Dist)
VALUES (@Dist)
COMMIT TRANSACTION
select @CheckNo=@@IDENTITY
If what I pass is "416" I only get the "4" in my database and nothing else.
I don't get an error message.
What is wrong with my syntax?
PS I'm using Microsoft SQL 7.0
View 2 Replies
View Related
Jun 13, 2004
The following code should insert into 3 tables based on conditions. There's something screwy in my syntax and I'm pretty new at this can anyone help with transforming this in terms of performance and being syntactically correct? Thanks a million!
CREATE PROCEDURE [insert_vwMusic]
(@Artist [nvarchar](50),
@Genre [nvarchar](50),
@NLink [nvarchar](50),
@Album[nvarchar](50),
@Song[nvarchar](50),
@ArtistID[nvarchar](50),
@AlbumID[nvarchar](50),
@SLink[nvarchar](50))
AS
DECLARE @NewArtistID VarChar(50),
DECLARE @NewAlbumID VarChar(50)
IF Not Exists (SELECT [Artist] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
BEGIN
INSERT INTO [integration].[dbo].[tblMusic_Artist]
( [Artist],
[Genre],
[NLink])
VALUES
( @Artist,
@Genre,
@NLink)
SET @NewArtistID = @@IDENTITY
INSERT INTO [integration].[dbo].[tblMusic_Albums]
( [Album]
VALUES
( @Album)
SET @NewAlbumID = @@IDENTITY
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
ELSE
BEGIN
IF Not Exists (SELECT [Album] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
BEGIN
INSERT INTO [integration].[dbo].[tblMusic_Albums]
( [Album]
VALUES
( @Album)
SET @NewAlbumID = @@IDENTITY
SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
END
ELSE
BEGIN
SET @NewAlbumID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
GO
View 5 Replies
View Related
Sep 14, 2004
Hi all,
I have to insert records using transact iin the stored procedure. I have some thing like:
DECLARE @Err varchar(100)
DECLARE insertQ varchar(1000)
SET @Err = 'Insertion data'
SET @insertQ =('INSERT INTO dbo.T_ERRORLOG (ERROR_DESCR) VALUES(' + @Err + ')')
EXEC insertQ
But it don't work. Can you help me to solve the problem plz?
Thanks a lot
TT
View 4 Replies
View Related
Aug 9, 2006
May I know what does the syntax of inserting data into a field of type Unique Identifier look like?
[code]
INSERT INTO THAI_MK_MT_Log(GUID, Status) VALUES ('2331486348632', 'S')
[/code]
The "2331486348632" is to be inserted into a unique identifier field.
If i coded the insert statement as the above, I got an error saying that "Syntax error converting from a character string to uniqueidentifier".....
Can anyone help?
View 11 Replies
View Related
