Excel to create SQL insert
Please help to create a formula that would create the following SQL insert statement.
The insert statement on each row should be in the following format.
the first value needs to be an auto incrementing integer say @id...lets start the count from integer 20000 and increment by 1.
INSERT INTO table values(@id,string value from column, int value from column, string value from column, string value from column, getdate(), "", getdate(), "", int value from column
the insert would look like this in sql for one row
INSERT INTO table values(24374,'abc123',1234
Note here that the example of 4566 would be the same column in excel. Just in sql on needs to be converted to varchar..and the other remains an int.
The insert statement on each row should be in the following format.
the first value needs to be an auto incrementing integer say @id...lets start the count from integer 20000 and increment by 1.
INSERT INTO table values(@id,string value from column, int value from column, string value from column, string value from column, getdate(), "", getdate(), "", int value from column
the insert would look like this in sql for one row
INSERT INTO table values(24374,'abc123',1234
Note here that the example of 4566 would be the same column in excel. Just in sql on needs to be converted to varchar..and the other remains an int.
Pawan Kumar
When are you creating this column, choose another columns value i.e. ROWCOLNumber.
you should let the database to handle the auto-increment number and not being inserted via the Insert SQL statement, which means it's suggested that no need to include the id in the insert SQL statement.
you can also upload a sample file so that we can create the Excel formula for you.
you can also upload a sample file so that we can create the Excel formula for you.
ASKER
I will have to do the id in excell. This is a drastic situation. Data recovery. I cannot rely on identity columns in excel.
Yes I will upload a sample excel file.
Thanks
Yes I will upload a sample excel file.
Thanks
ASKER
The exell would look like this. Assume a header and column a, b, c accordingly.
clientid custcode client
20805 ROOT 19137
20807 19137 19137
20817 ROOT 19140
20819 19140 19140
20873 ROOT 19154
20875 19154 19154
20881 ROOT 19156
20883 19156 19156
-The first value needs to be the incrementer
-the second is custcode from above
-the third is client from above
-the 4th is a varchar(50) conversion of the int , clientid, from above
-the 5th is current date
-the 6th is empty string
-the 7th is current date
-the 8th is empty string
-the 9th is int version of clientid from above
INSERT INTO table values(24374,'abc123',1234
clientid custcode client
20805 ROOT 19137
20807 19137 19137
20817 ROOT 19140
20819 19140 19140
20873 ROOT 19154
20875 19154 19154
20881 ROOT 19156
20883 19156 19156
-The first value needs to be the incrementer
-the second is custcode from above
-the third is client from above
-the 4th is a varchar(50) conversion of the int , clientid, from above
-the 5th is current date
-the 6th is empty string
-the 7th is current date
-the 8th is empty string
-the 9th is int version of clientid from above
INSERT INTO table values(24374,'abc123',1234
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
nice...much different than how I used to do it years ago..used to have to use the string cocatenate function..this is much cleaner
ASKER
You saved me. Thank you!!!