Insert into table from another table RESOLVED

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert into table from another table RESOLVED

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-01 : 10:38:58

I want to step through each record in one table and extract a School number and concatenate it to a date and insert it in another table. I get an error abour duplicate keys when I run my sp though. each school number is unique. I want to end up with the key of my new table like this

01/03/07*2232
01/03/07*2253 etc

CREATE PROCEDURE spSM_AddWeeks
@dteWeekEnding nvarchar
as
INSERT INTO tblSM_Meals
(ML_Id)
select @dteWeekEnding +"'*'"SchoolNumber from tblSM_Schools
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-01 : 10:43:05

Is tblSM_Meals empty.
Is it the ML_Id column it is complaining about.

I find it diffcult to think of a reason (at least a reasonable one) why you would want to do this anyway.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 10:46:40

The code ou gave for the stored procedure isn't even valid syntax. At the very least it should be

CREATE PROCEDURE spSM_AddWeeks
@dteWeekEnding nvarchar
as
INSERT INTO tblSM_Meals
(ML_Id)
select @dteWeekEnding + '*' + SchoolNumber from tblSM_Schools
GO

Depending on the data type of SchoolNumber, you may have to cast it to nvarchar or varchar too. What exactly is the error you get?

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-01 : 10:51:23

SchoolNumber is numeric. I have changed my sp to yours and this is the error I get now.

Error converting data type nvarchar to numeric.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-01 : 10:53:45

select @dteWeekEnding + '*' + convert(varchar(20),SchoolNumber) from

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-01 : 10:55:01

Thanks - I will have to try that tomorrow as I have to go now. Thanks for your help

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 11:18:56

Or
select @dteWeekEnding + '*' + cast(SchoolNumber as varchar(10)) from tblSM_Schools

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-02 : 04:54:48

I'm afraid I'm still getting problems and an error message

Syntax error converting datetime from character string.

Here's my sp
CREATE PROCEDURE spSM_AddWeeks

@dteWeekEnding datetime

as

INSERT INTO tblSM_Meals
(ML_Id)
select @dteWeekEnding + '*' + cast(SchoolNumber as varchar(10)) from tblSM_Schools
GO

Here's my asp.net code

Dim Newdate As Date
Dim x As Integer

Newdate = CDate(Me.txtStartWE.Text)

For x = 1 To 52

Newdate = DateAdd("d", 7, Newdate)

Dim MySQL As String = "spSM_AddWeeks"

'Get connection string from Web.Config
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure

Cmd.Parameters.Add(New SqlParameter("@dteWeekEnding", CDate(Newdate)))

MyConn.Open()
Cmd.ExecuteNonQuery()
MyConn.Close()

Next x

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-03-02 : 08:10:53

You have to convert @dteWeekEnding to a character datatype too. Lookup CONVERT in BOL for details.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources