insert values using dynamic t-sql

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

SQL Server 2000 Forums

Transact-SQL (2000)

insert values using dynamic t-sql

Author

Topic

dataforums
Starting Member

14 Posts

Posted - 2005-05-24 : 13:48:41

Hi,

I am trying to insert data from a temperory table to an permanent table based on some business rules in T-sql procedure using dyanmic t-sql. The temperory and the permanent tables has the same columns. When i am doing the following Insert, it is only working if the temperory table has all the values for all the columns. And also all the columns in both the tables are nullable except the id column and all column are of character datatype.

So, Can anyone plz tell me where i am going wrong in this process?

Select @STR6= 'insert into FISCIDM.DBO.'+@orig_tab+' values('
+char(39)+''+@id+''+char(39)+','
+char(39)+''+@usr_val+''+char(39)','
+char(39)+''+@usr_acc+''+char(39)+','
+char(39)+''+@usr_login+''+char(39)+')'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-24 : 14:08:29

Why are you doing this? Why is the destination table variable? Your tables and columns should be constants, only the data itself should be subject to changes from call to call.

- Jeff

dataforums
Starting Member

14 Posts

Posted - 2005-05-24 : 14:56:08

I have to put ISNULL "ISNULL(@rec_id,'')" for every column. It wirks now. Thanks Anyway!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-24 : 19:53:51

What kind of a project are you working on??? I've read through all of your previous posts. Are you just in love with dynamic SQL for some stupid reason, or is the project you are working on actually requiring you to do this?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dursaliye
Starting Member

22 Posts

Posted - 2005-05-24 : 20:25:00

You can assign a declare variable by SET not SELECT

SET @STR6= 'insert into FISCIDM.DBO.'+@orig_tab+' values('
+char(39)+''+@id+''+char(39)+','
+char(39)+''+@usr_val+''+char(39)','
+char(39)+''+@usr_acc+''+char(39)+','
+char(39)+''+@usr_login+''+char(39)+')'

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-24 : 21:21:04

Dursaliye, you don't know what you're talking about.

http://vyaskn.tripod.com/differences_between_set_and_select.htm