insert values using dynamic t-sql

[dataforums]

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)+')'


Thanks in advance!

 

[JayKusch]

Try ...

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

PRINT @STR6

Print the string after you create it helps to see what the final variable looks like!


Thanks

J. Kusch

 

[dataforums]

Thanks Jay. It worked.