0

How do I perform a check using CASE to check if a specific column has been reached then SELECT INTO INSERT into a another table while copying the current data over into this second table?

This is because my first tbl cases only accepts inserts upto column 20 (in the real world these are actually upload files).

The two by definition are exactly the same as I populated the second from a Select Table As > Script into > New table query ....etc. ID is in both are identity cols.

For example:

--INSERT THE SAME DATA BUT DO NOT INSERT INTO COLUMN UploadNo1 to UploadNo20 into casesTwo as this should already have data (files from table cases). 

 INSERT INTO casesTwo --ONLY FILE FROM COLUMN UploadNo20 SELECT CAST( CASE WHEN No20 = 'UploadNo20' THEN 1 ELSE 0 END) FROM cases
Improve this question

1 Answer 1

Reset to default
0

No way with CASE - you have to list the first 20 columns manually. You will need a dynamic query if you want to get columns dynamically.

Here is a sample:

DECLARE @DynamicSQL NVARCHAR(max); DECLARE @ColumnsLits NVARCHAR(max); -- Concatenate first 20 columns: SELECT TOP 20 @ColumnsLits = isnull(@ColumnsLits + ', ', '') + column_name FROM information_schema.columns WHERE table_name = 'YourTable' ORDER BY ordinal_position OPTION (MAXDOP 1) -- You can check column list: -- SELECT @ColumnsLits -- Build the query: SET @DynamicSQL = N'SELECT ' + @ColumnsLits + N' FROM YourTable' -- ... and run it: EXEC (@DynamicSQL)
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks for your reply. Do you have an example I could try? I think the database design here is not the best but I believe in the real world the ideal scenario is to dynamically create new 'UploadNo' columns until I reach a limit of some sort (whether column number e.g. 100th column(UploadNo100) or data memory size e.g. (If total uploads size hits 100mb). If so then throw an exception and block further uploading.)) Blocking/exception is not so much of an issue as the frontend currently only has 40 upload buttons (ddls) so user can't physically choose e.g. 41st upload.
Sure - just added a sample.
Thanks. I've done this so far for testing puposes and it works: DECLARE DynamicSQL NVARCHAR(max); DECLARE ColumnsList NVARCHAR(max); SELECT TOP 20 ColumnsList = isnull(@ColumnsList + ', ', '') + IOSupportURL20 FROM [ViewpointDB].[dbo].[tbl_suppcorr] SELECT ColumnsList
But this doesn't work: DECLARE @ DynamicSQL NVARCHAR(max); DECLARE @ ColumnsList NVARCHAR(max); SELECT TOP 20 @ ColumnsList = isnull(@ ColumnsList + ', ', '') + IOSupportURL21 FROM [ViewpointDB].[dbo].[tbl_suppcorr] AS scT --SELECT @ ColumnsList WHERE scT.ID = [dbo].[tbl_suppcorr].ID OPTION (MAXDOP 1) -- You can check column list: SELECT @ ColumnsList
The error is: Msg 4104, Level 16, State 1, Line 4 The multi-part identifier "dbo.tbl_suppcorr.ID" could not be bound.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.