Using insert with dynamic colomns?
-
can anyone put me right? I'm trying to use the following INSERT statement, and I can't get it to work.
SET @sql = 'INSERT INTO dbo.Ti_Work (date1,[' + @sEin + ']) VALUES (@sdate1,@sArea)'
exec (@sQL)
-
How come you don't know the columns list at run time???
One way to debug this is to use the print statement :
Print @sql
Then tweak that code untill the print statement looks like it should run.
One last thing, you have to concatenate all the parameteres in the value part to make this code work. That is again why you should not be using dynamic sql
. -
This should be a good reading for you :
http://www.sommarskog.se/dynamic_sql.html
-
thanks for the info, the reason for the dynamic is because the data could be put in anyone of 10 different colomns
-
Insert into dbo.TableName (col1, col2, col10) values (@par1, @par2, @Par10).
Make sure to validate that non-nullable columns get feeded a value. Then all other params can be set to null.
-
I need to be able to pass the colomn name into the stored procedure that this insert query resides
-
Why? The columns name shouldn't change often in the life of the application!
Where are those Joe Celko quotes when you need them?!?!!
-
I have colomns called date1,john,george,joe,fred
I'd like to be able to insert data in colomns date1 and one of the other colomns, but by passing in the name of the colomn I want to insert into
-
Insert into Table (Date, John, George, Joe, Fred) VALUES (GetDate(), 'John', NULL, NULL, NULL)
Substitute the value part with parameters and that will always work.
Do you see anything wrong with this solution?
-
Other than a design problem, looks good
-
I already implied that... maybe I was too subtile
. -
many thanks for the assist
-
Can U send me a complete Query.
-
A complete query of what Bhudev?
-
HI I'M SENDING U SAMPLE QUERY - HOPE IT WILL WORK BUT THERE WILL BE ANOTHER PROBLEME WHEN U'LL CHANGE SECOND FIELD OTHER THAN INT. I'LL POST THAT SOLUTION TOMARROW. Till enjoy.
CREATE TABLE TEMP
(
Fdate DATETIME,
vInt INT
)
GO
SET DATEFORMAT DMY
DECLARE
@sEin NVARCHAR(128),
@sDate1 NVARCHAR(128),
@SQL NVARCHAR(500),
@integerval NVARCHAR(10)
SET @sEin = 'vInt'
SET @integerval = 100
SET @sDate1 = '25-10-2006'
SET @sql = 'INSERT INTO TEMP (fdate,' + @sEin + ') VALUES (''' + @sdate1 + ''',' + @integerval + ')'
EXEC ('
' + @sql + '
')
GO
SELECT * FROM TEMP
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply